http://www.sqlmag.com/authors/author/author/5777570/rsscomment/5777570en-USFri, 25 May 2012 08:49:19 GMTFri, 25 May 2012 08:49:19 GMThttp://www.sqlmag.com/article/quering/taming-the-skew#commentsAnchorThu, 13 Dec 2001 16:37:28 GMT
The article gives a very good tip how to handle skewed data: divide the table by using local partitioned views. However, this change on the data level (in the DDL) has some negative side effects. Sometimes it is more useful to make the change in the query (in the DML). If there are only a few highly selective values in the table, then the approach "Avoid using variables in batches" could be followed. For example: DECLARE @country char(25) SET @country = ’Germany’ If @country IN (’Germany’,’Netherlands’) SELECT customerid FROM customers WHERE gender = ’M’ and country = @country AND country IN (’Germany’,’Netherlands’) Else SELECT customerid FROM customers WHERE gender = ’M’ and country = @country Because of the predicate AND country IN (’Germany’,’Netherlands’), the query optimizer will choose index access for these cases. Advantage of this approach is, that we still leave the query plan up to the optimizer, that the data definition does not have to be changed, but that the performance has been greatly increased for the skewed values. Gert-Jan]]>Gert-Jan Strik Thu, 13 Dec 2001 16:37:28 GMThttp://www.sqlmag.com/article/quering/taming-the-skew#commentsAnchor