Need High performance query

Question

Please help in tuning this below query for more high performance. Make index suggestions.
Now Execution time is 5min.

SELECT DISTINCT top 10000 EG.Id AS ExposureGroupId, EG.Description, EG.IHUse, EGJ.HEGId, EG.Code AS ExGrCode, E.EmployeeNumber, E.FullName, E.FirstName, E.LastName, E.DateOfBirth
FROM dbo.LU_GLB_ExposureGroup AS EG
INNER JOIN dbo.DT_ExposureGroupJob AS EGJ ON EGJ.HEGId = EG.Id
CROSS JOIN DT_Employee E
WHERE (EGJ.GeographicLocationId = E.GeographicLocationId OR EGJ.GeographicLocationId = - 2 OR
(SELECT GeographicLocationLineage FROM dbo.LU_GLB_TreeMasterCode AS EGL WHERE (Id = E.GeographicLocationId)) LIKE
(SELECT GeographicLocationLineage FROM dbo.LU_GLB_TreeMasterCode AS EGJGL WHERE (Id = EGJ.GeographicLocationId)))
AND
(EGJ.DepartmentId = E.DepartmentId OR EGJ.DepartmentId = - 3 OR
(SELECT DepartmentLineage FROM dbo.LU_GLB_TreeMasterCode AS EDP WHERE (Id = E.DepartmentId)) LIKE
(SELECT DepartmentLineage FROM dbo.LU_GLB_TreeMasterCode AS EGJDP WHERE (Id = EGJ.DepartmentId)))
AND
(EGJ.DivisionId = E.DivisionId OR EGJ.DivisionId = - 4 OR
(SELECT DivisionLineage FROM dbo.LU_GLB_TreeMasterCode AS EDV WHERE (Id = E.DivisionId)) LIKE
(SELECT DivisionLineage FROM dbo.LU_GLB_TreeMasterCode AS EGJDV WHERE (Id = EGJ.DivisionId)))
AND
(EGJ.LocationId = E.LocationId OR EGJ.LocationId = - 5 OR
(SELECT LocationLineage FROM dbo.LU_GLB_TreeMasterCode AS ELC WHERE (Id = E.LocationId)) LIKE
(SELECT LocationLineage FROM dbo.LU_GLB_TreeMasterCode AS EGJLC WHERE (Id = EGJ.LocationId)))
AND
(EGJ.OrganizationId = E.OrganizationId OR EGJ.OrganizationId = - 6 OR
(SELECT OrganizationLineage from dbo.LU_GLB_TreeMasterCode AS EOG WHERE (Id = E.OrganizationId)) LIKE
(SELECT OrganizationLineage FROM dbo.LU_GLB_TreeMasterCode AS EGJOG WHERE (Id = EGJ.OrganizationId)))
AND
(EGJ.JobPositionId = E.JobPositionId OR EGJ.JobPositionId = - 1)
AND
(EGJ.FloorAreaId = E.FloorAreaId OR EGJ.FloorAreaId = - 8 OR
(SELECT FloorAreaLineage FROM dbo.LU_GLB_TreeMasterCode AS EFA WHERE (Id = E.FloorAreaId)) LIKE
(SELECT FloorAreaLineage FROM dbo.LU_GLB_TreeMasterCode AS EGJFA WHERE (Id = EGJ.FloorAreaId)))
AND
(EGJ.BuildingId = E.BuildingId OR EGJ.BuildingId = - 9 OR
(SELECT BuildingLineage FROM dbo.LU_GLB_TreeMasterCode AS EBD WHERE(Id = E.BuildingId)) LIKE
(SELECT BuildingLineage FROM dbo.LU_GLB_TreeMasterCode AS EGJBD WHERE (Id = EGJ.BuildingId)))
AND
(E.Id NOT IN (SELECT E2.Id FROM dbo.DT_ExposureGroupEmployee AS EGE
INNER JOIN dbo.DT_Employee AS E2 ON EGE.EmployeeId = E2.Id
WHERE
(EGE.ExposureGroupId = EGJ.HEGId) AND
(EGE.Excluded = 1 OR EGE.Excluded = - 1) AND
(EGE.StartDate <= CAST(GETDATE() AS date)) AND
(EGE.EndDate IS NULL OR EGE.EndDate > CAST(GETDATE() AS date))))

Answer 1

The first thing I would do with this query so it is reformat it to be more readable. Although, that is true, what is one programmer's well-written code is another programmer's unreadable junk. Nevertheless, the way the query is written now, I find it difficult to follow.

Anyway, when browsing through the query I see several constructs that are potentially problematic. OR often leads the optimizer astray. And then these LIKE conditions...

Before starting to performance-tuning a query there is a one precondition that some people miss, to wit: does the query produce the correct result? It has happened to me more than once that I've found that when a query was slow, it was because I made some silly mistake.

But one recommendation is to break up the complex query in several, using intermediate temp tables. This does not always work out to the better, but it can make things run faster it you index the temp table appropriately. If nothing else, it makes debugging easier.