I've been having a look at a project that is underperforming when selecting from a view, and noticed that in the primary View there are a number of interesting joins going on.
The structure of the tables is basically
CREATE Table #Partitioned (
Id Int Identity(1,1) NOT FOR REPLICATION
Create Table #Lookup (
ColId Int Identity(1,1) NOT FOR REPLICATION
With the table #Partitioned Partitioned by SysId, with a compound PK consisting of Id and SysId
The table #Lookup has a Compound Primary key of ColId and SysId, even though ColId is Unique, due to it being an Identity column
The query in view that joins these is basically
from #Partitioned p
LEFT JOIN #Lookup l
LEFT JOIN #Lookup l2
LEFT JOIN #Lookup l3
Row counts approx 10 million rows in #Partitioned and 50,000 rows in #Lookup.
I've done some preliminary testing by removing the SysId, from the Join and had around 4-5% performance increase with no visible impact on the result set.
In both instances the SQL server Query plan (Actual and Estimated) both show a Table Scan of the #Lookup
I've asked why this was done and I received an answer i didnt think was correct, so I would like to understand the following
1) Is the SysId in the PK on the #lookup table pointless? As the col1Id is Unique by default so it doesnt need the additional column to make it unique.
2) Why doesnt SQL server use the PK on the join?
3) Would an Index hint on the join Increase the performance?
I cant give actual query plans or code, but would just love to understand the rational behind this design.
_________________________________________________________________________SSC Guide to Posting and Best Practices