带有外部查询的 tsql 循环

Question

我正在循环遍历所有数据库并将结果聚合到聚合数据库中。

在我的循环中，我调用

master.dbo.xp_cmdshell osql C:\whatever.SQL 

随着循环的进行，cmdshell 的执行时间越来越长。如果我停止循环并为一个数据库运行单个聚合，它会快速执行。

我可以在外部 SQL 脚本中添加任何内容以使其运行得更快吗？也许在下一个循环之前要提交并释放记录？或者我应该在每个循环后添加某种暂停？

我想使用外部 SQL 文件，因为它包含许多更新语句，而且对我来说更易于管理。

我的循环方式如下：

Update dbFoo.dbo.tblBar set Processed = 0
Go

WHILE EXISTS ( SELECT ID FROM dbFoo.dbo.tblBar WHERE Processed = 0)
    BEGIN

        SELECT @aRow = MIN(tblBar.ID) FROM dbFoo.dbo.tblBar
        SELECT @aFoo1 = Foo1 FROM dbFoo.dbo.tblBar WHERE ID = @aRow
        SELECT @aFoo2 = Foo2 FROM dbFoo.dbo.tblBar WHERE ID = @aRow
        SELECT @aFoo3 = Foo3 FROM dbFoo.dbo.tblWhatever WHERE Foo = @aFoo

        EXEC RunPreAgg @Foo1 = @aFoo1, @Foo2 = @aFoo2, @Foo3 = @aFoo3, @RetVal = @aRetVal OUTPUT
        SELECT returning = @aRetVal

        UPDATE dbFoo.dbo.tblBar SET Processed = 1 WHERE ID = @aRow
    END

然后 RunPreAgg 存储过程基本上执行以下操作：

if db_id('db' + @Foo1 + '_' + @Foo2) is not null  
    BEGIN
        --This bat file creates the SQL File
        select @sql = 'master.dbo.xp_cmdshell '''+@path+'wwwRunPreAgg.bat ' + @Foo1 + ' ' + @Foo2 + ' ' + @Foo3 + ''''
        exec( @sql )

        --execute
        select @sql = 'master.dbo.xp_cmdshell ''osql -E -o '+@path+'output\tmp'+@Foo1+'_'+@Foo2+'.txt -i '+@path+'tmp' + @Foo1 + '.SQL'''
        exec( @sql )

        --This erases the SQL File
        select @sql = 'master.dbo.xp_cmdshell '''+@path+'wwwCleanup.bat ' + @Foo1 + ' ' + @Foo2 + ''''
        exec( @sql )

        Set @retval = 'Done!'
    END
ELSE
    BEGIN
        Set @retval = 'Err: No DataBase'
    END 

更改变量名称以保护无辜者。代码运行良好，我只需要优化。

Answer 1

如果循环性能给您带来了麻烦，您可以尝试减少选择的数量。通常我不喜欢游标，但你的循环可能会受益于游标。您可以将循环所需的所有值选择到内存中，然后循环遍历这些值，而不必每个循环运行 3 或 4 个选择（当然，如果性能下降发生在 RunPreAgg SP 内部，那么这将无济于事） ：

DECLARE cFoos CURSOR FOR 
    SELECT tblBar.ID, tblBar.Foo1, tblBar.Foo2, tblWhatever.Foo3
    FROM dbFoo.dbo.tblBar
        INNER JOIN dbFoo.dbo.tblWhatever
            ON tblWhatever.Foo = tblBar.Foo
    WHERE tblBar.Processed = 0;

OPEN cFoos;
FETCH NEXT FROM cFoos INTO @aRow, @aFoo1, @aFoo2, @aFoo3;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC RunPreAgg @Foo1 = @aFoo1, @Foo2 = @aFoo2, @Foo3 = @aFoo3, @RetVal = @aRetVal OUTPUT
    SELECT returning = @aRetVal

    UPDATE dbFoo.dbo.tblBar SET Processed = 1 WHERE ID = @aRow

   FETCH NEXT FROM cFoos INTO @aRow, @Foo1, @Foo2, @Foo3;
END

CLOSE cFoos;
DEALLOCATE cFoos;