CODEWITHSUNDEEP
sql-servercursor
Sreedhar
Sreedhar

Reputation: 30025

Avoiding Cursor in SQL

What is the best alternative to using a cursor in SQL if I am suffering from performance issues ?

I got the following code wherein it uses Cursor to loop through and insert records.

      DECLARE @AuditBatchID_logRow INT,
    @AuditOperationID_logRow INT,
    @RowIdentifier_logRow nvarchar(200),
    @AuditDBTableID_logRow INT, 
    @AuditLogRowID INT,

    @AuditDBColumnID INT, 
    @NewValue nvarchar(200),
    @PreviousVaue nvarchar(200), 
    @NewDisplayValue nvarchar(200)

  DECLARE Crsr_AUDITLOGROW CURSOR LOCAL FORWARD_ONLY STATIC 
      FOR 
         SELECT [t0].[AuditBatchID], 
                [t1].[AuditOperationID], 
                [t1].[RowIdentifier],
                [t0].[AuditTableID],
                [t1].[AuditLogRowID]
         FROM [AuditBatchTable] AS [t0]
         INNER JOIN [AuditLogRow] AS [t1] 
               ON [t0].[AuditBatchTableID] = [t1].[AuditBatchTableID]

  Open Crsr_AUDITLOGROW

  FETCH NEXT FROM Crsr_AUDITLOGROW 
     INTO @AuditBatchID_logRow, 
          @AuditOperationID_logRow,  
          @RowIdentifier_logRow, 
          @AuditDBTableID_logRow,
          @AuditLogRowID

  While(@@FETCH_STATUS = 0)
  BEGIN
      INSERT INTO AuditLog(AuditLogRowID, AuditColumnID, 
                           NewValue, OldDisplayValue, NewDisplayValue)
        (SELECT @AuditLogRowID,
                [ac].[AuditColumnID], 
                [t0].[UserEnteredValue], 
                [t0].[PreviousDisplayValue],
                [t0].[DisplayValue]
          FROM FMG_PROD.dbo.AuditLog AS [t0]
          INNER JOIN FMG_PROD.dbo.AuditDBColumn AS [t1] 
             ON [t0].[AuditDBColumnID] = [t1].[AuditDBColumnID]
          INNER JOIN FMG_PROD.dbo.AuditDBTable AS [t2] 
             ON [t1].[AuditDBTableID] = [t2].[AuditDBTableID]
          INNER JOIN AuditTable AS [AT] 
             ON [t2].AuditDBTable = [AT].AuditTable
          INNER JOIN AuditColumn AS [AC] 
             ON [AT].AuditTableID = [AC].AuditTableID 
          WHERE     
             ([t0].[AuditBatchID] = @AuditBatchID_logRow)  
             AND ([t0].[AuditOperationID] = @AuditOperationID_logRow)
             AND ([AT].[AuditTableID] = @AuditDBTableID_logRow) 
             AND [AC].AuditColumn = [t1].AuditDBColumn 
             AND (@RowIdentifier_logRow = 
                CASE ISNUMERIC(@RowIdentifier_logRow)
                  WHEN 1 then 
                      CAST ([t0].[RowID] AS VARCHAR(200))
                  ELSE 
                      CAST([t0].[RowGUID] AS VARCHAR(200))
     END))

         FETCH NEXT FROM Crsr_AUDITLOGROW 
           INTO @AuditBatchID_logRow, 
                @AuditOperationID_logRow, 
                @RowIdentifier_logRow, 
                @AuditDBTableID_logRow,
                @AuditLogRowID
END

CLOSE Crsr_AUDITLOGROW
DEALLOCATE Crsr_AUDITLOGROW

Upvotes: 2

Views: 987

Answers (1)

marc_s
marc_s

Reputation: 754398

Well, you're thinking and coding like a structured programmer - linearly, one by one, in tighest control of the program flow. That's how we (almost) all have been thought to program.

You need to think like a SQL guy - in SETS of data (not single rows, one at a time).

Avoid the need to tightly control each step of the algorithm - instead, just tell SQL Server WHAT you want - not HOW to do each step!

In the end, you're inserting a bunch of rows into the AuditLog table. Why do you need a cursor for that??

 INSERT INTO AuditLog(...list of columns.....)
    SELECT (....list of columns....)
    FROM Table1
    INNER JOIN ..........
    INNER JOIN .........
    WHERE ........

and you're done! Define what you want inserted into the table - DO NOT tell SQL Server in excrutiating detail how to do it - it'll know very well, thank you!

Marc

Upvotes: 8

Related Questions