sql - 动态 sql 性能和实现建议？

Question

我正在开发一项需要为区域提供报告数据的新服务。每个区域都有自己的数据库表，每个数据库表都有一些同名的列，如 ReportDate。不同的表会有很多不同/独特的列，这就是为什么我为每个区域创建一个单独的表。将有大约 10 个区域表。

我正在考虑为我的存储过程使用动态 sql，这样我就可以编写一个简单的查询，然后对每个表运行它。与编写 10 个对所有表执行相同 SQL 的单独查询相比，这会更快、更容易编写和维护。

SS 是否经过优化以处理此类实现？您是否看到任何潜在的性能问题？我应该采取任何具体步骤来优化此实现的性能吗？这里有没有人认为动态 sql 出于某种原因是“邪恶的”？我指的是完全在 SS 中完成的动态 sql，而不是传入动态 sql 语句的外部应用程序。

Answer 1

Dynamic Sql 正在成为 Angel Not Evil !!!

十年前，关系型数据库只有两条规则:“总是使用存储过程”和“从不构建动态 SQL(即席查询)”。

但是，随着 ORM 的革命和从 SQL Server 2005 到 SQL Server 2016 发布的新一代 SQL Server，游戏规则发生了变化。

听到这样的建议并不奇怪:

Don’t Fear Dynamic SQL. When done correctly, it can be used effectively

动态 SQL 肯定有一些缺点。但如果使用得当，它具有许多积极的特性，包括提高性能的能力。

动态SQL高性能的条件:

• 使用 sp_executesql 而非 EXEC 运行动态 sql。
• 为 where 条件使用参数(不要连接值)

sp_executesql 将使用缓存的计划来获得更高的性能。

ADO.NET 在客户端作为 sp_executeSQL 发送查询。您可以在 SQL Profiler 中查看生成的 SQL。

ORM 的作用是将 CRUD 动态生成为动态 SQL，并将数据映射到对象中，从而以良好的性能加速开发。

SQL Server 有 sp_executesql ，这是一个系统存储过程，它采用带有参数标记的 SQL 字符串和可变数量的参数参数。

因此，每当客户端必须执行参数化语句时，它就会向 sp_executesql 发出 RPC 请求。

参数化的 sp_executesql 调用更有可能具有可重用的缓存计划。

存储过程 sp_executesql 成为执行查询的标准过程，作为在 ADO.NET 中生成的动态 sql，甚至从 SQL Server 2005 开始(更新/插入/删除)。

带有参数替换的动态sql的内容作为一个执行计划被编译和执行，与调用sp_executesql的批处理的执行计划分开。

摘自MSDN sp_executesql

当语句的参数值更改是唯一的变化时，可以使用 sp_executesql 代替存储过程多次执行 Transact-SQL 语句。由于 Transact-SQL 语句本身保持不变，只有参数值发生变化，因此 SQL Server 查询优化器很可能会重用它为第一次执行生成的执行计划。

动态sql执行为:执行 sp_executesql @SQLString, @ParmDefinition, @SalesID = @IntVariable;

谁有能力生成动态sql并以良好的性能运行？

如果我们运行查询，在客户端中:

  var ID=1;
  string commandText = "select * from products WHERE productid = @ID;";

使用 ADO.NET:

生成的查询是:

 exec sp_executesql N'select * from products WHERE productid = @ID;',N'@ID int',@ID=11

Dapper(高性能 ORM)

Dapper 是 Stack Overflow 团队开发和使用的适用于 .NET 的微型 ORM，以原始性能为主要目标。

生成的查询是:

  exec sp_executesql N'select * from products WHERE productid = @ID;',N'@ID int',@ID=12

Entity Framework EF 6.3

查询:

var id=1;
Product p = db.Products.FirstOrDefault(t => t.ProductID == id);

生成动态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

等工具使用高性能的动态sql。

结论:动态sql如果用sp_executesql参数化执行，可以使用性能好