Complex Update on 3 Million or so Records

[Bonediggler1]

Hi -

To simplify: I have a table with about 10 fields but the update in question only involves 2, Invoice (INV) and Sequence Number (SEQ_NUM).

Basically I pull the data from Oracle and put it in SQL Server. In oracle there will be several transactions per invoice, which are grouped into sequences (00, 01, 02 etc). However, SEQ_NUM is only populated for the first invoice in a sequence, so the data would look like:

INV SEQ_NUM
12 00
12
12 01
12
12
12 02


I pull this into SQL Server and then use the following code to update the missing SEQ_NUMs:

DECLARE @V VARCHAR(20)
DECLARE @X VARCHAR(20)
DECLARE @Y INT
DECLARE @Z INT
DECLARE @COUNTER INT
DECLARE @COUNTER1 INT

SET @COUNTER = 1
SET @COUNTER1 = (SELECT COUNT(*) FROM TABLE)
SET @Y = NULL
SET @V = NULL

WHILE @COUNTER <= @COUNTER1
BEGIN

SET @Z = (SELECT SEQ_NUM FROM TABLE WHERE ROWID = @COUNTER)
SET @X = (SELECT INV FROM TABLE WHERE ROWID = @COUNTER)

IF @X = @V
IF @Z IS NULL
UPDATE TABLE SET SEQ_NUM = @Y WHERE ROWID = @COUNTER

SET @Y = (SELECT SEQ_NUM FROM TABLE WHERE ROWID = @COUNTER)
SET @V = (SELECT INV FROM TABLE WHERE ROWID = @COUNTER)

SET @COUNTER = @COUNTER + 1

END


The table is sorted so to look like the sample data above, and ROWID assigns a row number to each record, starting at 1.


This works, but takes a really long time - i am updating more than 3 million records.

Any ideas on how to improve performance?

Thank you!!

 

[ZenRaven]

Are there any other fields that identify the order these records are in? Maybe an identity field? How do you know any given INV 12 is actually "under" SEQ_NUM 00 and not 02?


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[markros]

If there is an identity field that provides the order, then this problem is simple enough and you can check Last problem in this blog
for solution.

PluralSight Learning Library

 

[ZenRaven]

I noticed you had a rowid. I tried something like this but it doesn't handle the last seq_num. You'd also have to do some conversion on seq_num as it looks like it isn't int. I haven't looked at it but the link markros provided is probably more elegant and efficient.

DECLARE @table TABLE
    (
      ROWID INT IDENTITY,
      INV INT,
      SEQ_NUM INT
    )

INSERT  INTO @table ( INV, SEQ_NUM )
VALUES  ( 12, 0 ),
        ( 12, NULL ),
        ( 12, 1 ),
        ( 12, NULL ),
        ( 12, NULL ),
        ( 12, 2 ),
        ( 12, NULL )
  
  
UPDATE  @table
SET     SEQ_NUM = d.SEQ_NUM
FROM    @table c,
        ( SELECT    a.ROWID AS minrow,
                    b.ROWID - 1 AS maxrow,
                    a.SEQ_NUM
          FROM      @table a
                    LEFT JOIN @table b ON a.INV = b.INV
                                          AND a.SEQ_NUM = b.SEQ_NUM - 1
          WHERE     a.SEQ_NUM IS NOT NULL
        ) d
WHERE   c.rowid BETWEEN d.minrow AND d.maxrow

SELECT  *
FROM    @table

--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson

 

[Bonediggler1]

Thanks all for the input-

Markos, i tried to adapt the code you suggested using, but the values are still not getting updated...is this because I am dealing with nulls?

My code is as follows:

;WITH CTE AS

(SELECT A.ROWID, A.SEQ_NUM, B.ROWID AS MATCHID, B.SEQ_NUM AS NEWSEQNUM
FROM @TABLE A

CROSS APPLY (SELECT TOP(1)*
FROM @TABLE c
WHERE c.ROWID < A.ROWID AND c.SEQ_NUM > NULL
ORDER BY ROWID DESC) b

WHERE A.SEQ_NUM IS NULL)

UPDATE CTE SET SEQ_NUM = NEWSEQNUM

SELECT *
FROM @TABLE

 

[Bonediggler1]

Strange-- I initially put all this data in SQL Server looking for better performance...but bringing it into Access and updating there is much faster - only a couple minutes vs maybe 10+ hours.

???

 

[markros]

Your comparison with NULL is wrong in the code above. It should be c.Seq_NUM IS NOT NULL

PluralSight Learning Library