Post reply

Primary Key Vs Clustered Index: Performance Tuning

  • December 21, 2010 at 12:31 am

    #148019

    I've a performance related issue.

    I've one big table A which is been used in one query having join with another big table B. Table A has no Primary key but a clustered index defined on four columns (composite) and two more nonclustered indexes. Table B also has no primary key but a clustered index with five columns (again composite) and three non clustered indexes. All of the indexes are on more than one column.

    While running the query, it taking too much time and execution plan shows 23% of sort (execution plan shows its in one of the case statements with "in (select ")) on one column of table A which is part of clustered index. How should I resolve it ? Secondly, what is the significance of PK and clustered index separately ? Table A comprises of 35% of index seek and Table B shows 18% of index seek.

    The query comprises of joins between these two tables and inserting in another table. Lots of columns in select statement and also few case statements with "exists (select" in them.

  • December 21, 2010 at 1:15 am

    #1264933

    Can you post the actual execution plan?

    You may also take a look at this article on how to post performace problems:

    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    -- Gianluca Sartori

  • December 21, 2010 at 6:08 am

    #1265020

    Not possible to send execution plan here. I created two new tables form above ones without any indexes and tried after replacing those into the query for a common set of parameters. Created primary clustered key constraint on both tables (with three columns on each table). Thereafter created one non clustered index for the three columns showing 18% in Hash Join in Table A (new one). Still taking approx same time (Just 20 seconds efficiency). Kindly suggest.

  • December 21, 2010 at 6:41 am

    #1265029

    Without seeing the exec plan and the table/index definitions, it's really hard to answer these kind of questions. You're just not posting enough information for us to be able to do more than wildly guess.

    Why no exec plan?

    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

  • December 21, 2010 at 6:42 am

    #1265030

    Without a query plan to look at, I would end up with a useless suggestion.

    I'm sure you can live without it.

    -- Gianluca Sartori

  • December 21, 2010 at 6:43 am

    #1265031

    For more information, I took help of sys.dm_db_missing_index_details and other DMVs to check out for missin index details. Table A and B both contains around 64 Lac records and their join result shows around 25k records. Select statement contains few case statements and overall query is taking 3 and half minutes to complete. Table A clustered index seek shows 66% participation.

  • December 21, 2010 at 10:56 pm

    #1265360

    Here's the execution plan.....

  • December 22, 2010 at 12:37 am

    #1265371

    Table and index definitions please?

    I'm guessing you didn't read the article that Gianluca posted.

    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

  • December 22, 2010 at 12:56 am

    #1265375

    Here they are...

  • December 22, 2010 at 2:39 am

    #1265393

    Any suggestions ???? Eagerly waiting your advice... Already tried and tested lots of things but query taking around 3:15 mins at best....

  • December 22, 2010 at 4:03 am

    #1265418

    I think you should try to filter as much as possible the input with an appropriate index, and then join the correlated table.

    Given that usually the primary key is the best possible clustered index, I would work on the nonclustered index side.

    However, I see that you're selecting out a lot of columns from table "A", so that the optimizer could decide to avoid CI lookups and use the clustered index directly. I don't have your data volumes in place, I can't say without testing.

    However, based on your query, I would try to see if a couple of NC Indexes can help.

    I would create these two:

    -- NC Index on Table A

    -- KEY COLUMNS:

    A.ProfileName

    A.DomainName

    A.TaskId

    A.BookName

    A.RunVersion

    -- INCLUDE COLUMNS:

    A.ProductType

    A.PayReceive

    -- NC Index on Table B

    -- KEY COLUMNS:

    B.RunVersion

    B.COBDate

    --INCLUDE COLUMNS:

    B.Side

    B.Currency

    B.PaynonPVaccrual

    B.Notional

    B.RecnonPVaccrual

    B.Mtm3

    Also, you could transform the expression on the COBDate as follows:

    --AND CONVERT(VARCHAR, B.COBDate, 112) = '20101116'

    AND B.COBDate = CONVERT(datetime, '20101116',112)

    Hope this helps. This is the best I can do from here.

    Gianluca

    -- Gianluca Sartori

  • December 22, 2010 at 6:31 am

    #1265468

    Thanks mate. Seems promising. Will try and let you know. However, the sizes of both tables I've already mentioned in earlier posts. Both Table A and B contains around 6.5 Million records.

  • December 22, 2010 at 6:38 am

    #1265475

    ankit.dhasmana (12/22/2010)

    Both Table A and B contains around 6.5 Million records.

    I see. However I have no clue of how the optimizer will decide to work with the new indexes, beacause I'm working on empty tables.

    -- Gianluca Sartori

  • December 22, 2010 at 6:43 am

    #1265482

    I'll check and surely let you know the outcome....

Viewing 14 posts - 1 through 13 (of 13 total)

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