I have some performance issue with my T-SQL Query attached , here are the details below.
1. I am processing 1000 records in a batch first by insert in to temporary table and then eventually to the fact table.
2. Problem is with the query of the temporary table.
3. When I check the execution plan , sort is taking longer time , is it because of the use of Group By and Order By statement in the select ? How to Optimize this ?
4. Any missing Indexes . Please advice ?
Please help me to optimize the performance of this query and also advice me on missing indexes.
Here are the indexes on the various tables used in the query (excluding the dimension tables) as the dimension tables are not large size, hence I have not listed the indexes of the dimension tables.
create clustered index idx1 on #FACT_Labor_Demand_Ideal ([calendar_day],[business_unit_id],[labor_workgroup_id],[labor_role_id],[time_quarter_hour_id],daypart_id,comparable_flag_id,day_comparable_flag_id,business_unit_status_id,[datasource_id],[labor_schedule_version_id] )
create clustered index idx1 on #FACT_Labor_Schedule_Version ([calendar_day],[business_unit_id],[labor_workgroup_id],[labor_role_id],[time_quarter_hour_id],daypart_id,labor_schedule_version_id,comparable_flag_id,business_unit_status_id,[datasource_id] )
Will appreciate your quick reply and your help and support in this regard.