Originally posted by Jeanne Boyarsky:
Welcome to JavaRanch!
The performance hit depends on both the number of trips to the database and how much data is transfered across the network. The fact that there are a million rows in your table is the same for a stored proc or a prepared statement if they aren't all being returned.
A connection pool is good at caching connections and prepared statements, so there isn't any inherent problem there. .
Does the application have a performance problem? If not, the design is fine.
I am surprised to see such a code.. I am not sure about the performance hit here.. but, business logic is segregated here.. I could have moved to all this to a single stored procedure..
Originally posted by Ram kovis:
The data that gets transfeerred with each SQL execution is minimum 300 rows( ofcourse no of columns varies frm 3 to 10..) and final outut to the user will have some 300 rows by 8 to 10 columns..All these queries actually join different tables, not just a table..
I think, Stored proc ececutionn will be faster, compared to normal SQL execution + some java code execution.. Is n't true?
So, since we have a connection pool, we can use connections as many times , we want.. Aren't we moving frequently between app server and Database?
Currenty no, as database is having only 50000 rows and by end of the year, there will be 500000 after alll the PROD rollouts and no of users will also increase by then..
Dont we get any extra benefits by usinng stored procedures? execution times etc?