• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

slow query using JDBC in servlet

 
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 405
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
1. How are you acquiring your Connection?


2. How are you releasing your resources i.e. Connection, ResultSet etc.?
 
Dan Walin
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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");
 
Ranch Hand
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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



When posting code, be sure to check the 'disable smiles' checkbox.
 
Dan Walin
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1683
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Find out how long this takes to execute:



And are you using the correct driver. Which drivers do you have loaded?
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic