index scan to seek - SQL Server Forums

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)

index scan to seek

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2011-02-15 : 17:47:05

2 tables

create sales
( sales_id int primary key identity,
store_sales_id int ,
product_id int ,
quantity int ,
create_date datetime
)

create StoreSales
( Store_Sales_id int primary key identity,
store_id int ,
store_type varchar(10),
create_date datetime)

CREATE NONCLUSTERED INDEX store_sales_idx
ON [dbo].[Sales] ([Store_Sales_id])

CREATE NONCLUSTERED INDEX sales_date
ON [dbo].[Sales] ([create_date])

CREATE NONCLUSTERED INDEX store_sales_date
ON [dbo].[StoreSales] ([create_date])

select ss.sales_id, so.store_sales_id from
sales s join storesales ss on s.store_sales_id
where s.create_date >= DATEADD(dd,(DATEDIFF(dd,0,getdate()-1)),0)
and s.create_date < DATEADD(dd,(DATEDIFF(dd,0,getdate())),0) )

Millions of rows exist in each table .This is doing index scan . so taking lot of time for a given day comparison .

How can i make index seek ?

------------------------

agadha jala sanchari rohita naiva garvitam
gandusha jala matrena safari pharpharayate

------------------------
Chanakya
Takhyashila,Bharat

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-02-16 : 00:43:57

Make your index on create_date a covering index. Likely the cost of the key lookups is considered far too high.

--
Gail Shaw
SQL Server MVP

konark
Yak Posting Veteran

60 Posts

Posted - 2011-02-16 : 13:05:38

already tried by modifying sales_date index to include sales_id and store_sales_id .But still doing index scan on StoreSales table.

------------------------

agadha jala sanchari rohita naiva garvitam
gandusha jala matrena safari pharpharayate

------------------------
Chanakya
Takhyashila,Bharat

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2011-02-16 : 15:50:44

Your example has highly flawed syntax, which means you made it up to showcase an example. That is fine, but I have a feeling you are over simplifying it which is why the covering index isn't working. Can you provide the real schema and query you are using ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources