As if I needed any further convincing that nested views were bad....

The other day I had to write a particularly ugly query to recover some lost data.  The query was complicated (please don't ask) and the resulting SQL had 5 (five) levels of nested views.  I realized that this was going to need some optimization, but I didn't know it would turn out to be that bad just for testing purposes so I ran the query on an offline copy of some customer data.  Long story short, the query was so slow that I thought the server was hanging.  It took me 9 minutes to recover 3 rows of data and after doing some quick calculations I realized that the query was going to take 108 DAYS to complete at this rate.

Realizing that the customer probably wanted the fix sometime this Spring, I killed the query and began optimizing.  I decided to take 4 out of the 5 levels of nested views out of the equation by persisting that result set into a temp table and then running the bulk of the query off the temp table.  The results were amazing...it took the query from 108 days to just over an hour.

Being that I broke several personal records with this event I just had to post it and let everyone know 🙂