December 12, 2007 at 2:51 pm
I just did something like that today - using a staging table - the 1M or so records took 13 seconds to finish. You'll be lucky if you can get a cursor solution to finish in 10 minutes (more like an hour).
Hmmm...
I guess you're not quite experienced with cursors.
😛
We've got several upload tasks which initially were implemented by VB programmers and, of course, used cursors.
One of them loads 300k-500k rows file into 5 tables joined by FKs. And yes, it must perform all those checks, validations, etc.
After I replaced the cursor with set based statements execution time was decreased to 15-22 seconds (depending on server load) from... 4-4.5 hours!
_____________
Code for TallyGenerator
December 12, 2007 at 3:10 pm
Sergiy (12/12/2007)
I just did something like that today - using a staging table - the 1M or so records took 13 seconds to finish. You'll be lucky if you can get a cursor solution to finish in 10 minutes (more like an hour).
Hmmm...
I guess you're not quite experienced with cursors.
😛
We've got several upload tasks which initially were implemented by VB programmers and, of course, used cursors.
One of them loads 300k-500k rows file into 5 tables joined by FKs. And yes, it must perform all those checks, validations, etc.
After I replaced the cursor with set based statements execution time was decreased to 15-22 seconds (depending on server load) from... 4-4.5 hours!
I guess I got lucky today - this one didn't required a whole lot of cleanup so the cursor almost ran in an acceptable amount of time (for a human, not for the server). Something like 30 minutes, with few lookups. (yes I just did it for giggles on my dev machine). I figured someone hell-bound to use a cursor might wring some more time out of it, so I guessed lower...:)
I gave up on that method a long time ago after much smaller files took much longer...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply