优化存储过程

Question

如何在不使用光标的情况下改进这一点？我以为使用表变量会有所帮助，但我这样做的方向是正确的吗？我省略了游标并尝试使用表变量执行此操作。请帮忙，这是代码。

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[BatchProcessBridge_CustomerEvent]') AND [xtype] IN (N'P'))
BEGIN   
    DROP PROCEDURE [dbo].[BatchProcessBridge_CustomerEvent]
END
GO
CREATE PROCEDURE [dbo].[BatchProcessBridge_CustomerEvent]
(
    @BatchJobTable Bridge_CustomerEventBatchJobTable READONLY,
    @Name VARCHAR(50)
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Lsn BINARY(10), 
    DECLARE @SeqVal BINARY(10), 
    DECLARE @Action VARCHAR(300), 
    DECLARE @CustId VARCHAR(MAX)
    --using tabel variable. Cursor gives bad performance. 
    DECLARE  @TEMP_TABLE TABLE ( [Lsn] BINARY(10), [SeqVal] BINARY(10), [Action] VARCHAR(300), [CustId]  VARCHAR(MAX))
    INSERT INTO @TEMP_TABLE
    SELECT Lsn, SeqVal, [Action], [CustId] FROM @BatchJobTable

    --DECLARE GetBatchJobCursor CURSOR FAST_FORWARD
    --FOR
    --SELECT Lsn, SeqVal, [Action], [CustId] FROM @BatchJobTable
    --OPEN GetBatchJobCursor
    --FETCH NEXT FROM GetBatchJobCursor INTO @Lsn, @SeqVal, @Action, @CustId
    --WHILE @@FETCH_STATUS = 0
    BEGIN
    BEGIN TRY
    BEGIN TRANSACTION
        IF (@Action = 'create')
        BEGIN
            -- Create.
            INSERT INTO [Bridge_Customer]
            (
            [CustId]
            ,[PersonId]
            ,[DisplayName]
            ,[CreatedDate]
            ,[ModifiedDate]
            )
        SELECT 
            [CustId]
            ,[PersonId]
            ,[DisplayName]
            ,[CreatedDate]
            ,[ModifiedDate]
        FROM
            @BatchJobTable
        WHERE
            (Lsn = @Lsn) AND (SeqVal = @SeqVal)
        END
        ELSE IF (@Action = 'update')
        BEGIN
            -- Update.
            UPDATE [Target]
            SET
                [Target].[CustId] = [Ref].[CustId]
                ,[Target].[PersonId] = [Ref].[PersonId]
                ,[Target].[DisplayName] = [Ref].[DisplayName]
                ,[Target].[CreatedDate] = [Ref].[CreatedDate]
                ,[Target].[ModifiedDate] = [Ref].[ModifiedDate]
            FROM
                [dbo].[Bridge_Customer] AS [Target]
            INNER JOIN 
                (SELECT * FROM @BatchJobTable WHERE (Lsn = @Lsn) AND (SeqVal = @SeqVal)) AS [Ref]
            ON 
                ([Target].[CustId] = [Ref].[CustId])
        END
        ELSE IF (@Action = 'delete')
        BEGIN
            DELETE FROM [dbo].[Bridge_Customer] WHERE [CustId] = @CustId 
        END
        -- Update last processed event.
        EXEC [dbo].[UpdateLastProcessedEvent]
        @Name = @Name,
        @Lsn = @Lsn,
        @SeqVal = @SeqVal
    COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);  
        DECLARE @ErrorSeverity INT;  
        DECLARE @ErrorState INT;  
        SELECT   
            @ErrorMessage = ERROR_MESSAGE(),  
            @ErrorSeverity = ERROR_SEVERITY(),  
            @ErrorState = ERROR_STATE();  
        -- Use RAISERROR inside the CATCH block to return error  
        -- information about the original error that caused  
        -- execution to jump to the CATCH block.  
        RAISERROR (@ErrorMessage, -- Message text.  
                   @ErrorSeverity, -- Severity.  
                   @ErrorState -- State.  
                   );  
        ROLLBACK TRANSACTION
    END CATCH
    --FETCH NEXT FROM GetBatchJobCursor INTO @Lsn, @SeqVal, @Action, @CustId
    END
    --CLOSE GetBatchJobCursor
    --DEALLOCATE GetBatchJobCursor
END
GO

Answer 1

这里不需要游标;这只是基本的SQL。请原谅我的代码中的任何错误，因为您没有提供任何 DLL，但我很确定您可以这样做：

IF (@Action = 'create')
  INSERT INTO Bridge_Customer
  (
    CustId
   ,PersonId
   ,DisplayName
   ,CreatedDate
   ,ModifiedDate
  )
  SELECT 
      CustId
     ,PersonId
     ,DisplayName
     ,CreatedDate
     ,ModifiedDate
  FROM @BatchJobTable
ELSE IF (@Action = 'update')
  UPDATE tgt
  SET  tgt.CustId       = Ref.CustId
      ,tgt.PersonId     = Ref.PersonId
      ,tgt.DisplayName  = Ref.DisplayName
      ,tgt.CreatedDate  = Ref.CreatedDate
      ,tgt.ModifiedDate = Ref.ModifiedDate
  FROM dbo.Bridge_Customer AS tgt
  INNER JOIN @BatchJobTable AS ref 
  ON (tgt.CustId = Ref.CustId)
ELSE IF (@Action = 'delete')
  DELETE FROM dbo.Bridge_Customer 
  WHERE CustId IN (SELECT CustId FROM @BatchJobTable);

就个人而言，我会将其分成三个存储，并从应用程序层调用任何一个。您正在执行的操作称为"捕获所有查询"，这很好，但是，如果您必须走这条路，请阅读以下内容：捕获所有查询(Gail Shaw(