如何提高T-SQL查询的速度

Question

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

我使用SQL-Server和Query-Analyzer。是否有分析工具，如果有，它们是如何工作的？

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

-粗略算法/伪代码

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

要做的第一件事-去掉光标...

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。

Answer 2

丢掉光标。现在。(原因请看这里：Why is it considered bad practice to use cursors in SQL Server?)。

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

如果你正在做的事情很复杂，而且你没有信心，我会分三个步骤来做：

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

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

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

如果您希望将其作为一个步骤调用，则可以将整个过程包装到一个存储过程中。

这使得调试变得很容易，如果其他人需要，也可以很容易地与他们一起工作。你可以将你的更新分解成单独的步骤，这样你就可以快速识别出哪里出了问题。

也就是说，从外观上看，我不认为您正在做的事情不能在一条insert语句中完成。这可能并不吸引人，但我相信它是可以做到的：

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