Microsoft – SQL Server – Slow Predicate Queries

Background

Reviewing a set of Ad-Hoc Queries and noticed that depending on the number of predicates in a Query our response time is sometimes a bit jerky. After a certain threshold (around the number of arguments), some queries begin to run slower.

Queries

select *
from   table1
where  col2 in (1,10, 23, 56)

Much slower query:


select *
from   table1
where  col2 in (
                         1,10, 23, 56
                       , 12, 15, 2010, 2020
                       , 4012, 890, 17891, 901
                       , 56, 17, 56, 301, 107
                       , 108, 4676, 307, 415
                )

Review the Query Plan and found that the main differences are:

  1. In the Slower Query, we have a filter box.  The “Filter” entry basically states “restricting the set of rows based on a predicate”

Checked the usual suspects:

  1. Is col2 a computed column
  2. Do we have an even distribution for col2

 

Pertinent Blogs

Went Googling and found a couple of interesting Blog postings:

  1. Query Performance, Scaler UDFs, and predicate pushdown (Dimitri Furman) http://blogs.msdn.com/b/dfurman/archive/2009/12/02/query-performance-scalar-udfs-and-predicate-pushdown.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s