TSQL Generic Row number theoretical question

Question

To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next)

one of the procedures that I'm trying to write is to be able to take parameters such as an action (add, delete, modify), a source(which is a query), a target (which is a table) and keys (which will be used to join the source and target to do the action)... and this for any source to any table within our application... This being said, I have been able to create the procedure. Everything worked fine until we hit a big source and target (15 million rows).

one update statement was too big and an update statement for every row was too long... So I've managed to get the update to work in chunks of 500K rows in a test environment which performs way better but in my test lab I knew the source and the target so I was using a specific field from the source and Target to make sure I take the next 500K rows.

pseudo-code of my tests look like this.

DECLARE @BatchSize INT = 500000, @ROWCNT INT, @ROWTOTAL INT
WHILE 1 = 1
BEGIN
   UPDATE  TOP (@BatchSize) PRI
                SET {List of columns to update}
                FROM ({Source}) BAT 
                JOIN {Target} PRI ON {List of Keys} 
                WHERE PRI.F1 <> BAT.F1 -- F1 is updated by the list of columns to update
    IF @@ROWCOUNT < @BatchSize BREAK
END

The problem I'm having is that I want to use similar code but since I don't know what fields exist in the list, I can't use the WHERE PRI.F1 <> BAT.F1 which means that My loop would always pickup the same 500K rows (top 500K).

To counter that problem, I would need to use the ROW_NUMBER function but in my eyes it would double the execution time since I would have to create a temporary table that includes the row_number value and logic that will take the next 500k and so on.

Can anyone think of any other way (using physical addresses or something else) to be able to pick up the next 500K rows without having to create a temp table?

your're input is greatly appreciated. Thank you JG

Answer 1

Since the update is produced by dynamic SQL (as was revealed in the comment discussion – A. M.), it would be fairly easy to change (the dynamic part) so it knows which columns are updated and use them in the where clause.

Another route might be to use the OUTPUT clause so you get the list of primary key values of the updated rows in a temp table/variable. Then get the maximum of them and use it the next iteration of the loop with something like: WHERE PRI.pk_column > @previous_max. (All these assuming that all the target tables have a single-column integer primary/unique key.) This could prove more efficient as that column would be indexed (while an arbitrary F1 column might not be).

Answer 2

I beleive this will work on SQL Server 2005, but cannot test to confirm.

Instead of an endless loop that you break out of when you've got nothing left, you need to get how many you need to update and track until you've updated everything. I go backward to accommodate deletes from target... but the current example doesn't do that.

Note: the transaction will keep other things from updating your target out form under you, but if the source and the target are the same, your own update could cause a resequence and mess things up .

Here is an example against WorldWideImporters (re-runable, thanks to the rollback) to demonstrate. This update preserves order, so it isn't a problem.

SET XACT_ABORT ON

BEGIN TRANSACTION

SELECT * FROM [WideWorldImporters].[Purchasing].[PurchaseOrders] WHERE YEAR(ExpectedDeliveryDate) = 2013

DECLARE @BatchSize INT = 10, @ROWCNT INT, @ROWTOTAL INT
DECLARE @LastRecord INT = (SELECT COUNT(*) FROM (SELECT * FROM [Purchasing].[PurchaseOrders] WHERE YEAR(ExpectedDeliveryDate) = 2013) [{Target}]) 
DECLARE @StartRecord INT

WHILE @LastRecord > 0
BEGIN
    SET @StartRecord = @LastRecord - @BatchSize + 1

    UPDATE PRI
        SET ExpectedDeliveryDate = DATEADD(MONTH, 1, PRI.ExpectedDeliveryDate)
        FROM (
            SELECT 
                ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as [~Row],  * 
            FROM 
                (
                    SELECT * FROM [Purchasing].[PurchaseOrders] WHERE  YEAR(ExpectedDeliveryDate) = 2013
                ) [{Target}]
        ) BAT 
        JOIN [Purchasing].[PurchaseOrders]  PRI ON BAT.PurchaseOrderID =  PRI.PurchaseOrderID
        WHERE   [~Row] BETWEEN @StartRecord AND @LastRecord

    SET @LastRecord = @LastRecord - @BatchSize 

END


SELECT * FROM [WideWorldImporters].[Purchasing].[PurchaseOrders] WHERE YEAR(ExpectedDeliveryDate) = 2013

ROLLBACK