Seek Predicate versus Predicate problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Seek Predicate versus Predicate problem

Author

Topic

charterguide
Starting Member

5 Posts

Posted - 2011-02-05 : 17:35:18

Trying to optimize this query and I noticed the execution plan shows a Predicate in Index Scan on my index for the MonthID column and I don't understand why. I've shortened this query up a bit to make it easier to discuss and still duplicate the same behavior.

This query will create a Predicate in the Index Scan:

select distinct a.ActivityReportID
from dbo.ActivityReport
where (a.MonthID = a.MonthID)

However, this query uses an index seek and shows a Seek Predicate used.

select distinct a.ActivityReportID
from dbo.ActivityReport
where (a.MonthID = @MonthID)

My real word query does the following. Essentially I've got a ton of optional search criteria for my query.

where (a.MonthID = case when @MonthID > 0 then @MonthID else a.MonthID end)

Why would the query behave differently just because I am checking to see if the column equals its current value?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-05 : 17:40:38

Have a look at parameter sniffing
http://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

Poor planning on your part does not constitute an emergency on my part.

charterguide
Starting Member

5 Posts

Posted - 2011-02-05 : 17:50:50

I'm familiar with parameter sniffing. This behavior also shows itself with the query in a stand alone query window in management studio. How would parameter sniffing come into play?

select distinct a.ActivityReportID
from dbo.ActivityReport
where (a.MonthID = a.MonthID)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-05 : 18:18:43

Well, you are returning all records in that sample query, and SQL may have decided a scan was better than a seek. We might need more info about the actual query and structure/indexes, columns selected...etc to say why the behavior persists.

Does the real world query with all the case/when logic in the where clause use a seek or scan predicate?

My guess is it has to scan the table for equality of Monthid=monthId. Clearly the 2nd one would seek because it is looking for a specific value in the index.

Poor planning on your part does not constitute an emergency on my part.

charterguide
Starting Member

5 Posts

Posted - 2011-02-05 : 18:46:08

Hmm, maybe my understanding was wrong. I would have thought the MonthID value would in the index I created for the column. So, I'm not sure why it would it would need to go directly to the row.

charterguide
Starting Member

5 Posts

Posted - 2011-02-05 : 18:51:24

In other words, if a Seek is used for a.MonthID = @MonthID, then would wouldn't also use a Seek for a.MonthID equals itself? The value should be in the index.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-05 : 18:59:10

Well, it isn't exactly clear what you are trying to solve.

What do you mean "not sure why it would go directly to the row". By definition, that is what an index seek is doing. it is seeking to the record your criteria specifies (at least for a sargable, indexed column value). If your real world query has many columns..are the included in a covering index?

(a.MonthID = case when @MonthID > 0 then @MonthID else a.MonthID end)

I think you want
(a.monthID = @monthid and @monthid >0) OR
(a.monthID = a.MonthID and @monthid = 0)

Seems if 0 is passed you are trying to use that as a "return all months condition"

good SQLTeam article here on avoiding CASE in where clauses, and to use boolean logic : http://weblogs.sqlteam.com/jeffs/archive/2003/11/14/513.aspx

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-05 : 19:03:50

quote:
Originally posted by charterguide

In other words, if a Seek is used for a.MonthID = @MonthID, then would wouldn't also use a Seek for a.MonthID equals itself? The value should be in the index.

in a.MonthID = a.MonthID it would have to do a scan. It has to scan the table for all conditions where a.MonthID = a.MonthID ..plus you are returning ALL records in that particular example, and a scan would usually be chosen over a seek in that case (I think)..especially if it is not a particularly large table.

A seek looks for a specific value. In the case of columnA = columnA, it has to scan each record to see if the condition is true (it doesn't just say "duh, of course it equals itself". Plus, since it returns all records, there is no need for a seek. Nothing to optimize.

My guess is you get the same scan with WHERE 1 =1.

Poor planning on your part does not constitute an emergency on my part.

charterguide
Starting Member

5 Posts

Posted - 2011-02-05 : 19:15:14

Interesting. So, it performs a Seek by recognizing that @MonthID is a specific value and can't Seek by using the specific value in a.MonthID of that particular record? I would have expected it to recognize that a.MonthID is a specific value and perform the Seek.

Tested the boolean logic you gave me and got the exact same plan and performance results. You are dead on correct on what I'm trying to do with my real world query. If the monthid >0, it means the user selected that as a filter criteria. If not, then I to return the record regardless of MonthID. I have far too many filter criteria to create separate queries based on IF statements versus using case statements in my WHERE clause.

Read the other article about boolean logic. What wasn't clear is whether the author's preferences had any impact on performance. I have not see that so far. I personally find the CASE statements much easier to read and support. But, I'd give that up if I could prove their was a performance benefit.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-05 : 19:42:39

Play around with this:


--fill a table with some number of dates

Create table [test] (dates datetime not null)

Declare @d int 


INSERT INTO test
SELECT DISTINCT dateadd(mi,a.number*b.number ,'20070101')
FROM master..spt_values a ,master..spt_values b 
Where a.type = 'p' and b.type = 'p'  
and a.number<100 and b.number between 50 and 255  

GO
Create Unique NonClustered Index IX_Dates on Test(Dates)

GO

Declare @date datetime
Set @date = '2007-01-11 02:30:00.000'
--demonstrate a scan, nonclustered index
SELECT dates
  FROM test
WHERE dates= dates

--demonstrate a seek

Select dates
  FROM test
WHERE dates = '2007-01-11 02:30:00.000'

--demonstrate a seek

Select dates
  FROM test
WHERE dates = @date

--demonstrate a scan
 Set @date = 0
Select dates
  FROM test
WHERE dates = (Case when @date > 0 then @date else dates end)

--demonstrate a scan
Declare @date datetime
 
Set @date = '2007-01-11 02:30:00.000'
Select dates
  FROM test
WHERE dates = (Case when @date > 0 then @date else dates end)

--demonstrate a seek

Select dates
  FROM test
WHERE dates = @date and @date >0

The Where (Case When..) will cause a seek each time, whether date is valid or is 0. same with dates=dates. playing around with little batches like this can help you understand how where clauses might affect things.

IF @date >0
Select .....WHERE a.monthid = @monthid

If @date = 0
Select .... --no where clause

would be more efficient than a case. BUt the OR statement I propose will do a SEEK if @monthid >0. The @month=0 will always perform a scan on the sample you provide.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources