Friday, July 16, 2010

Optimizing SQL Views Profit++, Fun--

While working feverishly towards meeting a looming deadline on some SQL code, a problem arose. A particular UI that relies on a particular stored proc to perform a particular task was bombing. 

I brought up SQL Profiler and retraced the user's steps, and found the culprit, a convoluted view I was joining to as a step within the update seemed to be taking way too long to retrieve data.

The view started out relatively simple, a SELECT over several busy tables joined together along with a few scalar function calls and a very few subqueries nestled within some CASE statements. It evolved, thanks to lack of time and impatience and poor choices on my part, into a bit of a behemoth; it took roughly 1:30 to return the full result set. This didn't seem like a big deal at the time, however, because I planned to use it to pluck out specific entries, for this purpose it returned results in less than a second or two, which was acceptable.

Using it in a join though was a relatively new, hastily chosen design decision, and apparently it was proving a very poor choice since it was causing the UI to time out. I began by optimizing the view, minimizing the interaction with the busy tables as much as possible, avoiding subqueries unless absolutely necessary, and creating some new scalar functions to replace previously funky logic. No joy, however, as this only shaved around 10 seconds off the runtime for a SELECT, too little, I thought.

So began the quest to create a table-value function instead, full of optimizations like stuffing frequently used values into variables and creating a subset of the main tables in a table variable (impossible from within a view) and minimizing the use of scalar functions and even resorting to applying the WITH (NOLOCK) and eliminating string searches wherever possible...

It turns out, however, that the entire two paragraphs of chronicled woe were completely unnecessary. Here's a SELECT from within the stored proc which joins with the aforementioned view, notice anything odd?
SELECT TOP 1 @Fruit = 
        
  CASE 
      WHEN v.Operation IN ('APPLE', 'BANANA', 'GRAPE') THEN v.Operation
      ELSE NULL
  END

FROM dbo.ViewOfPain v
WHERE v.ID = ID 
ORDER BY v.InputDate DESC
  

Let's see, looks like I'm just trying a simple SELECT from the view with a CASE statement, checking the ID against the ID, yeah, that looks fi-- O SHI

Notice the missing '@' sign before the ID in the where clause??! I did, after spending a few hours writing a table function that I turned out not to need. The problem wasn't with the view, but with this bit of code in the stored proc, it was doing the equivalent of a SELECT on the entire view plus a bit more overhead to assign a value to the @Fruit variable. I ran this statement by itself in query analyzer, and indeed, it took well over 1:30 to execute!

Below is the code as it should've been, which returns almost instantaneously.
SELECT TOP 1 @Fruit = 
        
  CASE 
      WHEN v.Operation IN ('APPLE', 'BANANA', 'GRAPE') THEN v.Operation
      ELSE NULL
  END

FROM dbo.ViewOfPain v
WHERE v.ID = @ID
ORDER BY v.InputDate DESC


I'd spent hours optimizing and then finally creating a new table function to replace a view that turned out not to be the culprit. If I'd spot-checked my syntax in the stored proc, it's likely I could've picked out this omission of the '@' much sooner, but I was so tired and far gone into troubleshooting that I was getting increasingly desperate. 

The moral of my story is, take a break, unhook yourself from the problem, and review it again later with a fresh mind.