2

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

  • 1
    Can't you replace F1 with 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:15
  • @ypercubeᵀᴹ it's all in the parameters. This is a generic Procedure that uses Dynamic SQL. I don't know the columns that are being updated. I need to rely on the physical addresses or row numbers. – JohnG Feb 8 '16 at 19:44
  • Since the update is 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 the where clause. – ypercubeᵀᴹ Feb 8 '16 at 19:46
  • 1
    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). – ypercubeᵀᴹ Feb 8 '16 at 19:53
  • @ypercubeᵀᴹ sounds right, but which column do I take? What if a column that I chose doesn't change for all rows... I would be taking a chance that the column I'm choosing is reliable. It's a risk that I can't take. As for the the primary keys, there can be combined ones. I do like your suggestion of Output (didn't think of that one), I will try that. – JohnG Feb 8 '16 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.