Warning: Use of undefined constant íWP_POST_REVISIONSí - assumed 'íWP_POST_REVISIONSí' (this will throw an Error in a future version of PHP) in /homepages/10/d210128362/htdocs/wp-config.php on line 109

Warning: Cannot modify header information - headers already sent by (output started at /homepages/10/d210128362/htdocs/wp-config.php:109) in /homepages/10/d210128362/htdocs/wordpress/wp-includes/pluggable.php on line 1210
T-SQL Tuesday #23: Fixing Joined Views »
Warning: Declaration of Suffusion_MM_Walker::start_el(&$output, $item, $depth, $args) should be compatible with Walker_Nav_Menu::start_el(&$output, $item, $depth = 0, $args = Array, $id = 0) in /homepages/10/d210128362/htdocs/wordpress/wp-content/themes/suffusion/library/suffusion-walkers.php on line 39

Warning: count(): Parameter must be an array or an object that implements Countable in /homepages/10/d210128362/htdocs/wordpress/wp-includes/post-template.php on line 284
Oct 042011

This month’s TSQL Tuesday topic, hosted by Stuart Ainsworth, is Joins.¬† I am going to share a story of a performance improvement that I made with joined views.

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.

And the users rejoice at how fast their report now runs!

  2 Responses to “T-SQL Tuesday #23: Fixing Joined Views”

  1. Just an FYI that Plan Explorer is from SQL Sentry, not Red-Gate. And, yes, it’s a great tool. =^)

Leave a Reply to Matt Cancel reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>