How to add index to improve the performance?

  • zxmgh

    SSC Eights!

    Points: 911

    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


    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!


    Edited by - zxmgh on 09/13/2003 4:00:04 PM

  • Andy Warren

    SSC Guru

    Points: 119686

    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.


  • ray_higdon

    SSCarpal Tunnel

    Points: 4589

    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.


    Ray Higdon MCSE, MCDBA, CCNA


    Ray Higdon MCSE, MCDBA, CCNA

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply