Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

More info on nested views causing performance problems

 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 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 :-)


No comments.

Leave a Comment

Please register or log in to leave a comment.