Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Complex Update on 3 Million or so Records

Status
Not open for further replies.

Bonediggler1

Technical User
Jul 2, 2008
156
US
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!!
 
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
 
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.

Code:
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
 
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
 
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.

???
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top