Below Stored procedure has 34 left join , 18 subquery which goes dead in production server which have lakhs of records. Any
suggestion / tips / opinion regarding optimization
select BS.BS_WorkID,BS.ProjectCode,BS.CostCode,BS.PatchCode,BS.ExchangeCode,BS.BusinessTypeID,BWT.BusinessWorkType,
BS.SiteAlphaName,BS.JobLocation,BS.ClientID,BS.ClientContactID,BS.CustomerName,BS.ServiceOrderNumber,BS.SOType,
BS.WBSENo,BS.WorkTrackNo,BS.ProjectName,BS.SiteAlphaCodeID, tblSiteAlphaCode.SiteAlphaCode,tblJeopardyStatus.JeopardyStatus, BS.JeopardyStatusDate,
BS.CategoryID,BS.SubProgrammeID,SP.SubProgrammeCode as 'SubProgramme',SP.SubProgrammeDescription as 'SubProgrammeDescription',
BS.BulkBilledCategoryID,BC.BulkBilledCategory,BS.WorkTypeID,WT.BSWorkType as WorkType,BS.JobCategoryID, BS.FieldManagerCivilID,
JC.BSCategory as Category, BS.PurchaseOrderNo,BS.ProgrammeYear,BS.ProjectStatusID,BS.ProjectStatusDate,BS.Escalation,
BS.EscalationDate, BS.JobDescription,BS.JobScope,BS.SpecialRequirements,BS.RecordsUpdated,BS.RecordsUpdatedDate,
BS.ReceivedDate, BS.RFSDate,BS.ActualRFSDate,BS.DesignTeamLeaderID,BS.SchedulerID,BS.BuildManagerID,BS.ProjectManagerID,
BS.FieldManagerID, BS.ContractAdminID,BS.CurrentMilestoneID,BS.CurrentMilestoneDate,
BS.CancellationReason,JS.JobStatus,tblWorks_BS_Design.Designer1AssignedDate as DesignerAssignedDate,
tblBSResources.BSResourceName,tblBusinessUnits.BusinessUnit, JS.JobStatus,tblJobCategories.JobCategory,
(select BSResourceName from tblBSResources where BSResourceID =tblWorks_BS_Design.Designer1ID) as Designer1,
(select BSResourceName from tblBSResources where BSResourceID =tblWorks_BS_Design.Designer2ID) as Designer2,
(select BSResourceName from tblBSResources where BSResourceID =tblWorks_BS_Design.RMALandAccessID) as RMALandAccess,
(select BSResourceName from tblBSResources where BSResourceID =tblWorks_BS_Design.DraftsPersonID) as DraughtPerson,
(select BSResourceName from tblBSResources where BSResourceID = BS.SchedulerID) as SchedulerName,
(select BSResourceName from tblBSResources where BSResourceID = BS.ProjectManagerID) as ProjectManagerName,
(select BSResourceName from tblBSResources where BSResourceID = BS.BuildManagerID) as BuildManagerName,
(select BSResourceName from tblBSResources where BSResourceID = BS.FieldManagerID) as FieldManagerName,
(select BSResourceName from tblBSResources where BSResourceID = BS.ContractAdminID) as ContractAdminName,
(select BSResourceName from tblBSResources where BSResourceID = BS.ClientContactID) as ClientContact,
(select BSResourceName from tblBSResources where BSResourceID = BS.DesignTeamLeaderID) as DesignTeamLeaderName,
(select BSResourceName from tblBSResources where BSResourceID = tblWorks_BS_Resources.SiteInspectorID) as DesnSiteInspector,
(select BSResourceName from tblBSResources where BSResourceID = BS.FieldManagerCivilID) as FieldManagerCivilName,
(select BSResourceName from tblBSResources where BSResourceID = tblWorks_BS_Resources.DesignerIP1ID) as DesignerIP1,
--(select BSResourceName from tblBSResources where BSResourceID = tblWorks_BS_Resources.DesignerIP2ID) as DesignerIP2,
(select BSResourceName from tblBSResources where BSResourceID = tblWorks_BS_Resources.DesignerOP1ID) as DesignerOP1,
(select BSResourceName from tblBSResources where BSResourceID = tblWorks_BS_Resources.DesignerOP2ID) as DesignerOP2,
(select BSResourceName from tblBSResources where BSResourceID = tblWorks_BS_Resources.UpdateRecordsID) as UpdateRecords,
(select BSResourceName from tblBSResources where BSResourceID = BS.ProjectControllerID) as ProjectController,
BS.IToolsPacketID,BS.ProjectMilestoneID,CustomerRequested,SLADate,BS.Comments, null as ReasonforChanges, BS.CreatedBy,
BS.CreatedOn,BS.EditedBy,BS.EditedOn,tblWorks_BS_Design.CabinetID,tblProjectMilestone.ProjectMilestone,BS.DesignNotRequired ,
tblClients.Client, BS.RateID,BS.WorkPacketTextTitle,BS.Project,BS.PhaseCategory,BS.WorkPacketEstCompletion,BS.Type,BS.PhaseID,
/*iTools - Start*/
tblBS_i_ProjectPhase.ProjectPhase Phase, BS.iToolsPriorityID, tblBS_i_WorkPacketPriority.WorkPacketPriority, BS.iToolsStartDate,
BS.iToolsClientID, tblBS_i_Client.Client iToolsClient, BS.iToolsWorkPacketTypeID, tblBS_i_WorkPacketType.WorkPacketType,
BS.iToolsServiceCompanyID, tblBS_i_ServiceCompany.ServiceCompany, BS.iToolsWorkPacketCategoryID,
tblBS_i_WorkPacketCategory.WorkPacketCategory, BS.iToolsBaselineEndDate, BS.iToolsRegionID, tblBS_i_Region.Region,
BS.iToolsEndCustomerID, tblBS_i_EndCustomer.EndCustomer, BS.iToolsWorksTypeID, tblBS_i_WorksType.WorksType,
BS.iToolsDepartmentID, tblBS_i_Department.Department, BS.iToolsReCommitReasonID, tblBS_i_ReCommitReason.ReCommitReason,
BS.iToolsZoneID, tblBS_i_Zone.Zone AS iToolsZone,
/*iTools - End*/
IsFinalisedJob, NULL AS RFBDate, NULL AS ActualRFBDate, NULL AS IsToChangeRFBDateColor, BS.HighProfile,ProjectControllerID,
NULL AS UserID, NULL AS UserFullName, --UJM.UserID,U.UserFullName,
BS.RateClientID,BS.OverHeadID,OH.BSOverHead,BS.SiteContact, BS.RomanCode, BS.ReadytoClaimYN,
BS.DDCDate,BS.ActualDDCDate,BS.UFBStageDetailsID,UFB.BSUFBStageDetails, 0 as IsSafetyAlert,
WP.iToolsPacketName, BSJS.JeopardyStatusReason, JR.JeopardyReason,JR.JeopardyReasonID, JU.UserFullName as 'JeopardyCreatedBy',
BS.ScopeRptHeaderText
FROM tblWorks_BS BS
left join tblBusinessWorkTypes BWT ON BWT.BusinessWorkTypeID =BS.BusinessTypeID
left join tblClients Cl ON Cl.ClientID =BS.ClientID
left join tblSubProgrammes SP ON SP.SubProgrammeID =BS.SubProgrammeID
left join tblBSCategories JC ON JC.BSCategoryID =BS.CategoryID
left join tblBulkBilledCategories BC on BC.BulkBilledCategoryID=BS.BulkBilledCategoryID
left join tblBSWorkTypes WT ON WT.BSWorkTypeID =BS.WorkTypeID
LEFT JOIN tblJobStatus JS on BS.ProjectStatusID = JS.JobStatusID
LEFT JOIN tblWorks_BS_Design ON tblWorks_BS_Design.BS_WorkID = BS.BS_WorkID
LEFT JOIN tblBSResources on BS.ClientContactID = tblBSResources.BSResourceID
LEFT JOIN tblBusinessUnits on BS.ClientID = tblBusinessUnits.BusinessUnitID
LEFT JOIN tblJobCategories on BS.JobCategoryID = tblJobCategories.JobCategoryID
LEFT JOIN tblSiteAlphaCode on BS.SiteAlphaCodeID = tblSiteAlphaCode.SiteAlphaCodeID
LEFT JOIN tblJeopardyStatus on BS.JeopardyStatusID = tblJeopardyStatus.JeopardyStatusID
LEFT JOIN tblWorks_BS_Resources on tblWorks_BS_Resources.BS_WorkID = BS.BS_WorkID
LEFT JOIN tblProjectMilestone on BS.ProjectMilestoneID = tblProjectMilestone.ProjectMilestoneID
LEFT JOIN tblClients on tblClients.ClientID = BS.ClientID
/*iTools - Start*/
LEFT JOIN tblBS_i_ProjectPhase on BS.PhaseID = tblBS_i_ProjectPhase.ProjectPhaseID
LEFT JOIN tblBS_i_WorkPacketPriority on BS.iToolsPriorityID = tblBS_i_WorkPacketPriority.WorkPacketPriorityID
LEFT JOIN tblBS_i_Client on BS.iToolsClientID= tblBS_i_Client.ClientID
LEFT JOIN tblBS_i_WorkPacketType on BS.iToolsWorkPacketTypeID = tblBS_i_WorkPacketType.WorkPacketTypeID
LEFT JOIN tblBS_i_ServiceCompany on BS.iToolsServiceCompanyID = tblBS_i_ServiceCompany.ServiceCompanyID
LEFT JOIN tblBS_i_WorkPacketCategory on BS.iToolsWorkPacketCategoryID = tblBS_i_WorkPacketCategory.WorkPacketCategoryID
LEFT JOIN tblBS_i_Region on BS.iToolsRegionID = tblBS_i_Region.RegionID
LEFT JOIN tblBS_i_EndCustomer on BS.iToolsEndCustomerID = tblBS_i_EndCustomer.EndCustomerID
LEFT JOIN tblBS_i_WorksType on BS.iToolsWorksTypeID = tblBS_i_WorksType.WorksTypeID
LEFT JOIN tblBS_i_Department on BS.iToolsDepartmentID = tblBS_i_Department.DepartmentID
LEFT JOIN tblBS_i_ReCommitReason on BS.iToolsReCommitReasonID = tblBS_i_ReCommitReason.ReCommitReasonID
LEFT JOIN tblBS_i_Zone ON tblBS_i_Zone.ZoneID = BS.iToolsZoneID
/*iTools - End*/
--LEFT JOIN tblUserJobMapping UJM ON UJM.WorkID = BS.BS_WorkID AND UJM.WorkTypeID = 2
--LEFT JOIN tblUsers U ON U.UserID = UJM.UserID
LEFT JOIN tblBSOverHead OH ON BS.OverHeadID= OH.BSOverHeadID
LEFT JOIN tblBSUFBStageDetails UFB ON UFB.BSUFBStageDetailsID = BS.UFBStageDetailsID
LEFT JOIN tbliToolsWorkPacketDetails WP ON WP.iToolsPacketID = BS.IToolsPacketID
LEFT JOIN tblWorks_BS_JeopardyStatus BSJS ON BSJS.BS_JeopardyStatusID = BS.BS_JeopardyStatusID
LEFT JOIN tblBSJeopardyReason JR ON JR.JeopardyReasonID = BSJS.JeopardyReasonID
LEFT JOIN tblUsers JU ON JU.UserName = BSJS.CreatedBy