SQL: Improve Dynamic(Ad hoc) SQL Query Performance

Dynamic(Ad hoc) SQL query can be as performant as stored procedures if you use it correctly since SQL server can cache the compiled query plan and re-use it next time. If not, SQL server may have to re-compile the query every time, and which can be sometimes 100 times slower!

For example:

    String query = "SELECT * FROM employees e INNER JOIN EmployeeTerritories et ON e.employeeid = et.employeeid WHERE e.Employeeid = @id";
    sqlCmd.CommandType = System.Data.CommandType.Text;
    SqlParameter idParam = new SqlParameter("@id", System.Data.SqlDbType.Int, 0);
    sqlCmd.Parameters.Add(idParam);

    sqlCmd.Parameters[0].Value = 100;
    sqlReader = sqCmd.ExecuteReader();

If you use SQL tracer, you will see SQL server executes following statement:

   
    EXEC sp_executesql N'SELECT * FROM employees e INNER JOIN EmployeeTerritories et ON e.employeeid = ee.employeeid WHERE e.Employeeid = @id', N'@id int',@id=100

In this case, SQL server can cache the query plan and re-used it effectively. Because the statement is parameterized, even the value of @id changes, the compiled statement remains same and can be always re-used.

A common mistake of using Dynamic SQL is like below:

    String query = "SELECT * FROM employees e INNER JOIN EmployeeTerritories et ON e.employeeid = ee.employeeid WHERE e.Employeeid = " + id.ToString();
    sqlCmd.CommandType = System.Data.CommandType.Text;
    sqlReader = sqCmd.ExecuteReader();

If you trace again, this time you will see SQL server executes the following instead:

    SELECT * FROM employees e INNER JOIN EmployeeTerritories et ON e.employeeid = ee.employeeid WHERE e.Employeeid = 100

SQL server can cache and re-use above statement as well. However, since the statement is not parameterized, if the Employeeid changes, SQL server will have to re-compile the statement. The consequence is low cache hit and SQL server will have a lot of recompiling, the overall performance will be poor.

So the take away here is, you can use Dynamic SQL query, but do use SqlParameter to make your SQL statement parameterized.

SQL: Improve Dynamic(Ad hoc) SQL Query Performance

See also