• 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

Performance slow down Because of PreparedStatement

 
Ranch Hand
Posts: 329
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I understand that PreparedStatements are supposed to make your queries faster when you are using the same query multiple times and passing a different arguement. But for some reason it appears that switching from using a Statement to using a PreparedStatment has significantly slowed my process down. I'm performing the same query millions of times and my application is crawling even after the statement has been executed for the first time. I'm running against a sybase database using JDBC and jconn2.jar. If anyone knows why prepared staments may be slowing me down, please let me know.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ronnie,
Can you post the SQL that is going into your PreparedStatement? That will help us give you better advice.

Off that bat, I can think of:
1) Is the SQL the same in the prepared statement each time?
2) Are you running out of memory?
 
Ranch Hand
Posts: 293
Mac OS X Netbeans IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Are you instantiating a new PreparedStatement each time you query the database?

I've seen very minimal changes in performance when this is done.

As Jeanne said, post some code and SQL and we'll try to help.

Cheers,

Dave.
 
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
You have used profiling tools to pin-point the bottleneck?
 
Ronnie Phelps
Ranch Hand
Posts: 329
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The SQL in the PreparedStatement stays the same.

I am not receiving any out of memory exceptions.

I'm not instantiating a new PreparedStatement. I'm actually reusing the same prepared statement but I'm changing the arguements.

I haven't used any profiling tools.

Here is an example of the code i'm using:(Assume all necessary exception handling.


public class DataSelector{
private Connection conn;
protected PreparedStatement statement1PS;
protected PreparedStatement statement2PS;
protected PreparedStatement statement3PS;

public void DataSelector() {
createDatabaseConnectionAndPrepareStatements();

}

protected Connection createDatabaseConnectionAndPrepareStatement(){
if ((conn == null)) {
Class.forName("drivername").newInstance();
conn=
DriverManager.getConnection(<login info>);

statement1PS = conn.prepareStatement("select * from Table1 where field1 = ?");
statement2PS = conn.prepareStatement("select * from Table2 where field2 = ?");
statement3PS = conn.prepareStatement("select * from Table3 where field3 = ?");

}
}

public void queryData(String value1, String value2, String value3){
createDatabaseConnectionAndPrepareStatements();

statement1PS.setString(1, value1);

ResultSet emailResult = statement1PS.executeQuery();
<do some things with the results>


statement2PS.setString(1, value2);

ResultSet emailResult = statement2PS.executeQuery();
<do some things with the results>

statement3PS.setString(1, value3);

ResultSet emailResult = statement3PS.executeQuery();
<do some things with the results>
}

public void cleanUp() {
if (conn!= null) {
conn.close();
conn = null;
}
statement1PS.close();
statement2PS.close();
statement3PS.close();

}

}



The main class in my app only instantiates one instance of this class.

The one instance is referenced in a loop that passes different arguements to the querydata method.

After processing a huge amount of data the main class will call the cleanup method.

I just decided to switch back to Statement because PreparedStatement was too slow. Hopefully I am using the PreparedStatement incorrectly.
 
Ronnie Phelps
Ranch Hand
Posts: 329
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm going to send you guys an email just in case you forgot about me.

Thanks
 
Ronnie Phelps
Ranch Hand
Posts: 329
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Okay I couldn't figure out how to email you guys. Are any of you guys available?
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ronnie,
Without a profiling tool, it's hard to tell where the bottleneck is. You could try putting some printlns in to see which call is taking the longest.

I am not receiving any out of memory exceptions.


You could still be having a memory issue. For example, if the maching is doing a lot of paging to disk/thrashing it would affect performance. this is something a profiler could tell you. Having said that, I don't think it is the case here. Those statements look pretty simple.

The only thing I can see that might cause a problem is having all the statements open at once. Try preparing one statement, running it and then closing the resource. (And repeat three times) The overhead doesn't come from creating a prepared statement object, so this will be ok.
 
Jeanne Boyarsky
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ronnie,
If you really need to contact someone, you can use the private message function. (The happy face with a question icon at the beginning of the posts.)

This typically isn't necessary and is discouraged though. Most people check up on threads they posted in. Especially Bear and I (the forum moderators.) 24 hours is a good response time (more on weekends) to allow everyone to check the forum.
 
Dave Salter
Ranch Hand
Posts: 293
Mac OS X Netbeans IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ronnie,

Your code looks OK to me. What database are you using? Is your database tuned correctly for multi-user use?

Are you consistently getting poorer performance when using PreparedStatements versus Statements?
 
Ronnie Phelps
Ranch Hand
Posts: 329
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm not sure if my database is tuned correctly but I am using sybase. I'm consistently getting poorer performance. I just decided to use Statement instead.
 
Ranch Hand
Posts: 1873
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Ronnie

I see that the call to initializing method is twice, once from the constructor and once from the queryData().

public void queryData(String value1, String value2, String value3){
createDatabaseConnectionAndPrepareStatements();

In queryData() it will get called as many times as you call queryData() IF your connection objects get 'null' via cleanUp() method...See if that is the case..

Regards
Maulin
 
Ronnie Phelps
Ranch Hand
Posts: 329
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I only call cleanup once when the entire process completes. The statements will only get initialized once even though I'm calling the method more than once because the conn variable will not be null. I appreciate the attempt to help. Let me know if you have anymore suggestions.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic