Ah.... forgot to mention that if you absolutely must write RBAR (not likely if you learn to write nice simple set based code), you should also use SET NOCOUNT ON to improve the performance by not having to generate (250,000 in this case) "(1 row(s) affected)" messages. In the 2nd example given, SET NOCOUNT ON improved the performance from almost 18 seconds to just over 13 seconds on my box.
Of course, that's still a RBAR solution. Generating the same random numbers using one of the multiple methods to gen set based random numbers dropped the duration to less than 1.2 seconds to do the same thing. It's also easier on the I/O system...
The RBAR method generated more than 254,000 reads and an internal rowcount of 500,001 rows where the set based method generated less than 1000 reads and an internal rowcount of only 250,000 which is just what the good doctor ordered. (There is a slightly more complex method that will generate, get this, almost 0 reads... you've just got to look for it).
Heh... why don't I post that solution? I don't want to deprive you of having the fun of finding it and testing it on your own. ;-)
is pronounced ree-bar and is a Modenism for R
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs