aspose file tools*
The moose likes JDBC and the fly likes Connection pool + performance Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection pool + performance" Watch "Connection pool + performance" New topic
Author

Connection pool + performance

Ram kovis
Ranch Hand

Joined: Jun 23, 2005
Posts: 130
The Connection pool topic might have been discussed many times. But still want to clarify my doubts

I have been asked to work on a applcaiiton which has some 10 reports, basically read data from the database, do some processing, and finally display the data to the user..
It was already coded by someone else..
So, I went though the code
Following are my observations
1) the applciaiton has only one database , but a huge database ( say a million records)
2) NO stored procedures are used, though, all the data is avilable in a single database. ( I could have used Stored procs in this case.. entire report data logic in one place)
3) For each report, minimum 2 simple queries and some 4 complex queies get exeuted, so as many as 6 or 7 database calls are being made. So, basically, its like this.
open db connnection ->execute 1 preparedStmt-> get teh result set-->put the data in Bo with some more business logic on the data received on the java side--> close Connection
( Somtimes, since they are using CachedRowset, they close the connncewtion as soon as they get ResultSet and execute some more logic on java code)

Repeat the same for all the 6 or 7 queries.

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..
Just call this stored procedure from DAO passing 4 or 5 input paramters, get the final result set and loop through this resultSet and put it a List object and display it to the user. as simple as that..

Apart from the segregation of report logic across all the java classes( this can be handled in Stored proc, nothing complicated..), Aren't they hitting performance like creating connections, prepared stattement objcets etc
when I asked them , they say they have 8 instances and each instance has a connection pool of size 15, so there will not be any problem with creating so many connections for a single report as they closing the connecition immdiately after using it.. And these reports is a part of big applciation and which use the same pool. No of concurent users for this applcaiton is Min 800 at any point of time.. Will it not create any problem in the performance area. Just becasuse, we have conncetion pool , can we use them just like that? Shouldn't we minimise database calls for better resource usage?

Some moer details:
App server: WAS 5.1
no of instances: 8
pool size on each insatnce: 15
DB: DB2

[ August 10, 2005: Message edited by: Ram kovis ]
[ August 10, 2005: Message edited by: Ram kovis ]
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30758
    
156

Ram,
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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Ram kovis
Ranch Hand

Joined: Jun 23, 2005
Posts: 130
Jeanne,
thankks for the welcome.. and thanks for the reply too..


Originally posted by Jeanne Boyarsky:
Ram,
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.


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?

A connection pool is good at caching connections and prepared statements, so there isn't any inherent problem there. .

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?

.


Does the application have a performance problem? If not, the design is fine.


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?
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
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..

This is exactly what I like to see: business logic in one place (in the business objects). It is also good that connection pooling and closing of Connections is being done immediately after use. Just check that the ResultSet and Statement objects are also closed just before the Connections (all closures to be handled in separate try/catch blocks in the finally block).

I think you are getting too hung up about stored procedures and performance. Unless there is a compelling reason to make a change, then don't. As has been said often enough, premature optimisation is the root of all evil.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30758
    
156

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?

One query returning any amount of data with any number of joins will be of equivalent speed using either PreparedStatements or stored procs. This is because the database has the same query to execute and the network has the same amount of resulting data to transfer. I agree with Roger that it is good to leave the business logic in JDBC unless there is a compelling reason to change. Now if you had a few queries that built on the data that the previous query returned, you might have benefits to a stored procedure.

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?

It depends on how many queries you have. In the case of one large query with multiple joins, you are only using one connection.

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?

No. The database still has to execute the same queries regardless of where they reside.

Note that you could try a stored proc (on a simpler example) and see what it buys you. But unless you have a performance problem, it is better to wait.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connection pool + performance