October 1, 2008 at 7:12 am
Jeff Moden (9/30/2008)
Heh... I find it very difficult to believe an article on performance where the author says "At first, we prepare a test table with 1 million records:" and then builds the damned thing with a bloody loop. 😉
It does tend to hamper credibility.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 1, 2008 at 7:22 am
I looked at the code on the other site, and there isn't enough there to do a real work-over on it, but there are a few things that stand out.
First, it looks like the whole thing could be rewritten to avoid all those IF statements, by turning it into a series of Union statements. It looks like it could be replaced with a single command, which means it could avoid table variables altogether. That almost always improves performance significantly.
Second, I'd want to take a look at the view that's referenced repeatedly in several of the selects. It has Distinct on all the selects from it, which means it's probably being used inappropriately for this particular function. Most likely, it contains tables and columns that aren't needed for this use.
To go beyond those two points, I'd need to see execution plans and index use and all that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply