aspose file tools*
The moose likes JDBC and the fly likes When does a PreparedStatement produce better performance than a Statement? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "When does a PreparedStatement produce better performance than a Statement?" Watch "When does a PreparedStatement produce better performance than a Statement?" New topic
Author

When does a PreparedStatement produce better performance than a Statement?

William Stafford
Ranch Hand

Joined: Dec 13, 2004
Posts: 109
Our application builds a where clause dynamically, based on user inputs. There are at least 50 different combinations yielding potentially 50 different queries. The basic logic (in a Servlet) is

List<resultObj> list = runQuery (buildQuery(requestParams)) ;
// do something with the results

Since each request would result in a new prepared statement, even if the query was the same as any previous query, it seems to me that there would be no time savings with a prepared statement because the prepared statements are not cached anywhere. Each request is essentially starting from square one, i.e. there is no pre-compiled statement that just needs to be provided with params. A prepared statement would be advantageous if the results from above were used to do an update. For example:
PreparedStatement p = "update table where id=? set retrievedCount=(retrievedCount+1);
while(not at list end) {
p.setInt(1,list.next().getId())
// run the update query
}

Note that I'm ignoring the fact that prepared statements make sql injection much less likely. This alone would be a good reason for using prepared statements but my focus is on performance.

So, the question is: in the first scenario above, would PreparedStatement provide any performance advantage over Statement?

Thanks for any advice or ideas,
-=beeky
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

Example (take care: pseudocode):

These two calls are different for your database. It has to "compile/optimize/determine execution path" twice:

select name from users where id = 1;
execute();
select name from users where id = 2;
execute();

These two calls are the same for your database. Most databases can reuse the "compile/optimize/determine execution path" results from call 1:

select name from users where id = ?;
setInt(1,1);
execute();
setInt(1,2);
execute();




OCUP UML fundamental and ITIL foundation
youtube channel
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: When does a PreparedStatement produce better performance than a Statement?