NetInverse Developers Blog

Dynamic(Ad hoc) SQL query can be as performant as stored procedures if you use it correctly. SQL server compiles it, caches the compiled query plan and re-uses 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 EmployeeDetails 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 the SQL server executes following statement:

 EXEC sp_executesql N'SELECT * FROM employees e
 INNER JOIN EmployeeDetails 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 EmployeeDetails 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 EmployeeDetails 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 dynamic SQL statement parameterized.