Solved

PERFORMANCE OF SQL QUERY

Posted on 2016-09-20
13
59 Views
Last Modified: 2016-10-07
Hi Experts,

  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.

1.#FACT_Labor_Demand_Ideal

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] )

2.#FACT_Labor_Schedule_Version

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.

Thanks,
SRK.
0
Comment
Question by:n_srikanth4
  • 7
  • 5
13 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41806365
 I have some performance issue with my T-SQL Query attached
No attachments found.

Please help me to optimize the performance of this query and also advice me on missing indexes.
We'll need the Query Execution plan of the query so we can analyze it and give you the best advice possible.
0
 

Author Comment

by:n_srikanth4
ID: 41806501
Hi Vitor,

    Please find the attached SQL Query and will provide the execution plan in a short while .

Thanks,
SRK.
0
 

Author Comment

by:n_srikanth4
ID: 41806503
Sorry , missed as the format .sql is not accepted. Please find sql.txt file for the attachment.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41806506
Still no attachments. Also, forget the SQL query. If you can please post the Query Execution Plan instead.
0
 

Author Comment

by:n_srikanth4
ID: 41806510
Sorry , I am unsure if you are allowed to open the attachment . here is the query below :

insert into #FACT_Labor_Schedule_Version_Accuracy (datasource_id
                                                                        

,business_unit_id
                                                                        

,calendar_day
                                                                        

,time_quarter_hour_id
                                                                        

,labor_workgroup_id
                                                                        

,labor_role_id
                                                                        

,daypart_id
                                                                        

,labor_schedule_version_id
                                                                        

,labor_demand_acc_hours_qty
                                                                        

,labor_demand_acc_adj_hours_qty
                                                                        

,labor_sched_acc_hours_qty
                                                                        

,ver_covered_time
                                                                        

,ver_covered_time_adj
                                                                        

,ver_calc_sched_accuracy_hours_qty
                                                                        

,ver_adj_sched_accuracy_hours_qty
                                                                        

,ver_calc_sched_accuracy_id
                                                                        

,ver_adj_sched_accuracy_id
                                                                        

,business_unit_status_id
                                                                        

,comparable_flag_id
                                                                        

,day_comparable_flag_id)
                                                            select

datasource_id,

business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypar

t_id,labor_schedule_version_id
                                                                        

, max(labor_demand_acc_hours_qty) as labor_demand_acc_hours_qty
                                                                        

, max(labor_demand_acc_adj_hours_qty) as labor_demand_acc_adj_hours_qty
                                                                        

, max(labor_sched_hours_qty) as labor_sched_acc_hours_qty
                                                                        

, max(ver_covered_time) as ver_covered_time
                                                                        

, max(ver_covered_time_adj) as ver_covered_time_adj
                                                                        

, max(ver_calc_sched_accuracy_hours_qty) as ver_calc_sched_accuracy_hours_qty
                                                                        

, max(ver_adj_sched_accuracy_hours_qty) as ver_adj_sched_accuracy_hours_qty
                                                                        

,coalesce(t.sched_accuracy_id,0) as ver_calc_sched_accuracy_id  
                                                                        

,coalesce(ta.sched_accuracy_id,0) as ver_adj_sched_accuracy_id  
                                                                        

,business_unit_status_id,comparable_flag_id,day_comparable_flag_id
                                                            from
                                                            (
                                                                  

select
                                                                  

demand.datasource_id,demand.business_unit_id,demand.calendar_day,demand.time_quarter_hour_

id,demand.labor_workgroup_id,demand.labor_role_id,demand.daypart_id,demand.labor_schedule_

version_id
                                                                        

,coalesce(demand_calc_fte_qty,0)/4 as labor_demand_acc_hours_qty
                                                                        

,coalesce(demand_adj_fte_qty,0)/4 as labor_demand_acc_adj_hours_qty
                                                                        

,coalesce(labor_sched_hours_qty,0) as labor_sched_hours_qty
                                                                        

,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_calc_fte_qty,0)/4) then

coalesce(labor_sched_hours_qty,0) else (coalesce(demand_calc_fte_qty,0)/4) end as

ver_covered_time
                                                                        

,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_adj_fte_qty,0)/4) then

coalesce(labor_sched_hours_qty,0) else (coalesce(demand_adj_fte_qty,0)/4) end as

ver_covered_time_adj
                                                                        

,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_calc_fte_qty,0)/4) as

[ver_calc_sched_accuracy_hours_qty]  
                                                                        

,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_adj_fte_qty,0)/4) as

[ver_adj_sched_accuracy_hours_qty]  
                                                                        

,demand.business_unit_status_id,demand.comparable_flag_id,demand.day_comparable_flag_id
                                                                  

from #FACT_Labor_Demand_Ideal demand
                                                                  

left outer join  #FACT_Labor_Schedule_Version sched  
                                                                  on

demand.business_unit_id = sched.business_unit_id  
                                                                  

and demand.calendar_day = sched.calendar_day  
                                                                  

and demand.time_quarter_hour_id  = sched.time_quarter_hour_id    
                                                                  

and demand.labor_workgroup_id  = sched.labor_workgroup_id  
                                                                  

and demand.labor_role_id  = sched.labor_role_id  
                                                                  

and demand.datasource_id = sched.datasource_id
                                                                  

and demand.labor_schedule_version_id = sched.labor_schedule_version_id
                                                                  

                                                                  

UNION ALL

                                                                  

select
                                                                  

sched.datasource_id,sched.business_unit_id,sched.calendar_day,sched.time_quarter_hour_id,s

ched.labor_workgroup_id,sched.labor_role_id,sched.daypart_id,sched.labor_schedule_version_

id
                                                                        

,coalesce(demand_calc_fte_qty,0)/4 as labor_demand_acc_hours_qty
                                                                        

,coalesce(demand_adj_fte_qty,0)/4 as labor_demand_acc_adj_hours_qty
                                                                        

,coalesce(labor_sched_hours_qty,0) as labor_sched_hours_qty
                                                                        

,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_calc_fte_qty,0)/4) then

coalesce(labor_sched_hours_qty,0) else (coalesce(demand_calc_fte_qty,0)/4) end as

ver_covered_time
                                                                        

,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_adj_fte_qty,0)/4) then

coalesce(labor_sched_hours_qty,0) else (coalesce(demand_adj_fte_qty,0)/4) end as

ver_covered_time_adj
                                                                        

,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_calc_fte_qty,0)/4) as

[ver_calc_sched_accuracy_hours_qty]  
                                                                        

,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_adj_fte_qty,0)/4) as

[ver_adj_sched_accuracy_hours_qty]  
                                                                        

,demand.business_unit_status_id,demand.comparable_flag_id,demand.day_comparable_flag_id
                                                                  

from #FACT_Labor_Schedule_Version sched  
                                                                  

left outer join   #FACT_Labor_Demand_Ideal demand
                                                                  on

demand.business_unit_id = sched.business_unit_id  
                                                                  

and demand.calendar_day = sched.calendar_day  
                                                                  

and demand.time_quarter_hour_id  = sched.time_quarter_hour_id    
                                                                  

and demand.labor_workgroup_id  = sched.labor_workgroup_id  
                                                                  

and demand.labor_role_id  = sched.labor_role_id  
                                                                  

and demand.datasource_id = sched.datasource_id
                                                            ) u
                                                            left outer

join dim_labor_over_under t  
                                                            on

[ver_calc_sched_accuracy_hours_qty]  = t.value  
                                                            left outer

join dim_labor_over_under ta  
                                                            on

[ver_adj_sched_accuracy_hours_qty]  = ta.value  
                                                            group by

datasource_id,

business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypar

t_id,labor_schedule_version_id
                                                            ,coalesce

(t.sched_accuracy_id,0),coalesce(ta.sched_accuracy_id,0)
                                                            

,business_unit_status_id,comparable_flag_id,day_comparable_flag_id
                                                            order by

calendar_day,business_unit_id,labor_workgroup_id,labor_role_id,labor_schedule_version_id,t

ime_quarter_hour_id, datasource_id
0
 

Author Comment

by:n_srikanth4
ID: 41806578
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:n_srikanth4
ID: 41806579
Also is the attached stored procedure (complete code) for which the execution plan is captured.
Version-Accuracy.txt
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41806699
I don't even know where to begin.

First of all you have hundreds of query executions and some of them dealing with billions of records. It's an huge amount of records to be handled that many times so I wouldn't expect a miracle here. How long is all this process taking to run successfully?

Now, trying to give some tips following the code.
  1. Do not create the indexes in an empty table before a massive insert. Let the insert run and then create the index. It will save the insert time.
  2. First insert into #bulist doesn't need to check for the records existence since the table is empty at this stage (where not exists (select 1 from #bulist bl...).
  3. Now you can run "create index idx1 on #bulist (bu_id,business_date,datasource_id)"
  4. In inserts from a select you don't need the rows to be ordered especially if the target table has a clustered PK. Sort operations are really heavy so get rid of them.
  5. Do not use DISTINCT. Instead use GROUP BY. It will give the same result but with better performance.

Hope this helps to boost a little bit the performance.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 41807025
Here's my tips for the code:

A) definitely create the clustered index on the temp tables prior to loading, as you've shown, but include only the columns used to JOIN the table in the clus index.  [Don't create any nonclus index(es) on the temp tables.]  The idea is to, hopefully, get a MERGE join of the two tables rather than a HASH join or, ouch!, a LOOP join, and to avoid later sort(s).  Also, explicitly specify a fillfactor of 100 to reduce the number of pages in the temp table as much as possible.  Finally, you'll want to put the clus keys in the same order as the GROUP BY in the insert to #FACT_Labor_Schedule_Version_Accuracy.

Therefore, the temp table index definitions should be:

create clustered index idx1 on #FACT_Labor_Demand_Ideal ( datasource_id, business_unit_id, calendar_day, time_quarter_hour_id, labor_workgroup_id, labor_role_id, daypart_id, labor_schedule_version_id ) with ( fillfactor = 100 );

create clustered index idx1 on #FACT_Labor_Schedule_Version ( datasource_id, business_unit_id, calendar_day, time_quarter_hour_id, labor_workgroup_id, labor_role_id, daypart_id, labor_schedule_version_id ) with ( fillfactor = 100 );

B) The ORDER BY in the INSERT to #FACT_Labor_Schedule_Version_Accuracy should be removed.  But do define prior to loading the table a clustered index on the #FACT_Labor_Schedule_Version_Accuracy table on the same columns: datasource_id, business_unit_id, calendar_day, time_quarter_hour_id, labor_workgroup_id, labor_role_id, daypart_id, labor_schedule_version_id.  Since the data to be INSERTed is already GROUPed BY those columns, the sort is already done or very close to it.
0
 

Author Comment

by:n_srikanth4
ID: 41807989
Thanks Vitor for the immediate reply , Could you please send me the Code snippets you have recommended above  , so that  we are on the same page and I can implement them .   Will appreciate your help in this regard.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41808234
Not really my style to do all the coding for you but here you go:
/****** Object:  StoredProcedure [dbo].[ds_load_FACT_Labor_Schedule_Version_Accuracy]    Script Date: 9/12/2016 11:19:59 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[ds_load_FACT_Labor_Schedule_Version_Accuracy] @datasource_id int = -99,@Process_Start_Log_ID int = 0 as

set nocount on
-- declare @datasource_id int,@Process_Start_Log_ID int select @datasource_id = -99,@Process_Start_Log_ID = 0
declare	@Process_Log_ID	int
	,@sql nvarchar(3999) 
	,@calendar_day int
	,@iteration_count int
	

insert WH_Process_Log( process_start_log_id, event_description, start_datetime)
select @Process_Start_Log_ID, OBJECT_NAME(@@PROCID)+' @datasource_id = '+cast(@datasource_id as nvarchar(30)), getdate() 

set @Process_Log_ID = @@IDENTITY

declare	@InsertCount	int,
		@UpdateCount	int

set @InsertCount = 0
set @UpdateCount = 0

if object_id('tempdb..#bulist') is not null
	drop table #bulist
if object_id('tempdb..#bulist_processing') is not null
	drop table #bulist_processing
if object_id('tempdb..#FACT_Labor_Schedule_Version_Accuracy') is not null
	drop table #FACT_Labor_Schedule_Version_Accuracy
if object_id('tempdb..#FACT_Labor_Schedule_Version') is not null
	drop table #FACT_Labor_Schedule_Version
if object_id('tempdb..#FACT_Labor_Demand_Ideal') is not null
	drop table #FACT_Labor_Demand_Ideal
	

create table	#bulist (
	datasource_id int not null,
	bu_id			int,
	business_date   smalldatetime,
	processed_flag	nchar( 1)	not null	default 'N',
	business_unit_id int null,
	primary key clustered (business_date,bu_id,datasource_id))
	
--create index idx1 on #bulist (bu_id,business_date,datasource_id) #Remove from here
	
create table	#bulist_processing (
	datasource_id int not null,
	bu_id			int,
	business_date   smalldatetime,
	business_unit_id int null,
	primary key clustered (business_date,bu_id,datasource_id))

CREATE TABLE #FACT_Labor_Schedule_Version_Accuracy (
	[datasource_id] [int] NOT NULL,
	[business_unit_id] [int] NOT NULL,
	[calendar_day] [smalldatetime] NOT NULL,
	[time_quarter_hour_id] [int] NOT NULL,
	[labor_workgroup_id] [int] NOT NULL,
	[labor_role_id] [int] NOT NULL,
	[daypart_id] [int] NULL,
	[labor_schedule_version_id] [int] NOT NULL,
	[labor_demand_acc_hours_qty] [numeric](38, 6) NULL,
	[labor_demand_acc_adj_hours_qty] [numeric](38, 6) NULL,
	[labor_sched_acc_hours_qty] [numeric](38, 6) NULL,
	[ver_covered_time] [numeric](38, 6) NULL,
	[ver_covered_time_adj] [numeric](38, 6) NULL,
	[ver_calc_sched_accuracy_hours_qty] [numeric](38, 6) NULL,
	[ver_adj_sched_accuracy_hours_qty] [numeric](38, 6) NULL,
	[ver_calc_sched_accuracy_id] [int] NULL,
	[ver_adj_sched_accuracy_id] [int] NULL,
	[business_unit_status_id] [int] NULL,
	[comparable_flag_id] [int] NULL,
	[day_comparable_flag_id] [int] NULL,
	primary key clustered ([calendar_day],[business_unit_id],[labor_workgroup_id],[labor_role_id],[labor_schedule_version_id],[time_quarter_hour_id],[datasource_id]) )

CREATE TABLE #FACT_Labor_Demand_Ideal (
	[datasource_id] [int] NOT NULL,
	[business_unit_id] [int] NOT NULL,
	[calendar_day] [smalldatetime] NOT NULL,
	[time_quarter_hour_id] [int] NOT NULL,
	[labor_workgroup_id] [int] NOT NULL,
	[labor_role_id] [int] NOT NULL,
	[business_unit_status_id] [int] NULL,
	[comparable_flag_id] [int] NULL,
	[day_comparable_flag_id] [int] NULL,
	[daypart_id] [int] NOT NULL,
	[demand_calc_fte_qty] [numeric](38, 6) NULL,
	[demand_adj_fte_qty] [numeric](38, 6) NULL,
	[labor_schedule_version_id] [int] NOT NULL
)
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 TABLE #FACT_Labor_Schedule_Version (
	[datasource_id] [int] NOT NULL,
	[business_unit_id] [int] NOT NULL,
	[calendar_day] [smalldatetime] NOT NULL,
	[time_quarter_hour_id] [int] NOT NULL,
	[labor_workgroup_id] [int] NOT NULL,
	[labor_role_id] [int] NOT NULL,
	[daypart_id] [int] NULL,
	[labor_schedule_version_id] [int] NOT NULL,
	[business_unit_status_id] [int] NULL,
	[comparable_flag_id] [int] NULL,
	[day_comparable_flag_id] [int] NULL,
	[labor_sched_hours_qty] [numeric](38, 6) NULL )

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] )
		
insert into #bulist (datasource_id,bu_id,business_date,business_unit_id)  
select a.datasource_id,a.bu_id,a.business_date,b.org_hierarchy_id
from f_gen_labor_requirements_min_bu_day_load a
	join dim_org_hierarchy b with (nolock) on a.bu_id = b.org_hierarchy_id
											and a.datasource_id = b.datasource_id
											and a.datasource_id = case when @datasource_id < 1 then a.datasource_id else @datasource_id end
	join dim_time t with (nolock) on a.business_date = t.calendar_day
/*where not exists (select 1 from #bulist bl
				where a.bu_id = bl.bu_id
				and a.business_date = bl.business_date
				and a.datasource_id = bl.datasource_id)*/ -- #No need for this clause since table is empty at this point
group by a.datasource_id,a.bu_id,a.business_date,b.org_hierarchy_id

create index idx1 on #bulist (bu_id,business_date,datasource_id) -- #Now is good to create the index
		
insert into #bulist (datasource_id,bu_id,business_date,business_unit_id)  
select a.datasource_id,a.bu_id,a.business_date,b.org_hierarchy_id
from f_lab_rpt_sched_hours_load a 
	join dim_org_hierarchy b with (nolock) on a.bu_id = b.org_hierarchy_id
										and a.datasource_id = b.datasource_id
										and a.datasource_id = case when @datasource_id < 1 then a.datasource_id else @datasource_id end
	join dim_time t with (nolock) on a.business_date = t.calendar_day
where not exists (select 1 
				from #bulist bl
				where a.bu_id = bl.bu_id
				and a.business_date = bl.business_date
				and a.datasource_id = bl.datasource_id)
group by a.datasource_id,a.bu_id,a.business_date,b.org_hierarchy_id


while exists( select * from #bulist where processed_flag = 'N')
	begin
		truncate table #bulist_processing
		truncate table #FACT_Labor_Schedule_Version_Accuracy
		truncate table #FACT_Labor_Demand_Ideal
		truncate table #FACT_Labor_Schedule_Version
  
		select @iteration_count = coalesce(fact_labor_iteration_bu_count,200)
		from wh_ssis_global_settings_retail
			
		select @sql = 'insert into #bulist_processing (datasource_id,bu_id,business_date,business_unit_id)  
					select top '+cast(@iteration_count as nvarchar(30))+' datasource_id,bu_id,business_date,business_unit_id
					from #bulist  
					where processed_flag = ''N'''  
					--order by datasource_id asc,business_date desc, bu_id asc' #No need for ORDER BY clause in Insert
		 exec sp_executesql @sql
		  
		 
		insert into #FACT_Labor_Schedule_Version (datasource_id, business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,labor_schedule_version_id,business_unit_status_id,day_comparable_flag_id,labor_sched_hours_qty)			
		select a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,a.labor_schedule_version_id,null as business_unit_status_id,null as comparable_flag_id,sum(labor_sched_hours_qty) as labor_sched_hours_qty  
		from FACT_Labor_Schedule_Version a
			join #bulist_processing b on a.calendar_day = b.business_date
									and a.business_unit_id = b.bu_id
									and a.datasource_id = b.datasource_id
		group by a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id ,daypart_id,a.labor_schedule_version_id	

--changed the order of processing so that we only generate demand versions where we have schedule.  need to include auto version whenever we have demand.
		insert into #FACT_Labor_Demand_Ideal (datasource_id, business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id,labor_schedule_version_id, demand_calc_fte_qty, demand_adj_fte_qty)
		select a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id,1000001 as labor_schedule_version_id, sum(demand_calc_fte_qty) as demand_calc_fte_qty, sum(demand_adj_fte_qty) as demand_adj_fte_qty
		from fact_labor_demand_ideal a
			join #bulist_processing b on a.calendar_day = b.business_date
									and a.business_unit_id = b.bu_id
									and a.datasource_id = b.datasource_id
		group by a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id


		insert into #FACT_Labor_Demand_Ideal (datasource_id, business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id,dlsv.labor_schedule_version_id, demand_calc_fte_qty, demand_adj_fte_qty)
		select a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id,dlsv.labor_schedule_version_id, sum(demand_calc_fte_qty) as demand_calc_fte_qty, sum(demand_adj_fte_qty) as demand_adj_fte_qty
		from fact_labor_demand_ideal a
			join #bulist_processing b on a.calendar_day = b.business_date
									and a.business_unit_id = b.bu_id
									and a.datasource_id = b.datasource_id
			join DIM_Labor_Schedule_Version_Code dlsv on dlsv.labor_schedule_version_id <> 0
			join (select datasource_id,business_unit_id,calendar_day,labor_schedule_version_id -- # DISTINCT removed
					from #FACT_Labor_Schedule_Version 
					where labor_schedule_version_id>1000001
					group by datasource_id,business_unit_id,calendar_day,labor_schedule_version_id -- # GROUP BY added
					) l on a.datasource_id=l.datasource_id 
						and a.business_unit_id=l.business_unit_id 
						and a.calendar_day=l.calendar_day 
						and dlsv.labor_schedule_version_id=l.labor_schedule_version_id
		group by a.datasource_id, a.business_unit_id,a.calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,business_unit_status_id,comparable_flag_id,day_comparable_flag_id,dlsv.labor_schedule_version_id
												
--select * from #bulist_processing

		insert into #FACT_Labor_Schedule_Version_Accuracy (datasource_id
												,business_unit_id
												,calendar_day
												,time_quarter_hour_id
												,labor_workgroup_id
												,labor_role_id
												,daypart_id
												,labor_schedule_version_id
												,labor_demand_acc_hours_qty
												,labor_demand_acc_adj_hours_qty
												,labor_sched_acc_hours_qty
												,ver_covered_time
												,ver_covered_time_adj
												,ver_calc_sched_accuracy_hours_qty
												,ver_adj_sched_accuracy_hours_qty
												,ver_calc_sched_accuracy_id
												,ver_adj_sched_accuracy_id
												,business_unit_status_id
												,comparable_flag_id
												,day_comparable_flag_id)
		select datasource_id, business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,labor_schedule_version_id
												, max(labor_demand_acc_hours_qty) as labor_demand_acc_hours_qty
												, max(labor_demand_acc_adj_hours_qty) as labor_demand_acc_adj_hours_qty
												, max(labor_sched_hours_qty) as labor_sched_acc_hours_qty
												, max(ver_covered_time) as ver_covered_time
												, max(ver_covered_time_adj) as ver_covered_time_adj
												, max(ver_calc_sched_accuracy_hours_qty) as ver_calc_sched_accuracy_hours_qty
												, max(ver_adj_sched_accuracy_hours_qty) as ver_adj_sched_accuracy_hours_qty
												,coalesce(t.sched_accuracy_id,0) as ver_calc_sched_accuracy_id  
												,coalesce(ta.sched_accuracy_id,0) as ver_adj_sched_accuracy_id  
												,business_unit_status_id,comparable_flag_id,day_comparable_flag_id
		from (select demand.datasource_id,demand.business_unit_id,demand.calendar_day,demand.time_quarter_hour_id,demand.labor_workgroup_id,demand.labor_role_id,demand.daypart_id,demand.labor_schedule_version_id
												,coalesce(demand_calc_fte_qty,0)/4 as labor_demand_acc_hours_qty
												,coalesce(demand_adj_fte_qty,0)/4 as labor_demand_acc_adj_hours_qty
												,coalesce(labor_sched_hours_qty,0) as labor_sched_hours_qty 
												,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_calc_fte_qty,0)/4) then coalesce(labor_sched_hours_qty,0) else (coalesce(demand_calc_fte_qty,0)/4) end as ver_covered_time
												,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_adj_fte_qty,0)/4) then coalesce(labor_sched_hours_qty,0) else (coalesce(demand_adj_fte_qty,0)/4) end as ver_covered_time_adj
												,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_calc_fte_qty,0)/4) as [ver_calc_sched_accuracy_hours_qty]   
												,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_adj_fte_qty,0)/4) as [ver_adj_sched_accuracy_hours_qty]  
												,demand.business_unit_status_id,demand.comparable_flag_id,demand.day_comparable_flag_id
				from #FACT_Labor_Demand_Ideal demand
					left outer join  #FACT_Labor_Schedule_Version sched on demand.business_unit_id = sched.business_unit_id  
																	and demand.calendar_day = sched.calendar_day  
																	and demand.time_quarter_hour_id  = sched.time_quarter_hour_id    
																	and demand.labor_workgroup_id  = sched.labor_workgroup_id  
																	and demand.labor_role_id  = sched.labor_role_id  
																	and demand.datasource_id = sched.datasource_id
																	and demand.labor_schedule_version_id = sched.labor_schedule_version_id
											
				UNION ALL
				select sched.datasource_id,sched.business_unit_id,sched.calendar_day,sched.time_quarter_hour_id,sched.labor_workgroup_id,sched.labor_role_id,sched.daypart_id,sched.labor_schedule_version_id
														,coalesce(demand_calc_fte_qty,0)/4 as labor_demand_acc_hours_qty
														,coalesce(demand_adj_fte_qty,0)/4 as labor_demand_acc_adj_hours_qty
														,coalesce(labor_sched_hours_qty,0) as labor_sched_hours_qty 
														,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_calc_fte_qty,0)/4) then coalesce(labor_sched_hours_qty,0) else (coalesce(demand_calc_fte_qty,0)/4) end as ver_covered_time
														,case when coalesce(labor_sched_hours_qty,0)< (coalesce(demand_adj_fte_qty,0)/4) then coalesce(labor_sched_hours_qty,0) else (coalesce(demand_adj_fte_qty,0)/4) end as ver_covered_time_adj
														,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_calc_fte_qty,0)/4) as [ver_calc_sched_accuracy_hours_qty]   
														,coalesce(labor_sched_hours_qty,0) - (coalesce(demand_adj_fte_qty,0)/4) as [ver_adj_sched_accuracy_hours_qty]  
														,demand.business_unit_status_id,demand.comparable_flag_id,demand.day_comparable_flag_id
				from #FACT_Labor_Schedule_Version sched   
					left outer join #FACT_Labor_Demand_Ideal demand on demand.business_unit_id = sched.business_unit_id  
																	and demand.calendar_day = sched.calendar_day  
																	and demand.time_quarter_hour_id  = sched.time_quarter_hour_id    
																	and demand.labor_workgroup_id  = sched.labor_workgroup_id  
																	and demand.labor_role_id  = sched.labor_role_id  
																	and demand.datasource_id = sched.datasource_id) u
					left outer join dim_labor_over_under t on [ver_calc_sched_accuracy_hours_qty]  = t.value  
					left outer join dim_labor_over_under ta on [ver_adj_sched_accuracy_hours_qty]  = ta.value  
				group by datasource_id, business_unit_id,calendar_day,time_quarter_hour_id,labor_workgroup_id,labor_role_id,daypart_id,labor_schedule_version_id
										,coalesce(t.sched_accuracy_id,0),coalesce(ta.sched_accuracy_id,0)
										,business_unit_status_id,comparable_flag_id,day_comparable_flag_id
				-- order by calendar_day,business_unit_id,labor_workgroup_id,labor_role_id,labor_schedule_version_id,time_quarter_hour_id, datasource_id # No need for ORDER BY clause in Insert



		delete FACT_Labor_Schedule_Version_Accuracy
		from FACT_Labor_Schedule_Version_Accuracy a 
			join #bulist_processing bup on a.datasource_id = bup.datasource_id
										and a.calendar_day = bup.business_date
										and a.business_unit_id = bup.bu_id
		where not exists (select * 
						from #FACT_Labor_Schedule_Version_Accuracy b
						where a.datasource_id = b.datasource_id
						and a.business_unit_id=b.business_unit_id
						and a.calendar_day=b.calendar_day
						and a.time_quarter_hour_id=b.time_quarter_hour_id
						and a.labor_workgroup_id=b.labor_workgroup_id
						and a.labor_role_id=b.labor_role_id
						and coalesce(a.daypart_id,0) = coalesce(b.daypart_id,0)
						and a.labor_schedule_version_id = b.labor_schedule_version_id
						and coalesce(a.labor_demand_acc_hours_qty,-9999)=coalesce(b.labor_demand_acc_hours_qty,-9999)
						and coalesce(a.labor_demand_acc_adj_hours_qty,-9999)=coalesce(b.labor_demand_acc_adj_hours_qty,-9999)
						and coalesce(a.labor_sched_acc_hours_qty,-9999)=coalesce(b.labor_sched_acc_hours_qty,-9999)
						and coalesce(a.ver_covered_time,-9999)=coalesce(b.ver_covered_time,-9999)
						and coalesce(a.ver_covered_time_adj,-9999)=coalesce(b.ver_covered_time_adj,-9999)
						and coalesce(a.ver_calc_sched_accuracy_hours_qty,-9999)=coalesce(b.ver_calc_sched_accuracy_hours_qty,-9999)
						and coalesce(a.ver_adj_sched_accuracy_hours_qty,-9999)=coalesce(b.ver_adj_sched_accuracy_hours_qty,-9999)
						and a.ver_calc_sched_accuracy_id=b.ver_calc_sched_accuracy_id
						and a.ver_adj_sched_accuracy_id=b.ver_adj_sched_accuracy_id
						and coalesce(a.business_unit_status_id,0)=coalesce(b.business_unit_status_id,0)
						and coalesce(a.comparable_flag_id,0)=coalesce(b.comparable_flag_id,0)
						and coalesce(a.day_comparable_flag_id,0) = coalesce(b.day_comparable_flag_id,0))
			
		set @UpdateCount = @UpdateCount + @@ROWCOUNT

--select top 1 * from FACT_Labor_Schedule_Version_Accuracy

		insert FACT_Labor_Schedule_Version_Accuracy (datasource_id
												,business_unit_id
												,calendar_day
												,time_quarter_hour_id
												,labor_workgroup_id
												,labor_role_id
												,daypart_id
												,labor_schedule_version_id
												,labor_demand_acc_hours_qty
												,labor_demand_acc_adj_hours_qty
												,labor_sched_acc_hours_qty
												,ver_covered_time
												,ver_covered_time_adj
												,ver_calc_sched_accuracy_hours_qty
												,ver_adj_sched_accuracy_hours_qty
												,ver_calc_sched_accuracy_id
												,ver_adj_sched_accuracy_id
												,business_unit_status_id
												,comparable_flag_id
												,day_comparable_flag_id)
		select a.datasource_id
												,a.business_unit_id
												,a.calendar_day
												,a.time_quarter_hour_id
												,a.labor_workgroup_id
												,a.labor_role_id
												,a.daypart_id
												,a.labor_schedule_version_id
												,a.labor_demand_acc_hours_qty
												,a.labor_demand_acc_adj_hours_qty
												,a.labor_sched_acc_hours_qty
												,a.ver_covered_time
												,a.ver_covered_time_adj
												,a.ver_calc_sched_accuracy_hours_qty
												,a.ver_adj_sched_accuracy_hours_qty
												,a.ver_calc_sched_accuracy_id
												,a.ver_adj_sched_accuracy_id
												,a.business_unit_status_id
												,a.comparable_flag_id
												,a.day_comparable_flag_id
		from #FACT_Labor_Schedule_Version_Accuracy a
			left outer join (select f.calendar_day,f.business_unit_id,f.business_unit_status_id,f.labor_workgroup_id,f.labor_role_id,f.time_quarter_hour_id,f.datasource_id,f.labor_schedule_version_id
							from FACT_Labor_Schedule_Version_Accuracy f							
								join #bulist_processing bup on f.datasource_id = bup.datasource_id
															and f.calendar_day = bup.business_date
															and f.business_unit_id = bup.bu_id
							group by f.calendar_day,f.business_unit_id,f.business_unit_status_id,f.labor_workgroup_id,f.labor_role_id,f.time_quarter_hour_id,f.datasource_id,f.labor_schedule_version_id ) b
								on a.calendar_day = b.calendar_day 
									and a.business_unit_id = b.business_unit_id
									and coalesce(a.business_unit_status_id,0)=coalesce(b.business_unit_status_id,0)
									and a.labor_workgroup_id = b.labor_workgroup_id
									and a.labor_role_id = b.labor_role_id
									and a.time_quarter_hour_id = b.time_quarter_hour_id
									and a.datasource_id = b.datasource_id
									and a.labor_schedule_version_id = b.labor_schedule_version_id
		where b.business_unit_id is null
									
		set @InsertCount = @InsertCount + @@ROWCOUNT		
		
		update a
		set a.processed_flag = 'Y'
		from #bulist a
			join #bulist_processing b on a.bu_id = b.bu_id and a.business_date = b.business_date
end

update WH_Process_Log
set	end_datetime = getdate(),
	records_inserted = @InsertCount,
	records_updated = @UpdateCount
where record_id = @Process_Log_ID

GO

Open in new window

NOTE: I added comments in the lines that I've changed.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41829465
n_srikanth4, your issue has been solved?
0
 

Author Closing Comment

by:n_srikanth4
ID: 41834320
You are great Sir , I need to try on production environment . But I think it should work sir.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now