Unique datetime for each row
-
Hello,
I've been checking some SPs and triggers in our database and found rather terrible things - row by row processing at its worst (commercial software, so we can't do anything with it ourselves).
I think most of these things could be brought to set-based processing, but I'm at a loss what to do with the fact, that the program requires in a certain operation unique datetime to be written into each row. While processing row by row, it is done this way in the procedure:
while exists (select * from sometable
where product = @product and warehouse = @warehouse and datum = @datum)
begin
waitfor delay '00:00:00.002'
select @datum = getdate()
end
(and then later UPDATE sometable SET datum = @datum, .....)
I suppose this is needed because we are using FIFO method. Is there a way to achieve something like that when doing set-based operation? I'm afraid there isn't... and so we'll be stuck with this monstrosity we have there now.
-
The only way I can think of is to use temp table/table variable to store the unique key of the selected records plus IDENTITY column and update datetime using the IDENTITY column value as a multiplier to add milliseconds to GETDATE()
If there is no unique key then
Far away is close at hand in the images of elsewhere.
Anon. -
There is the triangular join way but the temp table will always be faster for larget data sets. So use David's solution.
-
Thanks for the replies, there is unique key for each row so I will consider that. I will not be writing the procedure myself, I just wanted to have some arguments before I have a talk with the authors.
-
You might also want to remember that the minimum resolution for the DateTime data type is 3 milliseconds, not 2 as posted in the original code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
But for uniqueness it is 4.
ie add 3 or 4 milliseconds to 13 milliseconds and you will get 17 milliseconds for both
Far away is close at hand in the images of elsewhere.
Anon. -
I stand corrected. Thanks David.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Heck, I only found out when I experimented before my first post
There is always something in SQL to catch me out
Far away is close at hand in the images of elsewhere.
Anon. -
I think you can include everyone else on the planet on that one David
.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply