PreparedStatement overhead
posted 14 years ago
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
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
Posts: 168
posted 14 years ago
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.
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.
Michael Zalewski
Ranch Hand
Posts: 168
posted 14 years ago
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.
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.
