Currently, we have a long time running issue for a data warehouse job, so we decided to optimize some stored procedures.
After analyzing the stored procedures. we have some findings regarding the sql optimization.
1. Use the temporary table to minimize disk access.
From the sql tuning view, the main concern for the database applications is to minimize disk access. Try to use the temporary table to limit
multiple database access.
For example, we have multiple places to access the table Gdm.SnapshotConsolidationRegionCorporateDepartment in one stored procedure, so we change that part to use the temporary table below.
Insert into #SnapshotConsolidationRegionCD
Select CorporateDepartmentCode,SourceCode,SnapshotId
From GrReportingStaging.Gdm.SnapshotConsolidationRegionCorporateDepartment
2. Use ‘select’ instead of ‘update’
We know that the update operations are much more resource consuming versus using select, so try to use the select in those scenarios.
For example, we have the case to update the ConsolidationRegionKey to -1 for the Budget before 2010.
Update F
Set F.ConsolidationRegionKey = -1
From #TimeAllocationBudget F
Join GrReporting.dbo.Calendar C on F.CalendarKey=C.CalendarKey
where CalendarYear<=2010
It has better performance if we use select as follows.
Insert into #TimeAllocationBudget(ConsolidationRegionKey,CalendarKey,…)
Select (Case when CalendarYear<=2010 then-1 else AllocationRegion.ConsolidationRegionKey end) as ConsolidationRegionKey,
Calendar.CalendarKey,…
From GrReporting.dbo.AllocationRegion AllocationRegion
Inner join GrReporting.dbo.Calendar Calendar
On AllocationRegion.CalendarKey = Calendar.CalendarKey
Inner join ….
3. Use the index in the temporary table.
Even for the temporary tables, we can also create the index to improve the query performance.
Create unique clustered index IX_Clustered on #TimeAllocationBudget (ReferenceCode)
