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.