Post reply

Indexing options to increase speed

  • May 20, 2008 at 3:56 pm

    #187982

    I am looking for some recommendations or pointers with regards to creating a set of indexes on a couple of tables to help with reporting speeds using SQL Server 2005

    - Data in both tables is read only (except when a nightly process populates new data into both tables)

    - Tables are classic parent child with an incremental ID (PK) on each

    - There is a clustered index on both PK’s no other indexes

    - TableA (parent) has about 2.5 million rows

    - TableB (child) has about 5.6 million rows

    - TableA and TableB have a foreign key relationship

    - Almost all of the reports do a GroupBy / Count from a nested sub query (not my idea)

    - Both tables are always inner joined via the TableA.ID and TableB.TableA_ID fields

    - There is a consistent filter (used all the time) on TableA.AccountID and TableB.DateCreated and a third filter used about 50% of the time on TableB.TypeID

    - Report traffic is run newest to oldest with regards to the TableB.DateCreated field and month to date is the most common filter

    Primary Columns in TableA

    ID int

    DateCreated (DateTime)

    AccountID int

    SessionID int

    Primary Columns in TableB

    ID int

    TableA_ID

    DateCreated

    TypeID int

    Base on what I have been reading and such a possible candidate for a clustered index for each table would be:

    TableA

    {DateCreated desc, AccountID asc, SessionID asc}

    TableB

    {DateCreated desc, TypeID asc}

    (would TableA_ID be another field in the index or would that make a difference)

    There are at least three columns in each table (no more than 6) that are used to group the data to provide counts based on the groupings. They can be either all from the same table or from each table.

    Would it be better to create a covering non-clustered index for these fields or include more of them on the clustered index (the ones that are used the most)?

    From what I have also read the non-clustered indexes will utilize the clustered index (to some degree). This would mean a small clutered index with a combination of a few smaller non-clustered indexes might perform better?

    Any thoughts on my thinking would be appreciated (or just tell me I am out to lunch).

    I have also thought of de-normalizing the data into reporting tables and indexing those to help with the speed too.

    Thanks

    - William

  • May 20, 2008 at 5:36 pm

    #818289

    Table_A should have the clustered index on the ID column for the tables you have shown. Otherwise, not enough info...

    Write the query you want and tune it by looking at the execution plan and, perhaps, using the index tuning wizard.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • May 21, 2008 at 12:49 am

    #818376

    Clustered indexes should (imho) be as narrow as possible. From what you've said, I'd second Jeff and go for a cluster on A on just the ID. You can create a covering NC index on DateCreated, AccountID, SessionID

    All nonclustered indexes include the clustering key within, as the address of the rows. The clustering key is not part of the NC index key though

    For table B, maybe just have the cluster on DateCreated. This is just a suggestion, as it is very difficult to do proper index tuning withou seeing execution plans, IO stats and the like.

    I would suggest you set up somewhere to test, try various combinations of cluster and nonclustered indexes, look at the exec plan, see how SQL uses tham and tweak until its running optimally

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 21, 2008 at 6:13 am

    #818502

    Just to be a bit contrarian, I agree with Jeff & Gail on TableA, but on Table B, I'd look at making the foreign key value from TableA the leading edge of the clustered index and compound it with the date value. But again, you need to test to see what works. Considering the volume of data, I'd emphasize getting a good clustered index seek and avoiding bookmark lookups. Still, testing it is the only way to know for sure, as Gail points out.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • May 22, 2008 at 2:19 pm

    #819376

    Thanks for all the input 🙂

    - William

Viewing 5 posts - 1 through 4 (of 4 total)

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