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.