Regarding Indexes

[sendba]

All

I have a table which has 30 Million rows and my developer has created a SP which queries the 30 Million row table. The query is

select count(*)
from Tablename(nolock)
where typeid = 1
and ((transstatusid = 1) or (transstatusid is null))
and responsetime >= @currenttime_Start and responsetime < @CurrentTime_end

I have created a composite index for the table as follows:

index name :idx_hourlytrans
index : nonclustered located on PRIMARY
Indexed Columns : TypeID, TransStatusID, ResponseTime

Whenever i run an estimated Execution Plan for the above query it goes for an Index SCAN on this table.This query takes about 2 minutes to run.
Please let me know what should i do so that i can eliminate the Index Scan and go for Index Usage.

Sen

 

[ptheriault]

Eleminate the count(*),
Count the PK field.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[sendba]

I replaced count(*) with count(PKField) Even then it shows the index Scan.
Any suggestions

 

[ptheriault]

You could turn your index into a cover index.

add the PK to idx_hourlytrans

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

The two fields (other than the date field) what are the possible values in those columns?

When you created the index what order were the columns created in? Does the table have a clustered index on it? Can we try making this index the clustered index?

Try putting the date field as the first field in the index (also try moving the between part to the top of the where clause).

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sendba]

Guys

I have found out the issue but I need you guidance to solve the problem.
The issue is in the following where clause statment
((transstatusid = 1) or (transstatusid is null))

Is there any way to split this query.

Thanks
Sen

 

[ptheriault]

I believe this part of your query is causing the scan.

"transstatusid is null"

Because null is unknown you are going to get an index scan. If you remove that do you still get a scan?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[sendba]

I know this causes the problem but is there any way i can resolve it. It is necessary in the Query.

Sen

 

[ptheriault]

I don't believe so. Because you are searching for unknown, SQL Server must scan the table. That is one of the problems with allowing NULLs

- Paul

- If at first you don't succeed, find out if the loser gets anything.