September 13, 2003 at 3:59 pm
Hi, All:
I have a question about adding index to improve the performance of the query. I have a big table called c and relatively
small table called d. The number of rows in c is several handred million. I have a query which is used by many applications
to get data from these two tables, the performance of this query is horrible, sometimes it uses CPU for as long as 20
seconds.
SELECT d.d1, COUNT(c.c3)
FROM c, d
WHERE d.d1 = c.c2
AND c.c1 > '06/01/03'
AND c.c1 < '06/05/03'
AND DATEPART( hour, c.c1 ) >= 2
AND DATEPART( hour, c.c1 ) <= 10
AND d.d2 = 1
GROUP BY d.d1
c table ( The primary key of this table is the combination of c1, c2, c3 and another varchar(20) column)
c1 datetime
c2 int
c3 int
d table
d1 int (primary key)
d2 int
I wonder if there is a way to improve the performance of this query by adding a clustered index on (c1, c2, c3) and another
clustered index on d1
Thanks a lot!
Jason
Edited by - zxmgh on 09/13/2003 4:00:04 PM
September 13, 2003 at 6:39 pm
I'd stick with standard index on the primary key, look at using the clustered index for the date. Probably need to take a look to see what datepart is costing you, might be worth creating a computed column and indexing.
Andy
September 14, 2003 at 12:05 pm
To add to Andy's post, When it comes to things like this I would recommend lots of playing (or educated guesses, whichever you want to call it)
99% of the time I will not use composite clustered indexes as they bloat all non-clustered indexes and will cause unpredictable page splits on inserts, updates.
You can use the command
"set statistics IO on"
before your query to see the logical IO (which is the number of times SQL had to access a page (not no. of pages))
Keep your current config, run that command, see the logical IO and then try adding indexes. I'm not sure if these are the only columns in your tables, if they are, it should be quick work to find out which index combination produces the least amount of page reads.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply