Bind Variables Usage (Parameterized Queries in SQL Server)

We cannot emphasize enough how important it is to use parameterized queries (SQL Server lingo) – these are also called as queries that use bind variables ( Oracle lingo). These are critical in dynamic SQLs. The reason that you do not always have to use bind variables in static SQL is that static SQL is, by definition, fixed at compile time. Every time you run such a program, the static SQL will be the same, and it will be 100-percent shareable. So, using static SQL, a query such as:

select * from foo where col1 = ‘some value’

will always be

select * from foo where col1 = ‘some value’

No matter how many times you run that program, that same old query will be executed (reused execution plan).

Using dynamic SQL, you are able to construct statements like the following:

select * from foo where col1 = ‘a’
select * from foo where col1 = ‘b’
select * from foo where col1 = ‘c’

and so on. Each statement is brand new and unique with the only difference being the value that is being used against the column. An example of a parameterized query for the above scenario will be:

select * from foo where col1 = : x;

The parameter x can have different values (a, b, c) at execution time. If you are not using bind variables, then each of these queries will have to be parsed, qualified (names resolved), security checked, optimized and so on. In short, it will be compiled. Every unique statement you execute will have to be compiled every time. Using parameters or parameter markers in the code increases the ability of the relational engine to match new SQL Statements with existing execution plans in the cache thus increasing performance since the hard-parse and compilation steps are now avoided and only the execution step of the SQL occurs.

Given the excessively high amount of latching that not using bind variables creates, scaling up without using the bind variables would be hard in any OLTP system. As you add more and more users without using bind variables, the runtime differences compared with using bind variables will skyrocket. Another term that is used for queries using bind variables is parameterized queries. By using bind variables, the same statement gets parsed and compiled once and executed many times because of the plan re-use. If you are not using parameterized queries, then you will start noticing procedure cache (SQL Server) and shared pool (Oracle) issues as well. Oracle atleast allows us to configure the shared pool, SQL Server has no configuration for putting a cap on the procedure cache.

Another advantage (besides the BIG performance advantage) of using bind variables in the code is security. Using parameters or parameter markers to hold values by end users is more secure than concatenating the values into a string that is then executed using either a data-access API method, or the sp_executesql stored procedure (SQL Server). Not using parameterized queries also exacerbates locking issues in the system because of poor response timings due to the need for repeating the parsing and the compilation steps in the SQL statement processing.

There are scenarios though where bind variable usage can lead to issues and under which you might consider using a constant – see this post for an example of such a scenario.