Querying against an Index - 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 2000 Forums

Transact-SQL (2000)

Querying against an Index

Author

Topic

baranozgul
Starting Member

20 Posts

Posted - 2005-01-12 : 09:28:34

I have general question regarding querying against a table index.

my table is as follows:
CREATE TABLE [T_Test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ServiceID] [int] NOT NULL ,
[TypeID] [int] NOT NULL ,
[FamilyID] [int] NOT NULL ,
[LocationID] [int] NOT NULL ,
[ChannelID] [int] NOT NULL ,
[ScriptID] [int] NOT NULL ,
[UserID] [int] NOT NULL ,
[ContentID] [int] NOT NULL ,
[PlanID] [int] NOT NULL ,
[Grade] [float] NOT NULL ,
[Date] [datetime] NOT NULL
)

And the non-clustered index is as follows:
CREATE INDEX [IX_T_TEST] ON [dbo].[T_Test](
[ServiceID], [FamilyID], [TypeID], [ChannelID], [LocationID], [UserID], [ScriptID], [PlanID])

table has over 4 million rows now, and I usuallu run queries like:

SELECT T_TEST.ServiceID AS Service,
T_TEST.FamilyID AS TestFamily,
T_Test.TypeID AS TestType,
T_Test.ChannelID AS TestChannel,
Avg(Grade) AS Rate,
Count(1) AS [Count]
FROM T_Test,
WHERE
AND T_Test.ServiceID = @SomeServiceID
AND T_Test.FamilyID = @FamilyID
AND T_Test.TypeID =@SomeTypeID
AND T_Test.ChannelID = @SomeChannelID
AND T_Test.[Date] > @StartDate
AND T_Test.[Date] < @EndDate

Query -as I can see on the execution plan, uses IX_T_TEST index which I mentioned above, and runs below 1 sec.

However If I omit some parameters from the WHERE clause query runs in more than half a minute. This is because it refuses to use the Index

I am new to indexing and performance. How do you think I can keep up with the performance when using a subset of the WHERE clause criteria such as:
WHERE
AND T_Test.FamilyID = @FamilyID
AND T_Test.TypeID =@SomeTypeID
AND T_Test.[Date] > @StartDate
AND T_Test.[Date] < @EndDate

or

WHERE
AND T_Test.ServiceID = @SomeServiceID
AND T_Test.ChannelID = @SomeChannelID
AND T_Test.[Date] > @StartDate

sincerely,
BAran

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-01-12 : 10:56:59

key to having any chance of an index being used to support a fast query, is to ensure that you include "somewhere" in the WHERE clause the 'major' portions of the index...in this case the [ServiceID], [FamilyID], [TypeID], [ChannelID], [LocationID], [UserID], [ScriptID], [PlanID] columns in that order....if per se, you leave out [ServiceId] column...then the index is totally useless (for your query).

likiewise, leaving in all but 'typeid' means that any fields after "typeid" don't count towards influencing the decision to use the index.
Also...based upon the 'statistics' for the table...the index may not be used ANYWAY..IF the SQL Engine decides it would be faster to read the entire table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources