如何加快 T-SQL 查询速度

Question

我开发了几个 T-SQL 存储过程，用于迭代相当多的数据。 第一个需要几分钟的时间来运行一年多的数据，这对于我的目的来说是很好的。 第二个使用相同的结构/算法，尽管数据更多，但需要两个小时，这是难以忍受的。

我正在使用 SQL 服务器和查询分析器。 是否有任何分析工具？如果有，它们是如何工作的？

或者，根据下面的伪代码，对如何提高速度有什么想法？ 简而言之，我使用游标来迭代来自直接 SELECT（来自几个连接表）的数据。 然后，我根据这些值构建一条 INSERT 语句，并将结果插入到另一个表中。 某些 SELECTed 变量在 INSERTion 之前需要进行一些操作。 其中包括从日期值中提取一些日期部分、一些基本的浮点运算和一些字符串连接。

--- 粗略算法/伪代码

DECLARE <necessary variables>
DECLARE @cmd varchar(1000)
DECLARE @insert varchar(100) = 'INSERT INTO MyTable COL1, COL2, ... COLN, VALUES('

DECLARE MyCursor Cursor FOR
    SELECT <columns> FROM TABLE_1 t1
    INNER JOIN TABLE_2 t2 on t1.key = t2.foreignKey
    INNER JOIN TABLE_3 t3 on t2.key = t3.foreignKey

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @VAL1, @VAL2, ..., @VALn
WHILE @@FETCH_STATUS = 0
BEGIN
   @F = @VAL2 / 1.1  --- float op
   @S = @VAL3 + ' ' + @VAL1
   SET @cmd = @insert
   SET @cmd = @cmd + DATEPART(@VAL1) + ', '
   SET @cmd = @cmd + STR(@F) + ', '
   SET @cmd = @cmd + @S + ', '
   SET @cmd = @cmd + ')'
   EXEC (@cmd)
   FETCH NEXT FROM MyCursor @VAL1, @VAL2, ..., @VALn
END
CLOSE MyCursor
DEALLOCATE MyCursor

Answer 1

SQl Server 还附带了一个名为 SQL Server Profiler 的分析工具。 它是 SSMS 中工具下菜单上的第一个选择。

Answer 2

失去光标。 现在。 （请参阅此处了解原因：为什么在 SQL Server 中使用游标被认为是不好的做法吗？）。

毫不粗鲁地说，您似乎正在采用程序程序员的 SQL 方法，这几乎总是次优的。

如果您所做的事情很复杂并且您没有信心，我会分三个步骤进行：

1）使用 insert 或 select into 将核心数据选择到临时表中。

2）使用更新进行操作 - 您可能只需更新现有列即可执行此操作，或者您可能需要在创建临时表时以正确的格式添加一些额外的列。 如果需要，您可以使用多个更新语句进一步分解它。

3）将其选择到您想要的任何位置。

如果您想将其全部作为一个步骤调用，那么您可以将整个过程包装到一个存储过程中。

这使得调试变得容易，并且其他人在需要时也可以轻松合作。 您可以将更新分解为单独的步骤，以便快速确定出现问题的位置。

也就是说，从外观上看，我不相信您所做的事情不能在单个插入语句中完成。 它可能没有吸引力，但我相信它可以做到：

INSERT INTO NewTable
DATEPART(@VAL1) DateCol, 
@STR(@VAL2 / 1.1) FloatCol,
@VAL3 + ' ' + @VAL1 ConcatCol
FROM TABLE_1 t1
INNER JOIN TABLE_2 t2 on t1.key = t2.foreignKey
INNER JOIN TABLE_3 t3 on t2.key = t3.foreignKey

DateCol、FloatCol 和 ConcatCol 是您希望列具有的任何名称。 尽管不需要它们，但最好将它们分配为（a）它可以让您更清楚地了解您正在做什么以及（b）某些语言难以处理未命名的列（并以非常不清楚的方式处理它）。

Answer 3

摆脱游标和动态sql：

INSERT INTO MyTable 
        (COL1, COL2, ... COLN)
    SELECT 
        <columns>
            ,DATEPART(@VAL1) AS DateCol
            ,@STR(@VAL2 / 1.1) AS FloatCol
            ,@VAL3 + ' ' + @VAL1 AS ConcatCol
        FROM TABLE_1        t1    
        INNER JOIN TABLE_2  t2 on t1.key = t2.foreignKey
        INNER JOIN TABLE_3  t3 on t2.key = t3.foreignKey

Answer 4

是否有任何分析工具，如果
那么，它们是如何工作的呢？

要回答有关查询调优工具的问题，您可以使用 TOAD for SQL Server 来协助查询调优。

我真的很喜欢这个工具，因为它会以大约 20 种不同的方式运行您的 SQL 语句，并比较执行计划，以便您确定最佳的一种。 有时我会惊讶于它对我的语句的优化，而且效果非常好。

更重要的是，当我在未来编写的脚本中使用这些技巧时，我已经使用它成为了更好的 t-sql 编写者。 我不知道 TOAD 如何使用这个脚本，因为正如其他人提到的那样，它使用游标，而我不使用它们，所以从未尝试过优化它。

TOAD 是 SQL Server 功能的一个巨大工具箱，查询优化只是其中的一小部分。 顺便说一句，我与 Quest Software 没有任何关系。

Answer 5

首先要做的事情 - 摆脱游标...

INSERT INTO MyTable COL1, COL2, ... , COLN
SELECT ...cols and manipulations...
FROM TABLE_1 t1
INNER JOIN TABLE_2 t2 on t1.key = t2.foreignKey
INNER JOIN TABLE_3 t3 on t2.key = t3.foreignKey

大多数事情应该可以直接在 TSQL 中实现（没有示例很难确定） - 并且您可以考虑使用 UDF 来进行更复杂的操作。