how to avoid index scan on this query

Question

how to avoid index scan on this query

kbhanu15

SSCarpal Tunnel

Points: 4493
More actions
March 12, 2014 at 3:23 am

#305194

Hi ALl,
I want to avoid index scan by creating one more index,
can you please help me to do this on this or condition
details:
CREATE TABLE bhanu1
(
id INT,
credate DATETIME,
modidate DATETIME
)
INSERT INTO bhanu1
SELECT 1,GETDATE()+2,GETDATE()+3
UNION
SELECT 2,GETDATE()+3,GETDATE()+4
UNION
SELECT 3,GETDATE()+4,GETDATE()+5
CREATE UNIQUE CLUSTERED INDEX ix_id ON bhanu1(id)
SELECT id FROM bhanu1----index scan
where credate > GETDATE()
OR modidate > GETDATE()
please find the execution plan in attachments.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What are the current indexes on that table?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 2

currently we have clustered index on this table (id) columns.

CREATE UNIQUE CLUSTERED INDEX ix_id ON bhanu1(id)

i want to create non clustered index to avoid the index scan

help me how to do this ?

thanks

Bhanu

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You need two nonclustered indexes, one on each of the columns in the where clause.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 4

as per your advise i created two non clustered indexes:

CREATE nonCLUSTERED INDEX ix_credate ON bhanu1(credate)

GO

CREATE nonCLUSTERED INDEX ix_modidate ON bhanu1(modidate)

GO

then also it is going for index scan and using the clustered index.

CREATE UNIQUE CLUSTERED INDEX ix_id ON bhanu1(id)

please find the execution plan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Probably because with only 3 rows in the table there's no point in doing anything else as the clustered index scan is the most efficient at that tiny data size.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 6

hi,

for sample example i given this tamp table and sample results.

the original query is different in my project.

any other way to avoid this index scan.

i need solution for issue.

Thanks

Bhanu

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

You don't have an issue.

The data volume is so low that the clustered index scan is the most efficient option. If the data volume grows to the point where the table takes more than a page or two, SQL will switch to doing two index seeks, but right now two index seeks will be more work than one scan and so the scan is chosen.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Divine Flame SSCoach Points: 15943 More actions · Answer 8

kbhanu15 (3/12/2014)
hi,
for sample example i given this tamp table and sample results.
the original query is different in my project.
any other way to avoid this index scan.
i need solution for issue.
Thanks
Bhanu

Considering the sample data that you have provided over here, it is unlikely that you will get index seek (even when you have huge number of rows).

As per your test data every Credate & ModiDate that you have inserted is greater than GetDate (your filter condition). Therefore, your query is asking SQL Server to pull out each & every record always. To pull each & every record (or even 80-90% of total records, scan is better than seek so SQL Server will just do that).

Ensure the test data that you take resembles to your production data to better understand the indexes you need to make.

Sujeet Singh

kbhanu15 SSCarpal Tunnel Points: 4493 More actions · Answer 9

kbhanu15

SSCarpal Tunnel

Points: 4493

March 12, 2014 at 5:49 am

#1696324

Thank you for your detailed explanation.