This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
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
2. How are you releasing your resources i.e. Connection, ResultSet etc.?
Joined: Nov 11, 2003
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.
Joined: Nov 11, 2003
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:
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.
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).
Joined: Sep 29, 2002
Find out how long this takes to execute:
And are you using the correct driver. Which drivers do you have loaded?
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.