Primary Key Vs Clustered Index: Performance Tuning

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Notification Services » Administration » Primary Key Vs Clustered Index: Performance...

14 posts, Page 2 of 2

««1 2

Primary Key Vs Clustered Index: Performance Tuning

Rate Topic

Display Mode

Topic Options

Author

Message

spaghettidba

Posted Wednesday, December 22, 2010 4:03 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

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

Get your two-cent-answer quickly
The Spaghetti DBA

Post #1038158

iamanks

Posted Wednesday, December 22, 2010 6:31 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, January 06, 2011 9:39 PM
Points: 20, Visits: 101

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.

Post #1038218