One of the main vendor-built applications that I support has views built on top of tables.¬† Unfortunately that’s not the end of the story.¬† These views are built on top of views, which join together other views, which link back to the database tables.¬† In all the training materials provided by the vendor, they say to *always* use the views when writing a report on the data and never directly query the table.
Using the vendor provided views generally works out OK and performs reasonably well.¬† One particular report I wrote kept bugging me due to how long it took to run (several minutes each time). compared to how much data was actually returned.¬† So I started looking at the execution times and the query plans.¬† Note, I took the screenshots below using the fantastic free tool from SQL Sentry, SQL Sentry Plan Explorer.
This report needed to join 5 tables to get the data that I needed.¬† Using the vendor provided views, here is the join diagram I started out with:
You can see that the vendor views are joining together a lot more hidden tables (table & field names blurred to protect the innocent) than the 5 I actually need.¬† The nested views are even hitting the same tables more than once.
Here is the original query plan:
You can’t see it in the screen shot, but one of the thick lines in the middle is representing 23 million rows!
OK, time to pull out the detective hat.¬† I decided to rewrite the query using only the base tables.¬† I had to do a bit of extra work with things like UTC datetime vs. local time which the views converted.¬† After the rewrite, the join diagram looked like this:
Here is the final query plan:
The highest number of rows coming through is about 11,000.¬† A far cry from the 23 million rows in the original query! The execution plan also looks a lot leaner than what I started with as well.
The original query was running with an average CPU time = 26062 ms, and an average elapsed time = 26424 ms.
My rewritten query is now running with an average CPU time = 0 ms, and an average elapsed time = 266 ms.
Looking at the actual execution plan, SSMS is prompting me that there is a missing index for my new query, and there is still a Clustered Index Scan that accounts for 87% of the query. ¬† I may look into that more at a later date, but for now I am very happy running a query in a couple of seconds that used to take several minutes. I don’t think it’s really worth the extra effort to try to shave another 100 ms off of a query that completes in under 300 ms.
The vendor supplied nested views were each joining together multiple views and tables, which was causing a lot of extra and unnecessary bloat in my query.¬† An hour of work and cutting out all the bloat made a huge difference in this particular report’s run time.