SQL Server cursor replacement (Update from another question)

Question

This is sort of a follow up question I asked here: SQL Server better way to iterate through millions of rows. I was told in that thread to submit a new question because what I really was looking for was different than the way I asked it.

I was not initially clear in my OP that this procedure will be run periodically. So this will not be run one time, it will be run weekly and the procedure needs to be able to check the counter table and the temp table.

I am working with SAP Timesheet data, so there are millions of rows. What I am trying to do is select the data from the SAP table and insert it into a table on MS SQL Server.

So I want to insert the original record(if it doesnt already exist), then if an update to the original record happens, which is in the form of a new SAP record with a refcounter, I want to find the original record in my table and update it, keeping the original counter value.

So I have done this successfully with a cursor (I know not the best), but with millions of records, I am wondering if there is a faster way, because I am on day 4 of my cursor running. Is there a better way then what I have below:

BEGIN
    CREATE TABLE CATSDB 
        (
            [COUNTER] nvarchar(12),
            REFCOUNTER nvarchar(12),
            PERNR nvarchar(8),
            WORKDATE nvarchar(8),
            CATSHOURS decimal(7, 3),
            APDAT nvarchar(8),
            LAETM nvarchar(6),
            CATS_STATUS nvarchar(2),
            APPR_STATUS nvarchar(2)
        )   

    INSERT INTO CATSDB
            (
                [COUNTER],REFCOUNTER,PERNR,WORKDATE,CATSHOURS,APDAT,LAETM,CATS_STATUS,APPR_STATUS
            )
        VALUES
            ('000421692670',NULL,'00000071','20190114','6.00','20190204','174541','30','30'),
            ('000421692671',NULL,'00000071','20190114','3.00','20190204','174541','30','30'),
            ('000421692672',NULL,'00000071','20190115','6.00','00000000','000000','60','20'),
            ('000421692673',NULL,'00000071','20190115','3.00','00000000','000000','60','20'),
            ('000421692712','000421692672','00000071','20190115','0.00','20190115','111007','30','30'),
            ('000421692713','000421692673','00000071','20190115','0.00','20190115','111007','30','30'),
            ('000429718015',NULL,'00000072','20190313','7.00','00000000','000000','60','20'),
            ('000429718016',NULL,'00000072','20190313','1.50','20190315','164659','30','30'),
            ('000429718017',NULL,'00000072','20190313','1.00','20190315','164659','30','30'),
            ('000430154143',NULL,'00000072','20190313','2.00','00000000','000000','60','20'),
            ('000430154142','000429718015','00000072','20190313','5.00','00000000','000000','60','20'),
            ('000430154928','000430154142','00000072','20190313','4.50','20190315','164659','30','30'),
            ('000430154929','000430154143','00000072','20190313','2.50','20190315','164659','30','30'),
            ('000429774620',NULL,'00000152','20190314','1.00','00000000','000000','60','20'),
            ('000429774619',NULL,'00000152','20190314','1.00','00000000','000000','60','20'),
            ('000429802106','000429774620','00000152','20190314','2.00','00000000','000000','60','20'),
            ('000429802105','000429774619','00000152','20190314','3.00','00000000','000000','60','20'),
            ('000429840242','000429802106','00000152','20190314','4.00','20190315','143857','30','30'),
            ('000429840241','000429802105','00000152','20190314','5.00','20190315','143857','30','30')

    CREATE TABLE [TBL_COUNTER]
        (
            [COUNTER] [varchar](12) NOT NULL,
            [REFCOUNTER] [varchar](12) NULL
        )   

    CREATE TABLE TEMP
        (
            [COUNTER] [nvarchar](12) NOT NULL,
            [REFCOUNTER] [nvarchar](12) NULL,
            [PERNR] [nvarchar](8) NULL,
            [WORKDATE] [nvarchar](8) NULL,
            [CATSHOURS] [decimal](7, 3) NULL,
            [APDAT] [nvarchar](8) NULL,
            [LAETM] [nvarchar](6) NULL,
            [CATS_STATUS] [nvarchar](2) NULL,
            [APPR_STATUS] [nvarchar](2) NULL
        )       
END

BEGIN
    DECLARE     @COUNTER nvarchar(12),  
                @REFCOUNTER nvarchar(12),   
                @PERNR nvarchar(8), 
                @WORKDATE nvarchar(8),  
                @CATSHOURS decimal(7, 3),
                @APDAT nvarchar(8),
                @LAETM nvarchar(6),
                @CATS_STATUS nvarchar(2),
                @APPR_STATUS nvarchar(2)

    DECLARE @orig_counter nvarchar(12)
END

--The below cursor will include a where statement where it selects data
--where the workdate or apdat fall within a data range.  Since I am
--running this weekly, the to and from dates will be the last work week.
BEGIN
    DECLARE curs CURSOR FOR
        SELECT 
                [COUNTER],
                REFCOUNTER,
                PERNR,
                WORKDATE,
                CATSHOURS,
                APDAT,
                LAETM,
                CATS_STATUS,
                APPR_STATUS
        FROM 
                CATSDB
END

BEGIN
    OPEN curs
END

BEGIN
    FETCH NEXT FROM curs INTO
        @COUNTER,
        @REFCOUNTER,
        @PERNR,
        @WORKDATE,
        @CATSHOURS,
        @APDAT,
        @LAETM,
        @CATS_STATUS,
        @APPR_STATUS
END

BEGIN
    WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN
                IF NOT EXISTS (SELECT * FROM TBL_COUNTER WHERE [COUNTER] = @COUNTER)
                    BEGIN
                        INSERT INTO TBL_COUNTER
                                ([COUNTER]
                                ,REFCOUNTER)
                            VALUES
                                (@COUNTER
                                ,@REFCOUNTER)
                    END
            END
            BEGIN
                IF NOT EXISTS (SELECT * FROM TEMP WHERE [COUNTER] = @COUNTER)
                    BEGIN
                            --If REFCOUNTER is populated, get the original COUNTER value, then update that row with the new values. Otherwise insert new record
                            IF @REFCOUNTER <> '' AND @REFCOUNTER IS NOT NULL
                                BEGIN
                                    BEGIN
                                        WITH n([COUNTER], REFCOUNTER) AS 
                                            (
                                                SELECT 
                                                        cnt.[COUNTER], 
                                                        cnt.REFCOUNTER 
                                                FROM 
                                                        TBL_COUNTER cnt
                                                WHERE 
                                                        cnt.[COUNTER] = @REFCOUNTER
                                            UNION ALL
                                                SELECT 
                                                        nplus1.[COUNTER], 
                                                        nplus1.REFCOUNTER 
                                                FROM 
                                                        TBL_COUNTER as nplus1, 
                                                        n
                                                WHERE 
                                                        n.[COUNTER] = nplus1.REFCOUNTER
                                            )
                                        SELECT @orig_counter = [COUNTER] FROM n WHERE REFCOUNTER = '' OR REFCOUNTER IS NULL
                                    END
                                    BEGIN
                                        UPDATE TEMP
                                           SET 
                                               [REFCOUNTER] = @REFCOUNTER
                                              ,[PERNR] = @PERNR 
                                              ,[WORKDATE] = @WORKDATE                                               
                                              ,[CATSHOURS] = @CATSHOURS                                                                                    
                                              ,[APDAT] = @APDAT                                        
                                              ,[LAETM] = @LAETM
                                              ,[CATS_STATUS] = @CATS_STATUS
                                              ,[APPR_STATUS] = @APPR_STATUS                                        
                                            WHERE [COUNTER] = @orig_counter
                                    END
                                END
                            ELSE
                                BEGIN
                                    INSERT INTO TEMP
                                               ([COUNTER]
                                               ,[REFCOUNTER]                                               
                                               ,[PERNR]                                               
                                               ,[WORKDATE]                                               
                                               ,[CATSHOURS]                                             
                                               ,[APDAT]                                              
                                               ,[LAETM]
                                               ,[CATS_STATUS]                                               
                                               ,[APPR_STATUS])                                              
                                         VALUES
                                               (@COUNTER
                                               ,@REFCOUNTER                                              
                                               ,@PERNR                                               
                                               ,@WORKDATE                                             
                                               ,@CATSHOURS                                               
                                               ,@APDAT                                               
                                               ,@LAETM                                               
                                               ,@CATS_STATUS                                               
                                               ,@APPR_STATUS)                                               
                                END
                    END

            FETCH NEXT FROM curs INTO
                @COUNTER,
                @REFCOUNTER,
                @PERNR,
                @WORKDATE,
                @CATSHOURS,
                @APDAT,
                @LAETM,
                @CATS_STATUS,
                @APPR_STATUS
        END
    END
END

BEGIN
    CLOSE curs
    DEALLOCATE curs
END

I shortened it and created the tables for you all to be able to see what is going on. The expected result is

+--------------+--------------+----------+----------+-----------+----------+--------+-------------+-------------+
|   COUNTER    |  REFCOUNTER  |  PERNR   | WORKDATE | CATSHOURS |  APDAT   | LAETM  | CATS_STATUS | APPR_STATUS |
+--------------+--------------+----------+----------+-----------+----------+--------+-------------+-------------+
| 000421692670 | NULL         | 00000071 | 20190114 |      6.00 | 20190204 | 174541 |          30 |          30 |
| 000421692671 | NULL         | 00000071 | 20190114 |      3.00 | 20190204 | 174541 |          30 |          30 |
| 000421692672 | 000421692672 | 00000071 | 20190115 |      0.00 | 20190115 | 111007 |          30 |          30 |
| 000421692673 | 000421692673 | 00000071 | 20190115 |      0.00 | 20190115 | 111007 |          30 |          30 |
| 000429718015 | 000430154142 | 00000072 | 20190313 |      4.50 | 20190315 | 164659 |          30 |          30 |
| 000429718016 | NULL         | 00000072 | 20190313 |      1.50 | 20190315 | 164659 |          30 |          30 |
| 000429718017 | NULL         | 00000072 | 20190313 |       1.0 | 20190315 | 164659 |          30 |          30 |
| 000430154143 | 000430154143 | 00000072 | 20190313 |      2.50 | 20190315 | 164659 |          30 |          30 |
| 000429774620 | 000429774620 | 00000152 | 20190314 |      2.00 | 00000000 | 000000 |          60 |          20 |
| 000429774619 | 000429802105 | 00000152 | 20190314 |      5.00 | 20190315 | 143857 |          30 |          30 |
+--------------+--------------+----------+----------+-----------+----------+--------+-------------+-------------+

I will pull the data from the origin source for new records and changed records from the last time i ran it. So I will not have the full chain every week. There needs to be a way to get back to the original counter value, without the full dataset, which is why i had the counter table. I apologize for not being more clear.

I hope what I am trying to do is clear. As i stated, i have it working with a cursor, but it is so slow. Processing a week work of data is taking at least 24 hours.

Have you tried looking at recursive ctes? This seems like what you need here. I am having a hard time deciphering the logic here though. My head is swimming with all the temp swapping of stuff. Can you explain the logic you are trying to accomplish? — Sean Lange, Nov 07 '19 at 14:45
@SeanLange I know it is a bit confusing. So on a weekly basis, I am selecting data from a HANA database and merging that into the CATSDB table. I then want to take that new data in the CATSDB table and update another table (temp was a poor choice in the example) Just assuming the temp table is the final table for the data. Does that help clarify it. — dk96m, Nov 07 '19 at 17:42

Neville Kuyt · Answer 1 · 2019-11-08T16:37:41.653

Your code is fairly long and complicated, and I'm not sure I fully understand what it does. However, I can show you the way to think about this in a more "relational database" way.

Your cursor iterates through millions of records, and for each record, executes a fairly simple if statement, and where that yields true, it does something.

In a database, you can replace that if for a single row in the cursor with a where against the entire database. As long as your where clause hits an index, it should be very fast.

For example, your first step is make sure that the current @counter is present in the table COUNTER:

IF NOT EXISTS (SELECT * FROM TBL_COUNTER WHERE [COUNTER] = @COUNTER)
BEGIN
   INSERT INTO TBL_COUNTER
   ([COUNTER]
   ,REFCOUNTER)
   VALUES
   (@COUNTER
   ,@REFCOUNTER)
END

This code executes for every row in your cursor (millions of times). Instead, outside the cursor, just once, you can run the following query:

INSERT INTO TBL_COUNTER
   ([COUNTER]
   ,REFCOUNTER)
SELECT [COUNTER]
       REFCOUNTER
FROM CATSDB
WHERE COUNTER NOT IN 
  (SELECT COUNTER FROM TBL_COUNTER)

This replaces inspecting every single row, one by one, with a where clause against the entire dataset.

You can take a similar approach with the next statement - the same principle applies.

IF NOT EXISTS (SELECT * FROM TEMP WHERE [COUNTER] = @COUNTER) BEGIN --If REFCOUNTER is populated, get the original COUNTER value, then update that row with the new values. Otherwise insert new record

   IF @REFCOUNTER <> '' AND @REFCOUNTER IS NOT NULL
   ....
   ELSE
   BEGIN
    INSERT INTO TEMP
      ([COUNTER]
       ,[REFCOUNTER]                                               
       ,[PERNR]                                               
       ,[WORKDATE]                                               
       ,[CATSHOURS]                                             
       ,[APDAT]                                              
       ,[LAETM]
       ,[CATS_STATUS]                                               
       ,[APPR_STATUS])                                              
       VALUES
         (@COUNTER
         ,@REFCOUNTER                                              
         ,@PERNR                                               
         ,@WORKDATE                                             
         ,@CATSHOURS                                               
         ,@APDAT                                               
         ,@LAETM                                               
         ,@CATS_STATUS                                               
         ,@APPR_STATUS)                                               
    END

You can re-write this as:

INSERT INTO TEMP
          ([COUNTER]
           ,[REFCOUNTER]                                               
           ,[PERNR]                                               
           ,[WORKDATE]                                               
           ,[CATSHOURS]                                             
           ,[APDAT]                                              
           ,[LAETM]
           ,[CATS_STATUS]                                               
           ,[APPR_STATUS])   
    SELECT 
 [COUNTER],
 REFCOUNTER,
 PERNR,
 WORKDATE,
 CATSHOURS,
 APDAT,
 LAETM,
 CATS_STATUS,
 APPR_STATUS
 FROM 
 CATSDB
  --- We want the opposite of IF @REFCOUNTER <> '' AND @REFCOUNTER IS NOT NULL
  WHERE @REFCOUNTER = '' OR @REFCOUNTER IS NULL

It may well be that this gives you enough of a speed boost (you have to adjust your cursor to exclude those cases, of course).

Finally, the clause with the CTE. It's late, I may be missing, something, but the comment says:

 --If REFCOUNTER is populated, get the original COUNTER value, then update that row with the new values. Otherwise insert new record

I think you can achieve that with:

 UPDATE TEMP
 SET 
 [REFCOUNTER] = c.REFCOUNTER
 ,[PERNR] = c.PERNR 
 ,[WORKDATE] = c.WORKDATE                                               
 ,[CATSHOURS] = c.CATSHOURS                                                                                    
 ,[APDAT] = c.APDAT                                        
 ,[LAETM] = c.LAETM
 ,[CATS_STATUS] = c.CATS_STATUS
 ,[APPR_STATUS] = c.APPR_STATUS                                        
 from TEMP t, 
 CATSDB c
 WHERE t.[COUNTER] = C.REFCOUNTER

I think I'm missing what the second part of your UNION in the CTE is achieving.

I see where you are going with this. Let me play around a little with it. Thank you — dk96m, Nov 07 '19 at 21:39
Ok, so that works for that part of it, but for the second part, it wont work, because of the walk back to the original counter value. — dk96m, Nov 07 '19 at 21:47
Could you perhaps insert comments into your code to show where the "walk back" is? — Neville Kuyt, Nov 08 '19 at 13:04
It is just under the part you with the inserting the counter value that you said I should change. it is a CTE that begins with "WITH n([COUNTER], REFCOUNTER) AS" — dk96m, Nov 08 '19 at 13:33
So then I was looking at instead of the cursor for the second part, use the CTE "Walk Back" with a merge statement. The only problem is I cant figure out how to change "cnt.COUNTER = @REFCOUNTER" in the CTE since I don't have @REFCOUNTER from the cursor anymore. — dk96m, Nov 08 '19 at 13:36
It's another "if" statement, right? So can you split that into two "insert....where" clauses (one where REFCOUNTER is populated, one where it isn't)? The second case is the "else" part of that bit of code, correct? — Neville Kuyt, Nov 08 '19 at 14:28
And the "union" in your CTE can also be redefined as two "where" statements. I don't have access to a SQL server, but will try and update the question with pseudo code. — Neville Kuyt, Nov 08 '19 at 15:00
Yes, it is another if statement, but the CTE is the issue I cant get around. — dk96m, Nov 08 '19 at 15:39
So the cte is the walk back to the original counter value. say the original record is counter 1, an update record comes in as counter 2, which refcounter 1. A third record comes in with counter 3, the refcounter is 2. So there is no reference to counter 1 in counter 3. So the CTE does the walk back when processing counter 3 to get the original counter 1. I think my only other option is to take the current set of data I am importing, deleting the records in my final table where the current set refcounter equals the final table counter, then inserting all the records. — dk96m, Nov 11 '19 at 12:52

SQL Server cursor replacement (Update from another question)

1 Answers1