Should I always prefer working with prepared SQL statements, for performance benefits?

Question

My understanding is that a prepared statement is compiled on the server once, thus saving the overhead of repeating parsing, optimization etc. Apparently, I should always prefer using prepared statements for queries that run more than once.

Are there any cons to this approach?

I am using ODBC (libodbc++) from C++ to MySQL.

Answer 1

Prepared Statements:

Why use prepared statements?

There are numerous advantages to using prepared statements in your applications, both for security and performance reasons.

Prepared statements can help increase security by separating SQL logic from the data being supplied. This separation of logic and data can help prevent a very common type of vulnerability called an SQL injection attack. Normally when you are dealing with an ad hoc query, you need to be very careful when handling the data that you received from the user. This entails using functions that escape all of the necessary trouble characters, such as the single quote, double quote, and backslash characters. This is unnecessary when dealing with prepared statements. The separation of the data allows MySQL to automatically take into account these characters and they do not need to be escaped using any special function.

The increase in performance in prepared statements can come from a few different features. First is the need to only parse the query a single time. When you initially prepare the statement, MySQL will parse the statement to check the syntax and set up the query to be run. Then if you execute the query many times, it will no longer have that overhead. This pre-parsing can lead to a speed increase if you need to run the same query many times, such as when doing many INSERT statements.

(Note: While it will not happen with MySQL 4.1, future versions will also cache the execution plan for prepared statements, eliminating another bit of overhead you currently pay for each query execution.)

The second place where performance may increase is through the use of the new binary protocol that prepared statements can use. The traditional protocol in MySQL always converts everything into strings before sending them across the network. This means that the client converts the data into strings, which are often larger than the original data, sends it over the network (or other transport) to the server, which finally decodes the string into the correct datatype. The binary protocol removes this conversion overhead. All types are sent in a native binary form, which saves the conversion CPU usage, and can also cut down on network usage.

When should you use prepared statements? Prepared statements can be useful for all of the above reasons, however they should not (and can not) be used for everything in your application. First off, the type of queries that they work on is limited to DML (INSERT, REPLACE, UPDATE, and DELETE), CREATE TABLE, and SELECT queries. Support for additional query types will be added in further versions, to make the prepared statements API more general.

-> Sometimes prepared statements can actually be slower than regular queries. The reason for this is that there are two round-trips to the server, which can slow down simple queries that are only executed a single time. In cases like that, one has to decide if it is worth trading off the performance impact of this extra round-trip in order to gain the security benefits of using prepared statements.

Answer 2

almost always.

http://use-the-index-luke.com/sql/where-clause/bind-parameters