Are there downsides to using prepared statements?

Question

I've been reading a lot about prepared statements and in everything I've read, no one talks about the downsides of using them. Therefore, I'm wondering if there are any "there be dragons" spots that people tend to overlook?

Answer 1

Prepared statement is just a parsed and precompiled SQL statement which just waits for the bound variables to be provided to be executed.

Any executed statement becomes prepared sooner or later (it need to be parsed, optimized, compiled and then executed).

A prepared statement just reuses the results of parsing, optimization and compilation.

Usually database systems use some kind of optimization to save some time on query preparation even if you don't use prepared queries yourself.

Oracle, for instance, when parsing a query first checks the library cache, and if the same statement had already been parsed, it uses the cached execution plan instead.

Answer 2

If you use a statement only once, or if you automatically generate dynamic sql statements (and either properly escape everythin or know for certain your parameters have only safe characters) then you should not use prepared statements.

Answer 3

There is one other small issue with prepared statements vs dynamic sql, and that is that it can be harder to debug them. With dynamic sql, you can always just write out a problem query to a log file and run it directly on the server exactly as your program sees it. With prepared statements it can take a little more work to test your query with a specific set of parameters determined from crash data. But not that much more, and the extra security definitely justifies the cost.

Answer 4

in some situations, the database engine might come up with an inferior query plan when using a prepared statement (because it can't make the right assumptions without having the actual bind values for a search).

see e.g. the "Notes" section at

http://www.postgresql.org/docs/current/static/sql-prepare.html

so it might be worth testing your queries with and without preparing statements to find out which is faster. ideally, you would then decide on a per-statement basis whether to use prepared statements or not, although not all ORMs will allow you to do that.

Answer 5

The only downside that I can think of is that they take up memory on the server. It's not much, but there are probably some edge cases where it would be a problem but I'm hard pressed to think of any.