时间戳

首页 > 解决方案 > 如何在 sql server 上运行时优化 sql

sql-server - 如何在 sql server 上运行时优化 sql

问题描述

如何优化下面相同的代码sql：

Declare @i int=0;
while @i<10000
Begin
Set @i=@i+1;
set @ID=(Select top(1) ID from Products.dbo.TempID where IsUpdate=0);
Delete from Attributes where ID=@ID;
Delete from Products where ID=@ID
Delete from Attributes_Archive where ID=@ID
update TempID set IsUpdate=1 where ID=@ID
end

标签： sql-server

解决方案

使用基于集合的查询而不是循环。在这种情况下，整个查询将只执行一次，而不是执行 10000 次的实现。就像是：

DELETE  FROM Attributes
WHERE   ID IN ( SELECT  ID
                FROM    Products.dbo.TempID
                WHERE   IsUpdate = 0
                        AND ID < 10000 );
DELETE  FROM Products
WHERE   ID IN ( SELECT  ID
                FROM    Products.dbo.TempID
                WHERE   IsUpdate = 0
                        AND ID < 10000 );
DELETE  FROM Attributes_Archive
WHERE   ID IN ( SELECT  ID
                FROM    Products.dbo.TempID
                WHERE   IsUpdate = 0
                        AND ID < 10000 );
UPDATE  TempID
SET     IsUpdate = 1
WHERE   ID IN ( SELECT  ID
                FROM    Products.dbo.TempID
                WHERE   IsUpdate = 0
                        AND ID < 10000 );

或者您可以先获取要删除/更新的 ID，然后使用此列表，如下所示：

SELECT  ID
INTO    #ids
FROM    Products.dbo.TempID
WHERE   IsUpdate = 0
        AND id < 10000;

DELETE  FROM Attributes
WHERE   ID IN ( SELECT  ID
                FROM    #ids );
...

推荐阅读