Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Primary Key Vs Clustered Index: Performance Tuning Expand / Collapse
Author
Message
Posted Wednesday, December 22, 2010 4:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:18 AM
Points: 5,014, Visits: 10,515
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1038158
Posted Wednesday, December 22, 2010 6:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 6, 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
Posted Wednesday, December 22, 2010 6:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:18 AM
Points: 5,014, Visits: 10,515
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1038228
Posted Wednesday, December 22, 2010 6:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 6, 2011 9:39 PM
Points: 20, Visits: 101
I'll check and surely let you know the outcome....
Post #1038235
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse