Date Comparison Using Table Scan ... Sometimes

Question

Post reply

Date Comparison Using Table Scan ... Sometimes

dkschill

Hall of Fame

Points: 3375
More actions
February 7, 2011 at 11:33 am

#246380

Below is an example of the data I am trying to query. The example does NOT reflect the issue, but gives context to my question.
DECLARE @TestTable TABLE
(
org INT NOT NULL,
dept CHAR(5) NOT NULL,
roleId INT NOT NULL,
empId INT NOT NULL,
startDate DATETIME NOT NULL,
endDate DATETIME,
PRIMARY KEY (org,dept,empId,startDate)
)
INSERT @TestTable
SELECT * FROM
(
select 1 as org,'1' as dept,1 as roleId,1 as empId,'1/1/1' as startDate,null as endDate
UNION
select 1,'1',1,2,'1/2/1',null
UNION
select 1,'1',1,3,'1/3/1',null
UNION
select 1,'1',1,4,'1/4/1',null
UNION
select 2,'2',2,5,'2/5/2',null
UNION
select 2,'2',2,6,'2/6/2',null
UNION
select 2,'2',2,7,'2/7/2',null
UNION
select 2,'2',2,8,'2/8/2',null
UNION
select 2,'2',2,10,'2/8/2',null
UNION
select 2,'2',2,9,'2/9/2',null
) AS A
SELECT * FROM @TestTable WHERE org = 1 AND dept = '1' AND roleId = 1 AND startDate <= GETDATE() AND (endDate IS NULL OR endDate > GETDATE())
SELECT * FROM @TestTable WHERE org = 2 AND dept = '2' AND roleId = 2 AND startDate <= GETDATE() AND (endDate IS NULL OR endDate > GETDATE())
When I run the two scripts one does a table scan and the other performs an index seek. Why would that be happening? Any help would be greatly appreciated. As a side question does it hurt to use ISNULL in place of the OR clause?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

I think we'd want to see the actual execution plan to be sure, you might benefit from posting that.

i think the issue is going to be related to this:

AND (endDate IS NULL OR endDate > GETDATE())

the OR portion is probably going to cause the scan...

assume that because of the StartDate, the compiler can shortcut and determine that the selected rows have no NULL endDate values...then it can use an index seek to determine if endDate is > GETDATE.

but if the compiler cannot determine whether endDate is null based on the Startdat/other criteria, it must scan every row to determine which are null or not, and then compare them to the startDate.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

vishal#SQL Mr or Mrs. 500 Points: 560 More actions · Answer 2

Both the statements are performing Clustered Index Seek for me.

in the actual plan

:w00t:

dkschill Hall of Fame Points: 3375 More actions · Answer 3

The test does not duplicate the problem it only helps build context. Unfortunately I was not able to put a test together to duplicate the issue. I am not sure what to post on the execution plan. I don't feel that it would be appropriate to post a non-test data driven execution plan. I can say that the one that performs the index seek also performs a RID Lookup and a Nested Loops operation. I may need to add that the index is NonClustered.

I hope this adds some value,

Dane

dkschill Hall of Fame Points: 3375 More actions · Answer 4

I think I found my answer here:

http://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/