Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Performance slow down Because of PreparedStatement

 
Ronnie Phelps
Ranch Hand
Posts: 329
  • Mark post as helpful
  • send pies
  • 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.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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?
 
Dave Salter
Ranch Hand
Posts: 293
Java Mac OS X Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65220
95
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • 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
  • 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 usingAssume 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
  • 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
  • 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
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • 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
Java Mac OS X Netbeans IDE
  • Mark post as helpful
  • send pies
  • 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
  • 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.
 
Maulin Vasavada
Ranch Hand
Posts: 1873
  • Mark post as helpful
  • send pies
  • 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
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic