performance of parameterized queries for different db's

Question

A lot of people know that it is important to use parameterized queries to prevent sql injection attacks.

Parameterized queries are also much faster in sqlite and oracle when doing online transaction processing because the query optimizer doesn't have to reparse every parameterized sql statement before executing. I've seen sqlite becoming 3 times faster when you use parameterized queries, oracle can become 10 times faster when you use parameterized queries in some extreme cases with a lot of concurrency.

How about other db's like mysql, ms sql, db2 and postgresql?

Is there an equal difference in performance between parameterized queries and literal queries?

Answer 1

I've nearly always seen an increase in speed - but only the first time generally. After the plans are loaded and cached I would have surmised that the various db engines will behave the same for either type.

Answer 2

With respect to MySQL, MySQLPerformanceBlog reported some benchmarks of queries per second with non-prepared statements, prepared statements, and query cached statements. Their conclusion is that prepared statements is actually 14.5% faster than non-prepared statements on MySQL. Follow the link for details.

Of course the ratio varies based on the query.

Some people suppose that there's some overhead because you're making an extra round-trip from the client to the RDBMS -- one to prepare the query, the second to pass parameters and execute the query.

But the reality is that these are false assumptions made without actually measuring. I've never heard of prepared statements being slower in any brand of database.