SQLTeam.com | Weblogs | Forums

Long query and how would you start fixing the query

Can you please let me know how to fix the following query?

SELECT
[Filter1].[ID1] AS [ID],
[Filter1].[ProjectID] AS [ProjectID],
[Filter1].[AllDay] AS [AllDay],
[Filter1].[Start] AS [Start],
[Filter1].[End] AS [End],
[Filter1].[Location] AS [Location],
[Filter1].[Notes] AS [Notes],
[Filter1].[Title] AS [Title],
[Filter1].[CreatedByID] AS [CreatedByID],
[Filter1].[ID2] AS [ID1],
[Filter1].[UniqueKey] AS [UniqueKey],
[Filter1].[CustomProjectTypeID1] AS [CustomProjectTypeID],
[Filter1].[PhaseID] AS [PhaseID],
[Filter1].[PhaseDate] AS [PhaseDate],
[Filter1].[EstimatedSettlementValue] AS [EstimatedSettlementValue],
[Filter1].[Number] AS [Number],
[Filter1].[ClientID] AS [ClientID],
[Filter1].[ProjectName] AS [ProjectName],
[Filter1].[ReferrerID] AS [ReferrerID],
[Filter1].[Description] AS [Description],
[Filter1].[CreateDate] AS [CreateDate],
[Filter1].[IncidentDate] AS [IncidentDate],
[Filter1].[ProjectGroupID] AS [ProjectGroupID],
[Filter1].[OrgID1] AS [OrgID],
[Filter1].[LastActivity] AS [LastActivity],
[Extent4].[ID] AS [ID2],
[Extent4].[BirthDate] AS [BirthDate],
[Extent4].[Ssn] AS [Ssn],
[Extent4].[Notes] AS [Notes1],
[Extent4].[FromCompany] AS [FromCompany],
[Extent4].[Specialty] AS [Specialty],
[Extent4].[Gender] AS [Gender],
[Extent4].[Language] AS [Language],
[Extent4].[MaritalStatus] AS [MaritalStatus],
[Extent4].[MiddleName] AS [MiddleName],
[Extent4].[IsTextingPermitted] AS [IsTextingPermitted],
[Extent4].[Remarket] AS [Remarket],
[Extent4].[AbbreviatedName] AS [AbbreviatedName],
[Extent4].[DriverLicenseNumber] AS [DriverLicenseNumber],
[Extent4].[IsTypeClient] AS [IsTypeClient],
[Extent4].[IsTypeAdjuster] AS [IsTypeAdjuster],
[Extent4].[IsTypeAttorney] AS [IsTypeAttorney],
[Extent4].[IsTypeFirm] AS [IsTypeFirm],
[Extent4].[IsTypeExpert] AS [IsTypeExpert],
[Extent4].[IsTypeMedicalProvider] AS [IsTypeMedicalProvider],
[Extent4].[IsTypeInvolvedParty] AS [IsTypeInvolvedParty],
[Extent4].[IsTypeJudge] AS [IsTypeJudge],
[Extent4].[IsTypeCourt] AS [IsTypeCourt],
[Extent4].[IsTypeInsuranceCompany] AS [IsTypeInsuranceCompany],
[Extent4].[Salutation] AS [Salutation],
[Extent4].[Fiduciary] AS [Fiduciary],
[Extent4].[IsMinor] AS [IsMinor],
[Extent4].[OrgID] AS [OrgID1],
[Extent4].[IsSingleName] AS [IsSingleName],
[Extent4].[FirstName] AS [FirstName],
[Extent4].[LastName] AS [LastName],
[Extent4].[PictureKey] AS [PictureKey]
FROM (SELECT [Extent1].[ID] AS [ID1],
[Extent1].[ProjectID] AS [ProjectID],
[Extent1].[AllDay] AS [AllDay],
[Extent1].[Start] AS [Start],
[Extent1].[End] AS [End], [Extent1].[Location] AS [Location], [Extent1].[Notes] AS [Notes],
[Extent1].[Title] AS [Title], [Extent1].[CreatedByID] AS [CreatedByID],
[Extent2].[ID] AS [ID2], [Extent2].[UniqueKey] AS [UniqueKey],
[Extent2].[CustomProjectTypeID] AS [CustomProjectTypeID1],
[Extent2].[PhaseID] AS [PhaseID], [Extent2].[PhaseDate] AS [PhaseDate],
[Extent2].[EstimatedSettlementValue] AS [EstimatedSettlementValue],
[Extent2].[Number] AS [Number], [Extent2].[ClientID] AS [ClientID],
[Extent2].[ProjectName] AS [ProjectName], [Extent2].[ReferrerID] AS [ReferrerID],
[Extent2].[Description] AS [Description], [Extent2].[CreateDate] AS [CreateDate],
[Extent2].[IncidentDate] AS [IncidentDate], [Extent2].[ProjectGroupID] AS [ProjectGroupID],
[Extent2].[OrgID] AS [OrgID1], [Extent2].[LastActivity] AS [LastActivity]
FROM [dbo].[Appointment] AS [Extent1]
INNER JOIN [dbo].[Project] AS [Extent2] ON [Extent1].[ProjectID] = [Extent2].[ID]
INNER JOIN [dbo].[Phase] AS [Extent3] ON [Extent2].[PhaseID] = [Extent3].[ID]
WHERE ([Extent1].[Start] IS NOT NULL) AND ([Extent3].[IsPermanent] <> 1) ) AS [Filter1]
LEFT OUTER JOIN [dbo].[Person] AS [Extent4] ON [Filter1]

hi what do you want to fix

whats wrong with it ??

:slight_smile:

It is taking long time to run. I think something wrong with it. Is there anyway you can modify it?
Thx for your time.

Do you have the proper indices on the columns you are joining on and columns you are filtering on?

Yes, there are indexes

You are doing a sub query (why) and then joining it to

LEFT OUTER JOIN [dbo].[Person] AS [Extent4] ON [Filter1], is that just got cut off when you posted or is there more?

yes it got cut off..It should read as below

LEFT OUTER JOIN [dbo].[Person] AS [Extent4] ON [Filter1].[ClientID]

That still looks something is cut off

LEFT OUTER JOIN [dbo].[Person] AS [Extent4] ON [Filter1].[ClientID]

so you have index for following columns?

Appointment => ProjectID & Start
Project => ID & PhaseID & ClienID
Phase => ID & IsPermanent
Person =>

Please post those indexes here?