Recurring SQL Queries

Question

What is considered best practice for executing recurring SQL queries? My understanding is to use a parameterized query and turn it into a prepared statement upon first execution. What if this query needs to be executed by multiple threads? Will I need to create a prepared statement for each type of query for each thread? Or is the parsing of SQL statements so efficient nowadays that prepared statements are no longer necessary?

Answer 1

Well, you didn't mention the environment you're using but in general, you can also consider stored procedures (if your DB engine supports it). It has the benefit of building an additional abstraction layer in the database itself thus making the exact database schema less relevant to client applications.

Using parameterized queries is encouraged most of the time, not only for the sake of performance, but for the security against SQL injection and preventing data type conversion issues (localized date time).

Answer 2

Good question - answered one bit at a time.

• What is considered best practice for executing recurring SQL queries?

If the query will be repeated apart from differences in the parameters, then use prepared statements.

• My understanding is to use a parameterized query and turn it into a prepared statement upon first execution.

That is my opinion on what should be done. Classically, the advice was to prepare all queries when the program started. In my view, this was always nonsense; it overloads the server with queries, many of which will not be used in any given run, wasting memory in both client and DBMS. It was always most sensible to prepare statements on demand; when it was first needed, and not unless it was needed. I'd allow an exception for statements that will 'always' be executed - but I'd have to be convinced that 'always' was really close to 100% of the time.

• What if this query needs to be executed by multiple threads? Will I need to create a prepared statement for each type of query for each thread?

That depends on how the different threads communicate with the DBMS. In the DBMS with which I'm familiar, if there is a single connection that the threads all share, then you only need to prepare it once for the single connection. If each thread has its own separate connection, then you need to prepare the statement for each thread separately.

• Or is the parsing of SQL statements so efficient nowadays that prepared statements are no longer necessary?

Machines are fast - yes. And for non-repeated statements, it is not worth worrying about the overhead. But if you are going to execute the query a few million times, then the cost of preparing it a few million times begins to add up. Also, database server machines are usually shared resources, but the statement is likely to be prepared separately for each user, so if you have multiple users hammering the system with repeated queries that are discarded, the server will be too busy preparing queries to execute any of them fast.

So, my answer is "No". Prepared statements are still beneficial when the queries will be repeated often enough - where 'often enough' is probably not all that often. Hundreds of times - use prepared statements. Tens of times - probably use prepared statements. Less than that - maybe do not use prepared statements.