主页 / dba / 问题 / 188669
Accepted
Vorster
Asked: 2017-10-18 07:13:57 +0800 CST

如何重写此存储过程以不使用游标？

  • 772

我想更改我的存储过程，使其不再使用游标来运行。我怎样才能更好地编写这个存储过程？

我正在使用游标来编译动态 SQL 以查询我从列表创建的服务器以跟踪事务日志备份历史记录。

ALTER PROC [dbo].[spLogBackup]

AS

TRUNCATE TABLE dbo.tLogBackup

DECLARE servers_cursor CURSOR
FOR
select  distinct LinkedserverName
from dbo.Environment
join master..sysservers on srvname COLLATE DATABASE_DEFAULT  = LinkedserverName COLLATE DATABASE_DEFAULT
where LinkedServerName not in ('TestDB')

and ServerUse in ('PROD', 'DR')
and IncludeInstats = 1
order by LinkedServerName

OPEN servers_cursor
DECLARE @Servername varchar(250)

DECLARE @sql varchar(8000)

FETCH NEXT FROM servers_cursor INTO @ServerName
WHILE (@@FETCH_STATUS = 0)
BEGIN
    --Looping throught the servers.
    set @sql = 'insert into dbo.tLogBackup (servername, rundate, runtime, jobname, Status, CurrentStatus, enabled) select ''' + @ServerName + ''',last_run_date, last_run_time, name, last_run_outcome, current_execution_status, enabled
     from openquery([' + @ServerName + '],''exec msdb.dbo.sp_get_composite_job_info'' )'
    print @ServerName
    exec (@sql)
    --SELECT (@sql)
  FETCH NEXT FROM servers_cursor INTO @ServerName
END
CLOSE servers_cursor
DEALLOCATE servers_cursor
sql-server t-sql

2 个回答

  1. 游标经常受到谴责，而且通常是正确的。SQL 在处理一些所谓的数据“包”时往往效果最好；游标有助于逐行处理。如果您可以重写当前在 SQL 中使用逐行处理的内容，以便所有这些都由一次查询运行来处理，那么它几乎总是会提高性能。

    但是，在这种情况下，您的光标正在识别多个远程服务器，因此它可以向这些服务器中的每一个发送一个请求。没有一种方法（据我所知）可以同时向多个链接服务器发送请求。因此，通常反对游标的论点在这里不适用。

    可能有更快的方法来获得你想要的东西（例如，使用 SSIS 包从其他各种服务器中提取数据，你可以在其中并行地从多个服务器中提取数据），在这里使用游标没有错，并且在功能上任何方法都可能会使用相同的机制。

    • 5
  2. Best Answer

    对于这种类型的操作，您需要光标。有一些解决方案可以简化您的代码，但它们也在幕后使用游标。

    然而，如果您只是想简化您的存储过程，您可以这样做。我会创建一个存储过程，它接受一串动态 sql（预格式化），然后在这些机器上执行它。此过程不了解业务逻辑或表结构。

    然后我将创建第二个存储过程 (spLogBackup)，它唯一做的就是创建字符串，然后将其传递给另一个存储过程以执行。

    通过将代码分成两个过程，您可以使第一个过程可重复用于需要执行类似操作的其他查询。这将减少代码重复，并使将来创建类似的流程变得更加简单。

    • 1

相关问题