Speed up this query joining to a table multiple times

Question

Hi,

I have this query that (stripped right down) goes something like this :

SELECT
    [Person_PrimaryContact].[LegalName],
    [Person_Manager].[LegalName],
    [Person_Owner].[LegalName],
    [Person_ProspectOwner].[LegalName],
    [Person_ProspectBDM].[LegalName],
    [Person_ProspectFE].[LegalName],
    [Person_Signatory].[LegalName] 

FROM [Cache]
   LEFT JOIN [dbo].[Person] AS [Person_Owner] WITH (NOLOCK) 
       ON [Person_Owner].[PersonID] = [Cache].[ClientOwnerID]
   LEFT JOIN [dbo].[Person] AS [Person_Manager] WITH (NOLOCK) 
       ON [Person_Manager].[PersonID] = [Cache].[ClientManagerID]
   LEFT JOIN [dbo].[Person] AS [Person_Signatory] WITH (NOLOCK) 
       ON [Person_Signatory].[PersonID] = [Cache].[ClientSignatoryID]
   LEFT JOIN [dbo].[Person] AS [Person_PrimaryContact] WITH (NOLOCK) 
       ON [Person_PrimaryContact].[PersonID] = [Cache].[PrimaryContactID]
   LEFT JOIN [dbo].[Person] AS [Person_ProspectOwner] WITH (NOLOCK) 
       ON [Person_ProspectOwner].[PersonID] = [Cache].[ProspectOwnerID]
   LEFT JOIN [dbo].[Person] AS [Person_ProspectBDM] WITH (NOLOCK) 
       ON [Person_ProspectBDM].[PersonID] = [Cache].[ProspectBDMID]
   LEFT JOIN [dbo].[Person] AS [Person_ProspectFE] WITH (NOLOCK) 
       ON [Person_ProspectFE].[PersonID] = [Cache].[ProspectFEID]

Person is a huge table and each join to it has a pretty significant hit in the execution plan.

Is there anyway I can adjust this query so that I am only linking to it once, or at least get SQL Server to scan through it only once?

EDIT

Here is the plan:

  |--Parallelism(Gather Streams)
       |--Merge Join(Right Outer Join, MERGE:([Person_ProspectFE].[PersonID])=([Cache].[ProspectFEID]), RESIDUAL:([PracticeManagement].[dbo].[Person].[PersonID] as [Person_ProspectFE].[PersonID]=[PracticeManagement].[dbo].[ListCache].[ProspectFEID] as [Cache].[ProspectFEID]))
            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Person_ProspectFE].[PersonID]), ORDER BY:([Person_ProspectFE].[PersonID] ASC))
            |    |--Clustered Index Scan(OBJECT:([PracticeManagement].[dbo].[Person].[Person_PK] AS [Person_ProspectFE]), ORDERED FORWARD)
            |--Sort(ORDER BY:([Cache].[ProspectFEID] ASC))
                 |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Cache].[ProspectFEID]))
                      |--Merge Join(Right Outer Join, MERGE:([Person_ProspectBDM].[PersonID])=([Cache].[ProspectBDMID]), RESIDUAL:([PracticeManagement].[dbo].[Person].[PersonID] as [Person_ProspectBDM].[PersonID]=[PracticeManagement].[dbo].[ListCache].[ProspectBDMID] as [Cache].[ProspectBDMID]))
                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Person_ProspectBDM].[PersonID]), ORDER BY:([Person_ProspectBDM].[PersonID] ASC))
                           |    |--Clustered Index Scan(OBJECT:([PracticeManagement].[dbo].[Person].[Person_PK] AS [Person_ProspectBDM]), ORDERED FORWARD)
                           |--Sort(ORDER BY:([Cache].[ProspectBDMID] ASC))
                                |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Cache].[ProspectBDMID]))
                                     |--Merge Join(Right Outer Join, MERGE:([Person_ProspectOwner].[PersonID])=([Cache].[ProspectOwnerID]), RESIDUAL:([PracticeManagement].[dbo].[Person].[PersonID] as [Person_ProspectOwner].[PersonID]=[PracticeManagement].[dbo].[ListCache].[ProspectOwnerID] as [Cache].[ProspectOwnerID]))
                                          |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Person_ProspectOwner].[PersonID]), ORDER BY:([Person_ProspectOwner].[PersonID] ASC))
                                          |    |--Clustered Index Scan(OBJECT:([PracticeManagement].[dbo].[Person].[Person_PK] AS [Person_ProspectOwner]), ORDERED FORWARD)
                                          |--Sort(ORDER BY:([Cache].[ProspectOwnerID] ASC))
                                               |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Cache].[ProspectOwnerID]))
                                                    |--Merge Join(Right Outer Join, MERGE:([Person_PrimaryContact].[PersonID])=([Cache].[PrimaryContactID]), RESIDUAL:([PracticeManagement].[dbo].[Person].[PersonID] as [Person_PrimaryContact].[PersonID]=[PracticeManagement].[dbo].[ListCache].[PrimaryContactID] as [Cache].[PrimaryContactID]))
                                                         |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Person_PrimaryContact].[PersonID]), ORDER BY:([Person_PrimaryContact].[PersonID] ASC))
                                                         |    |--Clustered Index Scan(OBJECT:([PracticeManagement].[dbo].[Person].[Person_PK] AS [Person_PrimaryContact]), ORDERED FORWARD)
                                                         |--Sort(ORDER BY:([Cache].[PrimaryContactID] ASC))
                                                              |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Cache].[PrimaryContactID]))
                                                                   |--Merge Join(Right Outer Join, MERGE:([Person_Signatory].[PersonID])=([Cache].[ClientSignatoryID]), RESIDUAL:([PracticeManagement].[dbo].[Person].[PersonID] as [Person_Signatory].[PersonID]=[PracticeManagement].[dbo].[ListCache].[ClientSignatoryID] as [Cache].[ClientSignatoryID]))
                                                                        |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Person_Signatory].[PersonID]), ORDER BY:([Person_Signatory].[PersonID] ASC))
                                                                        |    |--Clustered Index Scan(OBJECT:([PracticeManagement].[dbo].[Person].[Person_PK] AS [Person_Signatory]), ORDERED FORWARD)
                                                                        |--Sort(ORDER BY:([Cache].[ClientSignatoryID] ASC))
                                                                             |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Cache].[ClientSignatoryID]))
                                                                                  |--Merge Join(Right Outer Join, MERGE:([Person_Manager].[PersonID])=([Cache].[ClientManagerID]), RESIDUAL:([PracticeManagement].[dbo].[Person].[PersonID] as [Person_Manager].[PersonID]=[PracticeManagement].[dbo].[ListCache].[ClientManagerID] as [Cache].[ClientManagerID]))
                                                                                       |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Person_Manager].[PersonID]), ORDER BY:([Person_Manager].[PersonID] ASC))
                                                                                       |    |--Clustered Index Scan(OBJECT:([PracticeManagement].[dbo].[Person].[Person_PK] AS [Person_Manager]), ORDERED FORWARD)
                                                                                       |--Sort(ORDER BY:([Cache].[ClientManagerID] ASC))
                                                                                            |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Cache].[ClientManagerID]))
                                                                                                 |--Merge Join(Right Outer Join, MERGE:([Person_Owner].[PersonID])=([Cache].[ClientOwnerID]), RESIDUAL:([PracticeManagement].[dbo].[Person].[PersonID] as [Person_Owner].[PersonID]=[PracticeManagement].[dbo].[ListCache].[ClientOwnerID] as [Cache].[ClientOwnerID]))
                                                                                                      |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Person_Owner].[PersonID]), ORDER BY:([Person_Owner].[PersonID] ASC))
                                                                                                      |    |--Clustered Index Scan(OBJECT:([PracticeManagement].[dbo].[Person].[Person_PK] AS [Person_Owner]), ORDERED FORWARD)
                                                                                                      |--Sort(ORDER BY:([Cache].[ClientOwnerID] ASC))
                                                                                                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Cache].[ClientOwnerID]))
                                                                                                                |--Clustered Index Scan(OBJECT:([PracticeManagement].[dbo].[ListCache].[IX_ListCache_Type] AS [Cache]))

Answer 1

Make sure you've created indexes on not only the Person.PersonID field but also the fields in Cache that are being used to join the two tables.

Answer 2

Assuming PersonId is the primary Key on the Person table, and that you have a Primary Key index on that field, (which should happen automatically when you designated that as PK), then no, that PK index is the best way to get at the seven different values in the Person table that you need to access... And what you are doing is the best way... But it should not be scanning the person table seven times, it should be traversing the Primary Key index on the Person table seven times, which is much faster... Check the query plan and ensure that that is what it is doing...

Answer 3

Have you tried using profiler and the DTA to see if it suggests any indexes?

Answer 4

One (rather long-winded) approach would be to select PersonID and LegalName into a separate temporary table for each of the Cache Person roles you're interested in, like so:

select [Person].[PersonID], [Person.LegalName]
into #Person_Owner
from [Cache] join [Person] with (nolock)
on [Person].[PersonID] = [Cache].[ClientOwnerID]

Then run a final query, linking each of the Cache Person roles to the relevant temporary tables. It's messy and longwinded (in code terms), but it should ensure that you only retrieve the Person records you need once for each role.

Alternatively, you could do a cartesian join between [Cache] and [Person], then use case structures to determine which Cache roles match which Legal Names. It would ensure that the [Person] table is only scanned once, but the result set would be gigantic; it's only likely to be faster than your existing query if it's logical reads that are slowing you down, and you have an awful lot of memory to spare on your DB server.

Answer 5

If slower inserts, updates and deletes aren't a problem for you, you can create an indexed (materialized) view for your query and select your records from that view.

http://technet.microsoft.com/en-us/library/cc917715.aspx