More on SQL
time to read 2 min | 211 words

Just thought to let you know, I just changed a cursor-based query to use a set base approach, and the performance just shot thought the roof. From over 5 minutes for the cursor approach (which examined the data and then executed several statements for each row) to set base approach (which execute several statements for the whole set) and run at roughly half a minute.

I will be the first to admit that I'm not a SQL guru, not even close to a Zen student level, actually. But I think that I can begin to like it, the possibilities are great, and I like to be able to say what, and not how. It gives the engine much more space to work with, and, I assume, Microsoft spent more time optimizing their engine than I can spend on optimizing my query J

Now all I need to do is wait for the whole process to run, and see if the results are any good, I've a pretty good feeling about it. Just to give you a hint about the difference in performance, the cursor based query (which run on test data, much smaller than the real one, of course) would issue ~80,001 queries. The set base approach issues 2.

Tweet Share Share 0 comments