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
F1with one of the updated columns from the target table? I mean, how can you not know the list of columns that are updated? How is that list produced? Is it with dynamic SQL? – ypercubeᵀᴹ Feb 8 '16 at 19:15updateis produced by dynamic SQL, it would be fairly easy to change (the dynamic part) so it knows which columns are updated and use them in thewhereclause. – ypercubeᵀᴹ Feb 8 '16 at 19:46OUTPUTclause 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 arbitraryF1column might not be). – ypercubeᵀᴹ Feb 8 '16 at 19:53