aspose file tools*
The moose likes JDBC and the fly likes slow query using JDBC in servlet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "slow query using JDBC in servlet" Watch "slow query using JDBC in servlet" New topic
Author

slow query using JDBC in servlet

Dan Walin
Ranch Hand

Joined: Nov 11, 2003
Posts: 109
I have a servlet that is using a preparedStatement to look up a value in a DB2 table. All parts of the servlet run very fast except when the query is executed. A simple lookup of one record in a table takes 4 minutes. As a test, I set up an sql query on the server that was exactly the same and the results come back in less than a second so there is no issue with the database table itself or the server. I put timers in the servlet to log times as different portions of the servlet code run and everything takes a matter of a few seconds except the query execution. Here's the portion of my code that executes the query:


Any help or suggestions to speed this up would be greatly appreciated. Thanks
Craig Jackson
Ranch Hand

Joined: Mar 19, 2002
Posts: 405
1. How are you acquiring your Connection?


2. How are you releasing your resources i.e. Connection, ResultSet etc.?
Dan Walin
Ranch Hand

Joined: Nov 11, 2003
Posts: 109
I'm glad someone responded to this - it's a big mystery to me. I have "timings" in the code so that a time check is done before the connection is made, after the connection is made and then after the sql is executed and finally after the connection is closed. The connection takes .003 seconds, the sql is executed in 5 minutes and 30 seconds, the connection is closed in .3 seconds.

The connection is made like this:


and the connection is closed:



I think I mentioned in my first post that the servlet runs on one server A and the database is on another server B. If I sign on to server A and run the sql right on the server - it is able to find the record on server B and diplay the results in less than a second so there does not appear to be any issue with the database or the connection from A to B.

In my test, I just run the servlet once to find one record, if I run it more times, the timing is the same. I'm sharing this in case you're wondering about too many connections being open or something like that. Thanks for any advice.
Dan Walin
Ranch Hand

Joined: Nov 11, 2003
Posts: 109
For some reason the forum messed up my code with smiley faces - must be the sequence of characters in the code format so here's the connection again:

Connection Conn =
DriverManager.getConnection(
"jdbc b2 bname",
"userid",
"password");
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
It is better to use a JNDI lookup and DataSource to obtain your JDBC Connection, but this isn't the cause of your problem.

Ensure that your Connection variable is local, that you obtain your Connection as late as possible and that you close your ResultSet, Statement and Connection when they are not needed. I recommend closing these JDBC objects in the finally block of the method in which they were created, but don't throw exceptions in this block. Check that you are using the correct driver. If you still have a problem, please post your full code.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61230
    
  66

For some reason the forum messed up my code with smiley faces


When posting code, be sure to check the 'disable smiles' checkbox.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Dan Walin
Ranch Hand

Joined: Nov 11, 2003
Posts: 109
When you say to make the connection variable local you mean just to use a "local variable"? I think I'm doing that. Here is the entire code - this class called "documentfind" is called from the servlet. The servlet passes a parameter of sdocid. As you see I have a lot of debugging code (writing to logs) which I added in an attempt to find out where the slowness is. Also, FYI - this runs on the iSeries (OS400).

Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
Find out how long this takes to execute:



And are you using the correct driver. Which drivers do you have loaded?
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61230
    
  66

Dan, please do not cross-post the same question in multiple forums. It wastes people's time when multiple redundant conversations take place. Please continue any discussion of this topic in the Servlets forum.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: slow query using JDBC in servlet