wood burning stoves 2.0*
The moose likes JDBC and the fly likes Performance slow down Because of PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Performance slow down Because of PreparedStatement" Watch "Performance slow down Because of PreparedStatement" New topic
Author

Performance slow down Because of PreparedStatement

Ronnie Phelps
Ranch Hand

Joined: Mar 12, 2001
Posts: 329

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30057
    
149

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?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Dave Salter
Ranch Hand

Joined: Jul 20, 2005
Posts: 292

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

Joined: Jan 10, 2002
Posts: 60743
    
  65

You have used profiling tools to pin-point the bottleneck?


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Ronnie Phelps
Ranch Hand

Joined: Mar 12, 2001
Posts: 329
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

Joined: Mar 12, 2001
Posts: 329
I'm going to send you guys an email just in case you forgot about me.

Thanks
Ronnie Phelps
Ranch Hand

Joined: Mar 12, 2001
Posts: 329
Okay I couldn't figure out how to email you guys. Are any of you guys available?
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30057
    
149

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30057
    
149

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

Joined: Jul 20, 2005
Posts: 292

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

Joined: Mar 12, 2001
Posts: 329
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

Joined: Nov 04, 2001
Posts: 1871
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

Joined: Mar 12, 2001
Posts: 329
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.
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Performance slow down Because of PreparedStatement
 
Similar Threads
drop down menu usinng optionCollections
select vs desc
benefit of PreparedStatement if closed due to error: maximum open cursors exceeded
Whats the Importance Of Sequence Of Conditions In an Sql Query
Problem with UNION query