Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes PreparedStatement overhead 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 "PreparedStatement overhead" Watch "PreparedStatement overhead" New topic
Author

PreparedStatement overhead

Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8904

Hi,
It is said - When PreparedStatement executes for the first time it is slower than Statement? Can some one explain me why.
Of course subsequent executions of the PreparedStatment will be faster.


Groovy
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

A PreparedStatement is slower because it gets prepared first.
There is a related article here (from the ServerSide), but the short answer is:
What you write in a SQL statement has little relation to what the database executes (apart from the specifics). It also needs to look at its configuration setting and any extra data it knows (indices etc) to create what it believes to be the best way to perform you query.
If you ask it to perform "select * from table where value=2" it will do all this work and the work it does will only be useful the next time you run exactly the same query. THis is not terribly useful.
If you tell it to prepare to run "select * from table but I haven't decided what the value is yet", it will work out the fastest way to perform the query based on that variable for all values.
Dave
Michael Zalewski
Ranch Hand

Joined: Apr 23, 2002
Posts: 168
When you create a prepared statement, the database will have to send a message back to your driver. Probably internally it allocates a handle to the compiled version of the statement and the execution plan. Then when you execute the prepared statement, there is another message sent back and forth between your driver and the server. That's two network messages from the client, and two replies from the server.
When you use dynamic SQL (which is what a Statement turns out to be on the server end), there is only one message needed from the client, and one reply from the server. Furthermore, the server will not need to keep a compiled version of the statement, nor an execution plan. So that's why a simple Statement might be better for low volume, one-time use.
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8904

Hi,
I think that execution plan of Statment are stored as well.
Furthermore, the server will not need to keep a compiled version of the statement, nor an execution plan.
Michael Zalewski
Ranch Hand

Joined: Apr 23, 2002
Posts: 168
Originally posted by Pradeep Bhat:
Hi,
I think that execution plan of Statment are stored as well.

A Statement must make an execution plan. All queries (and even all DML) need an execution plan. But there is no reason for the server to keep the execution plan in memory once the Statement has been executed. For a PreparedStatement, the server does have a reason to hold the execution plan -- at least until the client closes the PreparedStatement or Connection.
My point was that a PreparedStatement has a little extra overhead on the server side, because the server will keep the execution plan in memory until the PreparedStatement is closed. With a regular Statement, the server is free to release the execution plan as soon as the query (or statement) has been executed. So a PreparedStatement has a little more overhead on the server side.
Actually, your mileage may vary depending on the RDBMS that you use. For example, Oracle and DB2 will both keep the execution plan on the server side, even for a Statement, even after the Statemen has been executed. But even here, the database can release the execution plan if it belongs to a Statement that has already been executed. If the execution plan belongs to a PreparedStatement, the server does not know when or if that statement will be executed again.
 
Don't get me started about those stupid light bulbs.
 
subject: PreparedStatement overhead
 
Similar Threads
Problem with quotes (escape characters)
error of missing values
Update query
How to insert a null into a timezone column
jdbc