Dynamic Sql is becoming Angel Not Evil !!!.
Ten years ago, there were only two rules in Relational database:
"always use stored procedures" and "never build dynamic SQL (Ad Hoc Query)".
But, the game has changed with the the revolution of ORM and the new generations of sql server starting from SQL server 2005 up to SQL server 2016 release.
It's not amazing to hear an advice like:
Don’t Fear Dynamic SQL. When done correctly, it can be used effectively
Dynamic SQL certainly has some downsides. But when used properly, it has many positive attributes, including the ability to enhance performance.
Conditions for dynamic SQL high performance :
- Run dynamic sql using sp_executesql not EXEC.
- Use parameters for where conditions (DON'T concatenate values)
sp_executesql will use cached plan to get more performance.
ADO.NET send Queries in the client as sp_executeSQL. You can view the generated SQL in SQL Profiler.
What ORM does is dynamically generating CRUD as dynamic SQL and map data into objects to speed development with good performance.
SQL Server has sp_executesql , which is a system stored-procedure that takes a SQL string with parameter markers and a variable number of arguments for the parameters.
So the client issues an RPC request to sp_executesql whenever it has to execute a parameterized statement.
The parameterized sp_executesql call is more likely to have a reusable cached plan.
The stored procedure sp_executesql become the standard procedure for executing queries as dynamic sql generated in ADO.NET even (update/insert/delete) starting from SQL Server 2005.
The contents of dynamic sql with parameter substitution are compiled and executed as an execution plan separate from the execution plan of the batch that called sp_executesql.
Excerpt from MSDN
sp_executesql
sp_executesql can be used instead of stored procedures to execute a Transact-SQL statement many times when the change in parameter values to the statement is the only variation. Because the Transact-SQL statement itself remains constant and only the parameter values change, the SQL Server query optimizer is likely to reuse the execution plan it generates for the first execution.
The dynamic sql is executed as:
EXECUTE sp_executesql @SQLString, @ParmDefinition, @SalesID = @IntVariable;
Who has the power of Generating dynamic sql and run with good performance?
In the client if we run the query:
var ID=1;
string commandText = "select * from products WHERE productid = @ID;";
With ADO.NET:
The generated query is:
exec sp_executesql N'select * from products WHERE productid = @ID;',N'@ID int',@ID=11
Dapper (high performance ORM)
Dapper is a micro-ORM for .NET developed and used by the Stack Overflow team, focusing on raw performance as the primary aim.
The generated query is:
exec sp_executesql N'select * from products WHERE productid = @ID;',N'@ID int',@ID=12
EntityFramework EF 6.3
the query:
var id=1;
Product p = db.Products.FirstOrDefault(t => t.ProductID == id);
The generate dynamic sql :
exec sp_executesql N'SELECT TOP (1)
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent1].[SupplierID] AS [SupplierID],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitsInStock] AS [UnitsInStock],
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent1].[ReorderLevel] AS [ReorderLevel],
[Extent1].[Discontinued] AS [Discontinued]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[ProductID] = @p__linq__0',N'@p__linq__0 int',@p__linq__0=1
and other tools use dynamic sql with high performance.
Conclusion:
Dynamic sql can be used with good performance if it's parameterized and executed with sp_executesql
