THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

When correlated columns are in different tables

In my previous post

I described how correlation between columns may confuse the optimizer and cause it to choose an inefficient plan. I also recommended index covering as the most robust way to optimize queries that involve correlated columns.

However, there are quite a few cases when correlated columns are in different tables. For example,  employees' home zipcodes and their cell phone area codes may be strongly correlated. Similarly, car models and kinds of repair work for those cars may be strongly correlated too.

In such cases indexed views come very handy, because they can allow the optimizer to satisfy such queries with one and the same plan, the range scan, regardless of the expected cardinality. Even if the optimizer's estimated cardinality is wildly wrong, it does not matter if the execution plan is still the same range scan.

Of course, indexed views come with a rather hefty price tag,

and in some cases you may have to drop them altogether.

 

Published Tuesday, June 09, 2009 10:21 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works at DRW Trading Group in Chicago, where he leads a team of developers, practicing agile development, defensive programming, TDD, and database unit testing.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement