Which column to index? Date or TestNo

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

SQL Server Development (2000)

Which column to index? Date or TestNo

Author

Topic

baranozgul
Starting Member

20 Posts

Posted - 2004-08-24 : 08:33:26

Hi all,

Suppose the following Table:

CREATE TABLE [DTR] (
[TestNo] [int] NOT NULL ,
[TestedService] [int] NOT NULL ,
[NumOfTries] [int] NOT NULL,
[TestResult] [int] NOT NULL,
[TestDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

Table holds test results for several services.

TestDate column holds datetime values as detailed as including the milliseconds. Values in this column is almost continuous. That is each row has absolutely a different TestDate value.

Would it be wise use the following index,

CREATE CLUSTERED INDEX [IX_STR] ON DTR
(
[TestedService],
[TestDate]
)
WITH DROP_EXISTING

to optimize the following query?

SELECT SUM(NumofRetries) FROM DTR
WHERE
TestedService = 167
AND TestDate < '07.06.2004'
AND TestDate > '04.06.2004'

I doubt indexing such a column results in an index as big as the table itself? Do anyone agree?

Sincerely,
Baran

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-24 : 09:41:47

Do you have a lot of different values in TestedService? If you dont, then you might see better performance by creating a clustered index on TestDate alone. How much data do you have, Baran? Why don't you give it a spin and test various combinations? That's the only way to find out

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-24 : 09:49:46

Are the test dates added in some kind of order? If they will be added randomly rather than in date order then you would have to be careful about page splits.

-------
Moo. :)

baranozgul
Starting Member

20 Posts

Posted - 2004-08-25 : 00:34:20

Hi,

The TestedService column has 60-70 different values which is fairly low for a 600K row database.

And in addition TestDate values are determined by getDate() default value function, meaning they are always ordered.

What additional suggestions may be made, upon this information?

Sicerely,
Baran

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-25 : 04:16:40

My first try would be:
UNIQUE CLUSTERED INDEX on TestDate
INDEX ON TestedService

Rationale:
Easy to insert the data sequentially as you say it is.
Efficient to retrieve data by TestedService and TestDate

/rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources