Speed up this query joining to a table multiple times
- by Mongus Pong
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?
