aspose file tools*
The moose likes Servlets 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 » Java » Servlets
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
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17260
    
    6

OK, suggestions

1. Move the JDBC code out of the Servlet code. It should be in its own class, and not in the Servlet. Servlet's should just handle the Request and the Response. Any business logic, database stuff, and EJB lookups should be done outside ths Servlet class to allow for re-use. If you ever need that functionality for a different type client, or even in another Servlet, they can all share the code.

2. Because it is now outside in its own class, you can do a simple test of the JDBC code from a Plain old Java Object (POJO) with a main method and see if that also takes a long time too.

Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Jared Richardson
author
Ranch Hand

Joined: Jun 22, 2005
Posts: 113
A four minute query from your Java code when the database console takes seconds is more than just JDBC overhead. It sounds like your network name resolution might be timing out on a DNS server before failing over to a second one. Try pinging ("ping <machine>") the database server, then put the IP address (that ping will show you) in your code instead of the machine name.

Also, try putting a timing statement around the creation of your JDBC connection and another around the code that executes it. This will tell you better where the problem is.

A JDBC connection pool can also be used to keep persistent connections available so each request won't have to establish a new connection to the database.


Check out <b>Ship It! A Practical Guide to Shipping Software</b><br /> <br /><a href="http://www.pragmaticprogrammer.com/titles/prj/" target="_blank" rel="nofollow">http://www.pragmaticprogrammer.com/titles/prj/</a>
Dan Walin
Ranch Hand

Joined: Nov 11, 2003
Posts: 109
Here's my code and the timings(in bold). As you can see, everything is fast, except for the execution of the sql. I have tested the sql on the server where the servlet runs and it's extremely fast (less than a second). I will try your suggestion of using the ip address for the database to see if that makes a difference.

Also, in this code, I get the connection and create the prepared statement in the init() method. I had it in doGet and someone recommended that I move it. That did not make a difference however. Thanks for any recommendations you can provide.



in init - got connection 18:23:14.444



in init - created prepared statement 18:23:14.444



step 1 19:9:37.136



step 2 19:14:39.76



step 3 19:14:40.961



step 4 19:14:40.961



step 5 19:14:42.648

Dan Walin
Ranch Hand

Joined: Nov 11, 2003
Posts: 109
Here's my code and the timings(in bold). As you can see, everything is fast, except for the execution of the sql. I have tested the sql on the server where the servlet runs and it's extremely fast (less than a second). I will try your suggestion of using the ip address for the database to see if that makes a difference.

Also, in this code, I get the connection and create the prepared statement in the init() method. I had it in doGet and someone recommended that I move it. That did not make a difference however. Thanks for any recommendations you can provide.



in init - got connection 18:23:14.444



in init - created prepared statement 18:23:14.444



step 1 19:9:37.136



step 2 19:14:39.76



step 3 19:14:40.961



step 4 19:14:40.961



step 5 19:14:42.648



calling pstmt.setString 19:9:37.139



after sql call got: CSOFLR01 SUBDIREC.00119:14:39.75

Dan Walin
Ranch Hand

Joined: Nov 11, 2003
Posts: 109
Here's my code and the timings(in bold). As you can see, everything is fast, except for the execution of the sql. I have tested the sql on the server where the servlet runs and it's extremely fast (less than a second). I will try your suggestion of using the ip address for the database to see if that makes a difference.

Also, in this code, I get the connection and create the prepared statement in the init() method. I had it in doGet and someone recommended that I move it. That did not make a difference however. Thanks for any recommendations you can provide.



in init - got connection 18:23:14.444



in init - created prepared statement 18:23:14.444



step 1 19:9:37.136



step 2 19:14:39.76



step 3 19:14:40.961



step 4 19:14:40.961



step 5 19:14:42.648



calling pstmt.setString 19:9:37.139



after sql call: 19:14:39.75

Dan Walin
Ranch Hand

Joined: Nov 11, 2003
Posts: 109
It was the wrong driver. This application is running on one iSeries and accessing the database on another iSeries. Here is what I found at:


JDBC Drivers FAQ

Which JDBC driver should I use?

The Native driver runs only on the OS/400 JVM, but performs better than the Toolbox driver when the data is on the same machine. The Toolbox driver runs on any JVM (including the JVM shipped with OS/400). The current general advice is this: If your program is only intended to run on the OS/400 JVM and the data is on the same machine, use the Native driver. If your program is intended to run on other JVMs or the Java program is on one iSeries and the data is on a different iSeries, use the Toolbox driver.

The best advice is to avoid tying (hardcoding) your program to use a specific JDBC driver. Instead, make the JDBC driver configurable at runtime. Users can then specify whichever JDBC driver makes sense in their environment.


By switching from :

com.ibm.db2.jdbc.app.DB2Driver
to
com.ibm.as400.access.AS400JDBCDriver

The time to run the sql dropped from 5 minutes to a few seconds.
Jared Richardson
author
Ranch Hand

Joined: Jun 22, 2005
Posts: 113
The time to run the sql dropped from 5 minutes to a few seconds.


Good job!

Remember though it usually takes a few moments to create the connection. If that matters to you, use a persistent connection instead of having the servlet reconnect every time.
Dan Walin
Ranch Hand

Joined: Nov 11, 2003
Posts: 109
Yes - actually I think I've done that by creating the Connection in the init() method of the servlet.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61764
    
  67

creating the Connection in the init() method of the servlet.


So how do you synchronize multiple threads of the servlet instance sharing this connection?

You really ought to check out container-managed connection pooling.
[ June 27, 2005: Message edited by: Bear Bibeault ]

[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Jared Richardson
author
Ranch Hand

Joined: Jun 22, 2005
Posts: 113
Originally posted by Dan Walin:
Yes - actually I think I've done that by creating the Connection in the init() method of the servlet.


Catch me if I'm wrong here becuase it's been a while since I've tried what you are doing but if memory serves, Tomcat doesn't promise you that only a single instance will be run. you'd except that the same servlet would be used over and over... and single-user light-weight tests will appear to bear out these assumptions. However, when you put the system under real load (usually production), you'll find the servlet instances are often used again sporadically and new connection overhead starts showing up when you least expect it.
Dan Walin
Ranch Hand

Joined: Nov 11, 2003
Posts: 109
On the question about multiple threads sharing the connection, my answer is "I don't know" - I have not tested multiple users yet. It sounds like it might make more sense to create the connection in doGet maybe. On the next question about issues with Tomcat and init() - we're using Websphere Application Server. Not sure if that has the same issues or not.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61764
    
  67

n the question about multiple threads sharing the connection, my answer is "I don't know"


I'll give you the answer: you've got a problem.

If you create the connection in init, and then use it in doGet/doPost, the connection gets reused by all the threads that call the do methods. Disaster.

Also, how/when are you closing the connection?

Do yourself a huge favor and look into container-managed connection pooling. It will save your behind.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: slow query using JDBC in servlet