We had a situation at work where we had a product search routine which we wanted to optimize a bit. So spending some time looking at execution plans, and we started optimizing the various indexes for what we could – changing scans to seeks and key look ups for what we could and so on.
The jist of the routine was a number of specific searches union’ed into one result. I’m not going to post the SQL because it doesn’t matter much for the given situation as it was more the experience I wanted to convey.
So after optimizing all the indexes we could we were still unhappy with the performance, but didn’t really have the timeframe available to change the architecture of the query or database, we started trying more untraditional aspects. First, because the results of the numbers of unions were varying, but all ending up with the same output (of course), we tried to only select the ID’s out and wrap them in a Common Table Expression, and use the ID’s from that to extract the actual product result.
That however did not help much, so we tried a number of other techniques, to identify the bottlenecks.
The strange thing was what actually worked. Instead of using a common table expression to wrap the multiple unions, we made a table variable and selected the ID’s into this variable, and used that to pull products and product information. This worked much better for some reason and I find the result rather counter-intuitive based on what I have tested and read and know about databases so far…… I guess I’ll have to try and reproduce the situation in an upcoming blog piece to see if I can identify why using a table variable improved performance so significantly as it did in this situation.
The lesson I learned from this was to try all sorts of avenues, instead of being bound by what one thinks is the correct way. That is – do not exclude possible ideas without giving them a trial run first, but I guess that is a very important lesson to actually learn, and I'll do my best to apply it.
That was a strange day :)