Query Optimization

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Query Optimization

30 posts, Page 1 of 3

1 2 3 »»»

Query Optimization

Rate Topic

Display Mode

Topic Options

Author

Message

farooq.hbs

Posted Thursday, November 15, 2012 12:07 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, March 18, 2013 1:44 AM
Points: 99, Visits: 160

Hi guys,

I have one query which is listed below which takes 5hrs mininum to execute it.. please help me on this to optimized this query so tht it runs much faster than now.

Any inputs will be highly appreciable..

DECLARE @benList TABLE(org_Name VARCHAR(150), org_Id VARCHAR(30), parent_Name VARCHAR(150), parent_id VARCHAR(150),level INT)

   INSERT INTO @benList
      SELECT md.name, m.map_item_id, md.name as parent, m.map_item_id as parent_id, 0 
      FROM map_item m, map_item_details md 
        WHERE m.map_item_id = md.map_item_id 
        and m.map_id = (select distinct tagset_id from view_tag where tagset_name = 'Beneficiary - Updated')
	and parent_id is null

   INSERT INTO @benList
      SELECT md.name, m.map_item_id, org.parent_name, org.parent_id, 1 from map_item m
           inner join map_item_details md on m.map_item_id = md.map_item_id
           inner join @benList org on org.org_id = m.parent_id
        where org.level = 0
        
Select * from @benList	

-----------------------------------------------------------------------------------------
-------- Set the dates
-----------------------------------------------------------------------------------------

		DECLARE @all int
		SET @all = (SELECT (case WHEN 'Select All' IN ('Net Sales - Type 1') THEN 1 else 0 end))

		DECLARE @dateStart datetime
		SET @dateStart = 10-12-0212

		DECLARE @now datetime
		SET @now = @dateStart

		DECLARE @today datetime 
		SET @today = getdate()

		DECLARE @prior_mo datetime
		SET @prior_mo = 
		DATEADD(month,+ 1* DATEPART(month,@today)-2,
		DATEADD(day,DATEPART(day,@now)-1,	@now)
		)

		DECLARE @yearstart datetime
		SET @yearstart = 
		DATEADD(month,-1 * DATEPART(month,@now) + 1,
		DATEADD(day,-1 * DATEPART(day,@now) + 1,@now)
		)
		
		DECLARE @yearend datetime
		SET @yearend = 
			DATEADD(month,-1 * DATEPART(month,@now) + 12,
			DATEADD(day,-1 * DATEPART(day,@now) + 30,@now)
			)
			
		DECLARE @next_yearstart datetime
		SET @next_yearstart =
			DATEADD(year,1,@yearstart) 
							
		DECLARE @next_yearend datetime
		SET @next_yearend = DATEADD(year,1,@yearend) 

		DECLARE @prior_yearstart datetime
		SET @prior_yearstart =
			DATEADD(year,-1,@yearstart) 
							
		DECLARE @prior_yearend datetime
		SET @prior_yearend = DATEADD(year,-1,@yearend) 

		DECLARE @year0 varchar(4)
		SET @year0 = DATEPART(year,@prior_yearstart)

		DECLARE @year1 varchar(4)
		SET @year1 = DATEPART(year,@yearstart)

		DECLARE @year2 varchar(4)
		SET @year2 = DATEPART(year,@next_yearstart)
		
DECLARE @locationfilter VARCHAR(1000)
SET @locationfilter = 'Finance'

DECLARE @ltion INTEGER
SET @ltion = CASE WHEN CHARINDEX('UNION ALL', @locationfilter) > 0 THEN 0 ELSE 1 END

DECLARE @mgdptfilter VARCHAR(1000)
SET @mgdptfilter = 'Div - Finance '

DECLARE @mgmtdept INTEGER
SET @mgmtdept = CASE WHEN CHARINDEX('UNION ALL', @mgdptfilter) > 0 THEN 0 ELSE 1 END

DECLARE @template_name nvarchar(255)
		SET @template_name = N'Financial Benefits'

DECLARE @template_name2 nvarchar(255)
		SET @template_name2 = N'WinningAsOne'

DECLARE @template_name3 nvarchar(255)
		SET @template_name3 = N'Renaissance'

DECLARE @template_name4 nvarchar(255)
		SET @template_name4 = N'GetFIT'

DECLARE @template_id varchar(26)
SET @template_id = (SELECT DISTINCT metric_template_id FROM dbo.View_Metric_Template WHERE template_name = @template_name)

DECLARE @template_id2 varchar(26)
SET @template_id2 = (SELECT DISTINCT metric_template_id FROM dbo.View_Metric_Template WHERE template_name = @template_name2)

DECLARE @template_id3 varchar(26)
SET @template_id3 = (SELECT DISTINCT metric_template_id FROM dbo.View_Metric_Template WHERE template_name = @template_name3)

DECLARE @template_id4 varchar(26)
SET @template_id4 = (SELECT DISTINCT metric_template_id FROM dbo.View_Metric_Template WHERE template_name = @template_name4)
			
DECLARE @view_id varchar(26)
SET @view_id = (SELECT DISTINCT metric_view_id FROM dbo.View_Metric_View WHERE metric_template_id = @template_id AND metric_view_name IN (N'ACT/FCST','ACT.FCST'))

DECLARE @view_id2 varchar(26)
SET @view_id2 = (SELECT DISTINCT metric_view_id FROM dbo.View_Metric_View WHERE metric_template_id = @template_id2 AND metric_view_name IN (N'ACT/FCST','ACT.FCST'))

DECLARE @view_id3 varchar(26)
SET @view_id3 = (SELECT DISTINCT metric_view_id FROM dbo.View_Metric_View WHERE metric_template_id = @template_id3 AND metric_view_name IN (N'ACT/FCST','ACT.FCST'))

DECLARE @view_id4 varchar(26)
SET @view_id4 = (SELECT DISTINCT metric_view_id FROM dbo.View_Metric_View WHERE metric_template_id = @template_id4 AND metric_view_name IN (N'ACT/FCST','ACT.FCST'))

DECLARE @view_id_bud varchar(26)
SET @view_id_bud = (SELECT DISTINCT metric_view_id FROM dbo.View_Metric_View WHERE metric_template_id = @template_id AND metric_view_name IN (N'Budget (Corp Only)','Plan'))

DECLARE @view_id_bud2 varchar(26)
SET @view_id_bud2 = (SELECT DISTINCT metric_view_id FROM dbo.View_Metric_View WHERE metric_template_id = @template_id2 AND metric_view_name IN (N'Budget (Corp Only)','Plan'))

DECLARE @view_id_bud3 varchar(26)
SET @view_id_bud3 = (SELECT DISTINCT metric_view_id FROM dbo.View_Metric_View WHERE metric_template_id = @template_id3 AND metric_view_name IN (N'Budget (Corp Only)','Plan'))

DECLARE @view_id_bud4 varchar(26)
SET @view_id_bud4 = (SELECT DISTINCT metric_view_id FROM dbo.View_Metric_View WHERE metric_template_id = @template_id4 AND metric_view_name IN (N'Budget (Corp Only)','Plan'))
			 
Select 
	@year0 as year0,
	@year1 as year1,
	@year2 as year2, @ltion as loc , @mgmtdept as mgdep,
	@prior_mo as YTD_prior_mo, top_parent,
	sector_benefit as [sector benefit],sector_benefit_id, conv_sector_benefit, beneficiary, beneficiary_id,conv_beneficiary_id, conv_beneficiary, 
	(Case WHEN parent like 'Supply Chain%' THEN 'Operations'
			WHEN parent IN ('Manufacturing & Logistics','Ops Admin','Procurement') THEN 'Operations'
			WHEN parent IN (N'Global R,D&E - Europe',N'Global R, D & E','RD&E') THEN 'RD&E'
			WHEN parent = N'Corporate' THEN N'Corporate'
			WHEN parent = N'Finance' THEN N'Corporate'
			WHEN parent IN ('CEO','Communications & Branding','Corporate Development','Culture','Executive','Facilities', 'HR Benefits',
							'HR G&A', 'IT','Legal','Sustainability','Tax','Regulatory','Safety', 'Finance / BO', 'HR - in EMEA P&L', 
							'Front Office','Sector President','General Admin (incl. BTO & LSS)','Office Infrastructure','Pest Functions',
							'Miscellaneous / Other','MEA Functions','Divestitures','UK Consolidation','Central Adjustments','Adjustments') THEN N'Corporate'
			WHEN parent = N'Human Resources' THEN N'Corporate'
			WHEN parent = N'Information Technology' THEN N'Corporate'
			WHEN parent = N'Law' THEN N'Corporate'
			WHEN parent = N'Mergers & Acquisitions' THEN N'Corporate'
			WHEN parent = N'Global Business Development' THEN N'Corporate'
			WHEN parent = N'Regulatory Affairs' THEN N'Corporate'
			WHEN parent = N'Treasury' THEN N'Corporate'
			WHEN parent = N'Shared Services' THEN N'Corporate'
			WHEN parent = N'Corporate Planning & Analysis' THEN N'Corporate'
			ELSE N'Division' END) as project_sponsor_function, parent_wk_id,
			parent,	child_work_id, child_name, child_status_current, 
			proj_type, proj_type_tagid, conv_project_type,
			manage_dept_tagid, manage_dept, project_mgr_id, project_mgr,
			proj_spons_id, proj_spons, Fin_Rep_id, Fin_Rep, PL_area, metric_lineitem_id, metric_lineitem,
			(Case WHEN metric_lineitem IN ('Capital (Incr) / Decr - Type 1') THEN 'C' Else benefit_type End) as benefit_type, 
			[YTD BUD], [YTD Actual/Fcst], [FY BUD0], [FY Actual/Fcst0], [YTD Actual/Fcst vs. Budget],[FY Actual/Fcst vs. Budget],[FY BUD1], [FY Actual/Fcst1], [FY BUD2], [FY Actual/Fcst2], 
			mdate, sum(act_val) as act_val, sum(bud_val) as bud_val,
			active_gate, focus_area_tagid, focus_area, mgr_role_tagid, mgr_role, program_tagid, program,
			child_sequence_id, location_tagid, location, imp_area_tagid, imp_area, fn_resp_log_tagid, fn_resp_log,
			region_tagid, region, Fnction_tagid, Fnction, proj_track_no, top_ren_tagid, top_ren, cntrl_tower_tagid, cntrl_tower,
			network_proj_tagid, network_proj,
	ISNULL((CASE WHEN (abs([FY Actual/Fcst0])=0 and abs([FY BUD0])=0 and abs([FY Actual/Fcst1])!=0 and PL_area != 'CAP'and PL_area != 'OI' and benefit_type = 'Type 1')  THEN 'New'
				WHEN (abs([FY Actual/Fcst0])=0 and abs([FY BUD0])=0 and abs([FY BUD1])!=0 and PL_area != 'CAP'and PL_area != 'OI' and benefit_type = 'Type 1') THEN 'New'
				WHEN PL_area != 'CAP'and PL_area != 'OI' and benefit_type = 'Type 1' THEN N'Carryover' END),'NA') as Carryover_Flag,
	CASE WHEN  pl_area = 'OI' and benefit_type is not null then 'NA' else Category end as Plan_Flag
	
FROM
	(
		Select 
			w.child_work_id, 
			w.child_sequence_id,
			calc_ytd_actfcst as [YTD Actual/Fcst], calc_ytd_bud as [YTD BUD],
			calc_prior_fy_act as [FY Actual/Fcst0],calc_prior_fy_bud as [FY BUD0],
			calc_cur_fy_actfcst as [FY Actual/Fcst1],calc_cur_fy_bud as [FY BUD1],
			calc_nxt_fy_act as [FY Actual/Fcst2],calc_nxt_fy_bud as [FY BUD2],
			calc_ytd_actfcst_bud as [YTD Actual/Fcst vs. Budget],calc_fy_actfcst_bud as [FY Actual/Fcst vs. Budget],
						(Select parent_name from view_work_hierarchy par where par.child_work_id = w.child_work_id and par.parent_type_name = 'Organization' and par.parent_parent_work_id IS NULL) as top_parent,
			bu_parent.parent_Name as sector_benefit,
			bu_parent.parent_id as sector_benefit_id,
			(Case 	
				WHEN BU_Parent.parent_Name IN (N'Africa Middle East',N'Europe')
				THEN 'EMEA'
				WHEN BU_Parent.parent_Name IN (N'Asia Pacific',N'Latin America') 
				THEN 'APLA' 
				ELSE BU_Parent.parent_Name END) as conv_sector_benefit,
			bu_parent.org_Name as beneficiary,
			bu_parent.org_Id as beneficiary_id,			
			(Case 
				WHEN BU_Parent.parent_Name = N'Latin America' THEN N'Latin America'
				WHEN BU_Parent.parent_Name = N'Africa Middle East' THEN N'Africa Middle East'
				WHEN bu_parent.org_Name  = N'Mexico' THEN N'Latin America'
				WHEN bu_parent.org_Name  = N'Australia' THEN N'Pacific'
				WHEN bu_parent.org_Name  = N'New Zealand' THEN N'Pacific'				
				WHEN bu_parent.org_Name  = N'Japan' THEN N'Japan/Korea'
				WHEN bu_parent.org_Name  = N'Korea' THEN N'Japan/Korea'
				WHEN bu_parent.org_Name  = N'Asia Pacific' THEN N'Asia'
				WHEN bu_parent.org_Name  = N'Hong Kong' THEN N'Asia'
				WHEN bu_parent.org_Name  = N'India' THEN N'Asia'
				WHEN bu_parent.org_Name  = N'Indonesia' THEN N'Asia'
				WHEN bu_parent.org_Name  = N'Malaysia' THEN N'Asia'
				WHEN bu_parent.org_Name  = N'Philippines' THEN N'Asia'
				WHEN bu_parent.org_Name  = N'Singapore' THEN N'Asia'
				WHEN bu_parent.org_Name  = N'Taiwan' THEN N'Asia'
				WHEN bu_parent.org_Name  = N'Thailand' THEN N'Asia'
				WHEN bu_parent.org_Name  IN ('Water LA','Paper LA') THEN N'WPS LA'
				WHEN bu_parent.org_Name  = 'Energy LA' THEN N'ES LA'
				WHEN bu_parent.org_Name  IN ('Water AP','Paper AP') THEN N'WPS AP'
				WHEN bu_parent.org_Name  = 'Energy AP' THEN N'ES AP'
				WHEN bu_parent.org_Name  IN ('Water EMEA','Paper EMEA') THEN N'WPS EMEA'
				WHEN bu_parent.org_Name  = 'Energy EMEA' THEN N'ES EMEA'
				WHEN bu_parent.org_Name  IN ('Water EAME','Paper EAME') THEN N'WPS EAME'
				WHEN bu_parent.org_Name  = 'Energy EAME' THEN N'ES EAME'
				WHEN bu_parent.org_Name  IN ('Water NA','Paper NA') THEN N'WPS NA'
				WHEN bu_parent.org_Name  = 'Energy NA' THEN N'ES NA'
				ELSE bu_parent.org_Name  END) as conv_beneficiary,	

			(Case 
				WHEN BU_Parent.parent_Name = N'Latin America' THEN isnull((Select org_Id from @benList where org_name = 'Latin America' and level =0),bu_parent.org_Id)
				WHEN BU_Parent.parent_Name = N'Africa Middle East' THEN isnull((Select org_Id from @benList where org_name = N'Africa Middle East' and level =0),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'Mexico' THEN isnull((Select org_Id from @benList where org_name = 'Latin America' and level =0),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'Australia' THEN isnull((Select org_Id from @benList where org_name = N'Pacific'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'New Zealand' THEN isnull((Select org_Id from @benList where org_name = N'Pacific'),bu_parent.org_Id)				
				WHEN bu_parent.org_Name  = N'Japan' THEN isnull((Select org_Id from @benList where org_name = N'Japan/Korea'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'Korea' THEN isnull((Select org_Id from @benList where org_name = N'Japan/Korea'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'Asia Pacific' THEN isnull((Select org_Id from @benList where org_name = N'Asia'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'Hong Kong' THEN isnull((Select org_Id from @benList where org_name = N'Asia'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'India' THEN isnull((Select org_Id from @benList where org_name = N'Asia'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'Indonesia' THEN isnull((Select org_Id from @benList where org_name = N'Asia'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'Malaysia' THEN isnull((Select org_Id from @benList where org_name = N'Asia'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'Philippines' THEN isnull((Select org_Id from @benList where org_name = N'Asia'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'Singapore' THEN isnull((Select org_Id from @benList where org_name = N'Asia'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'Taiwan' THEN isnull((Select org_Id from @benList where org_name = N'Asia'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = N'Thailand' THEN isnull((Select org_Id from @benList where org_name = N'Asia'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  IN ('Water LA','Paper LA') THEN isnull((Select org_Id from @benList where org_name = N'WPS LA'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = 'Energy LA' THEN isnull((Select org_Id from @benList where org_name = N'ES LA'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  IN ('Water AP','Paper AP') THEN isnull((Select org_Id from @benList where org_name = N'WPS AP'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = 'Energy AP' THEN isnull((Select org_Id from @benList where org_name = N'ES AP'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  IN ('Water EMEA','Paper EMEA') THEN isnull((Select org_Id from @benList where org_name = N'WPS EMEA'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = 'Energy EMEA' THEN isnull((Select org_Id from @benList where org_name = N'ES EMEA'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  IN ('Water EAME','Paper EAME') THEN isnull((Select org_Id from @benList where org_name = N'WPS EAME'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = 'Energy EAME' THEN isnull((Select org_Id from @benList where org_name = N'ES EAME'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  IN ('Water NA','Paper NA') THEN isnull((Select org_Id from @benList where org_name = N'WPS NA'),bu_parent.org_Id)
				WHEN bu_parent.org_Name  = 'Energy NA' THEN isnull((Select org_Id from @benList where org_name = N'ES NA'),bu_parent.org_Id)
				ELSE bu_parent.org_Id  END) as conv_beneficiary_id,
			child_name, child_status_current, 
			(Select name from view_work par where par.work_id = w.child_parent_work_id) as parent,
			(Select work_id from view_work par where par.work_id = w.child_parent_work_id) as parent_wk_id,
			(Select (Case 
						WHEN tag_name = 'LSS - DMAIC' THEN 'LSS'
						WHEN tag_name like 'LSS Kaizen%' THEN 'LSS'
						WHEN tag_name like 'Simplification' THEN 'LSS'
						WHEN tag_name like 'Design Excellence' THEN 'LSS'
						WHEN tag_name like 'DFLSS' THEN 'LSS' 
						WHEN tag_name like 'Renaissance%' 	THEN 'Renaissance'
						WHEN tag_name like 'WAO%' THEN 'WAO'
						WHEN tag_name like 'GetFIT%' THEN 'GetFIT'
						ELSE N'Non-LSS' END) 
			from view_tag where object_id = w.child_work_id and tagset_name = '05. Project Type') as conv_project_type,
			(Select tag_name from view_tag where object_id = w.child_work_id and tagset_name = '03. Region') as region,
			(Select tag_name from view_tag where object_id = w.child_work_id and tagset_name = '05. Project Type') as proj_type,
			(Select tag_name from view_tag where object_id = w.child_work_id and tagset_name = 'Function') as Fnction,
			(Select tag_name from view_tag where object_id = w.child_work_id and tagset_name = 'Top Renaissance Initiatives') as top_ren,
			(Select tag_name from view_tag where object_id = w.child_work_id and tagset_name = 'Mega Project / Control Tower') as cntrl_tower,
			(Select tag_name from view_tag where object_id = w.child_work_id and tagset_name = 'Matrix Program / Network Project') as network_proj,
			(Select tag_name from view_tag where object_id = w.child_work_id and tagset_name = '01. Managing Department') as manage_dept,
			(Select tag_name from view_tag where object_id = w.child_work_id and tagset_name = '06. Primary Strategic Focus Areas') as focus_area,
			(Select tag_name from view_tag where object_id = w.child_work_id and tagset_name = 'Program') as program,
			(Select tag_name from view_tag where object_id = w.child_owner_id and tagset_name = 'LSS Role') as mgr_role,
			(Select tag_name  + ', ' from view_tag where object_id = w.child_work_id and tagset_name = '04. Physical Location' AND ((@ltion = 0) OR (@ltion = 1 AND tag_name IN  $P!{selectFromList.location})) FOR XML PATH('')) as location,
			(Select tag_name from view_tag where object_id = w.child_work_id and tagset_name = 'Improvement Area') as imp_area,
			(Select tag_name from view_tag where object_id = w.child_work_id and tagset_name = 'Functional Responsibility - Logistics') as fn_resp_log,
			
			(Select tag_id from view_tag where object_id = w.child_work_id and tagset_name = '03. Region') as region_tagid,
			(Select tag_id from view_tag where object_id = w.child_work_id and tagset_name = '05. Project Type') as proj_type_tagid,
			(Select tag_id from view_tag where object_id = w.child_work_id and tagset_name = 'Function') as Fnction_tagid,
			(Select tag_id from view_tag where object_id = w.child_work_id and tagset_name = 'Top Renaissance Initiatives') as top_ren_tagid,
			(Select tag_id from view_tag where object_id = w.child_work_id and tagset_name = 'Mega Project / Control Tower') as cntrl_tower_tagid,
			(Select tag_id from view_tag where object_id = w.child_work_id and tagset_name = 'Matrix Program / Network Project') as network_proj_tagid,
			(Select tag_id from view_tag where object_id = w.child_work_id and tagset_name = '01. Managing Department') as manage_dept_tagid,
			(Select tag_id from view_tag where object_id = w.child_work_id and tagset_name = '06. Primary Strategic Focus Areas') as focus_area_tagid,
			(Select tag_id from view_tag where object_id = w.child_work_id and tagset_name = 'Program') as program_tagid,
			(Select tag_id from view_tag where object_id = w.child_owner_id and tagset_name = 'LSS Role') as mgr_role_tagid,
			(Select tag_id  + ', ' from view_tag where object_id = w.child_work_id and tagset_name = '04. Physical Location' AND ((@ltion = 0) OR (@ltion = 1 AND tag_name IN  $P!{selectFromList.location})) FOR XML PATH('')) as location_tagid,
			(Select tag_id from view_tag where object_id = w.child_work_id and tagset_name = 'Improvement Area') as imp_area_tagid,
			(Select tag_id from view_tag where object_id = w.child_work_id and tagset_name = 'Functional Responsibility - Logistics') as fn_resp_log_tagid,

			(Select value from view_custom_field where object_id = w.child_work_id and name = 'Project Tracking Number') as proj_track_no,
			(Select first_name + ' ' + last_name from view_user where user_id = w.child_owner_id) as project_mgr,
			(Select user_id from view_user where user_id = w.child_owner_id) as project_mgr_id,
			(SELECT DISTINCT (u.first_name + ' ' + u.last_name) + ', '  FROM view_user u INNER JOIN view_work_role wr on wr.user_id = u.user_id AND wr.work_id = w.child_work_id
						AND wr.role = 'Financial Rep'  FOR XML PATH('')) AS Fin_Rep,
			(SELECT DISTINCT u.user_id + ', '  FROM view_user u INNER JOIN view_work_role wr on wr.user_id = u.user_id AND wr.work_id = w.child_work_id
						AND wr.role = 'Financial Rep'  FOR XML PATH('')) AS Fin_Rep_id,
			(SELECT(SELECT first_name + ' ' + last_name FROM view_user WHERE user_id = r.user_id) +', ' 
							FROM view_work_role r INNER JOIN Configurable_Role_Detail crd ON r.role_id = crd.role_id WHERE r.work_id = w.child_work_id 
								and role like 'ConfRole%' and crd.name = 'Project Sponsor' FOR XML PATH('')) as proj_spons,
								(SELECT(SELECT user_id FROM view_user WHERE user_id = r.user_id) +', ' 
							FROM view_work_role r INNER JOIN Configurable_Role_Detail crd ON r.role_id = crd.role_id WHERE r.work_id = w.child_work_id 
								and role like 'ConfRole%' and crd.name = 'Project Sponsor' FOR XML PATH('')) as proj_spons_id,
			(Select name from view_work where work_id = w.child_active_gate) as active_gate,
			(CASE WHEN (metric.name like 'Frozen Standard%'
							or metric.name like '%Carryover%') THEN 'In Plan'
							when  metric.name  like 'Operating Income%' THEN 'NA'
							ELSE N'Not in Plan' END) as Category,
			(Case 
						WHEN metric.name IN ('Capital (Incr) / Decr - Type 1') THEN 'CAP'
						WHEN ( metric.name like 'Allocated Admin%' 
							or metric.name like 'Bad Debts%'
							or metric.name like 'Division Admin%'
							or metric.name like 'Field Selling%'
							or metric.name like 'Marketing%'
							or metric.name like 'Sales Comp%'
							or metric.name like 'SG&A Carryover%'
							or metric.name like 'Sales Mgmt%')THEN 'SGA'
						WHEN ( metric.name like 'COGS Admin%' 
							or metric.name like 'Frozen Standard%'
							or metric.name like 'COGS Carryover%'
							or metric.name like 'Customer Delivery%'
							or metric.name like 'Direct Charges%'
							or metric.name like 'Net Sales%'
							or metric.name like 'Storage & Handling%'
							or metric.name like 'Division Reformulations%'
							or metric.name like 'Interest Savings%'
							or metric.name like 'Variation%')THEN 'GM'	
						WHEN metric.name IN ('Synergy Savings (Gross)','Operating Benefits') THEN 'SG'
						WHEN metric.name like 'Period Cost%' THEN 'PC'
						WHEN metric.name = 'Synergy Savings (Net)' 
							or metric.name like 'Net Benefit Ecolab%' THEN 'OI'
						WHEN metric.name IN ('Gross Headcount Reductions (-)', 'Replacements / Additions (+)',
								'Net Headcount (Reductions) / Increase') THEN 'HC'
						WHEN metric.name like 'Net Benefit EMEA%' THEN 'NE'
						WHEN ( metric.name like 'Operating Income%') THEN 'OI'		
						ELSE N'' END) as pl_area,
			case when metric.name IN ('Synergy Savings (Gross)', 'Operating Benefits', 'Period Cost Corporate', 'Period Cost EMEA',
							'Capital Costs', 'Period Costs') then 'Type 1' 
				 when metric.name like 'Net Benefit%' then 'Type 1'
				 when metric.name like 'Period Cost%' then 'Type 1'
				 when metric.name = 'Synergy Savings (Net)' then 'Type 1'
				 when metric.name IN ('Gross Headcount Reductions (-)','Replacements / Additions (+)','Net Headcount (Reductions) / Increase') then ' '
				 else Right(metric.name,6) end  as benefit_type,
			metric.name as metric_lineitem,
			metric.template_item_id as metric_lineitem_id, mdate,
			case when metric_view_name IN (N'ACT/FCST','ACT.FCST')then value end as act_val, 
			case when metric_view_name IN (N'Budget (Corp Only)','Plan') then value end as bud_val
	from 
		view_work_hierarchy w
		INNER JOIN dbo.View_Shared_Portfolio portfolio	ON w.child_work_Id = portfolio.work_Id AND portfolio.shared_portfolio_id = @sharedPortfolioId  
		INNER JOIN dbo.View_Metric_Instance mi on w.child_work_id= mi.linked_project_id
		INNER JOIN dbo.fn_advancedMetrics(@prior_yearstart,@next_yearend) as metric ON mi.metric_instance_id = metric.metric_instance_id
		INNER JOIN View_Metric_View mv ON mv.metric_view_id = view_id 
		INNER JOIN dbo.map_item_details as Ben on ben.map_item_id = metric.beneficiary_id
		LEFT JOIN @benList BU_Parent ON BU_Parent.org_Id = ben.map_item_id
		LEFT OUTER JOIN 
		(
			SELECT mi.linked_project_id, m.beneficiary_id,
			(SELECT DISTINCT t.tag_name FROM view_tag t WHERE t.tag_id = m.beneficiary_id) as beneficiary,
			--m.mdate, 
			m.name as item_name,
			--sum(m.value) as value,
			sum(CASE WHEN view_id IN (@view_id,@view_id2,@view_id3,@view_id4) and mdate between @yearstart and @prior_mo
					  THEN value ELSE 0 END)  as calc_ytd_actfcst,
			sum(CASE WHEN view_id IN (@view_id_bud,@view_id_bud2,@view_id_bud3,@view_id_bud4) and mdate between @yearstart and @prior_mo
					  THEN value ELSE 0 END)  as calc_ytd_bud,
			sum(CASE WHEN view_id IN (@view_id,@view_id2,@view_id3,@view_id4) and mdate between @prior_yearstart and @prior_yearend
					  THEN value ELSE 0 END)  as calc_prior_fy_act,
			sum(CASE WHEN view_id IN (@view_id,@view_id2,@view_id3,@view_id4) and mdate between @yearstart and @yearend
					 THEN value 	ELSE 0 END)  as calc_cur_fy_actfcst,
			sum(CASE WHEN view_id IN (@view_id,@view_id2,@view_id3,@view_id4) and mdate between @next_yearstart and @next_yearend
					 THEN value ELSE 0 END)  as calc_nxt_fy_act,
			sum(CASE WHEN view_id IN (@view_id_bud,@view_id_bud2,@view_id_bud3,@view_id_bud4) and mdate between @prior_yearstart and @prior_yearend
					THEN value ELSE 0 END)  as calc_prior_fy_bud,
			sum(CASE WHEN view_id IN (@view_id_bud,@view_id_bud2,@view_id_bud3,@view_id_bud4) and mdate between @yearstart and @yearend
					THEN value ELSE 0 END)  as calc_cur_fy_bud,
			sum(CASE WHEN view_id IN (@view_id_bud,@view_id_bud2,@view_id_bud3,@view_id_bud4) and mdate between @next_yearstart and @next_yearend
					THEN value 	ELSE 0 END)  as calc_nxt_fy_bud,
					---------Calc Yearly Variance--------------						
			((Sum(CASE 	WHEN view_id IN (@view_id,@view_id2,@view_id3,@view_id4) and mdate between @yearstart and @prior_mo
					THEN value 	ELSE 0 END))-(sum(CASE 
							WHEN view_id IN (@view_id_bud,@view_id_bud2,@view_id_bud3,@view_id_bud4)  and mdate between @yearstart and @prior_mo
							THEN value 	ELSE 0 END))) as calc_ytd_actfcst_bud,
			((Sum(CASE 	WHEN view_id IN (@view_id,@view_id2,@view_id3,@view_id4) and mdate between @yearstart and @yearend
					THEN value 	ELSE 0 END))-(sum(CASE 
							WHEN view_id IN (@view_id_bud,@view_id_bud2,@view_id_bud3,@view_id_bud4) and mdate between @yearstart and @yearend
							THEN value ELSE 0 END))) as calc_fy_actfcst_bud
			FROM 
				view_metric_instance mi
				INNER JOIN fn_advancedMetrics(@prior_yearstart,@next_yearend) m ON m.metric_instance_id = mi.metric_instance_id 
						AND m.view_id IN (@view_id,@view_id2,@view_id3,@view_id4,@view_id_bud,@view_id_bud2,@view_id_bud3,@view_id_bud4)
						AND m.beneficiary_id IS NOT NULL
			WHERE mi.metric_template_id IN (@template_id,@template_id2,@template_id3,@template_id4)
				and m.name NOT like '%Savings Categories%' and m.name <> 'Operating Income - Total' 
			GROUP BY mi.linked_project_id, m.beneficiary_id, m.name
			) 
		ytd ON ytd.linked_project_id = w.child_work_id and metric.beneficiary_id = ytd.beneficiary_id and ytd.item_name = metric.name
	where 
	mi.metric_template_id IN (@template_id,@template_id2,@template_id3,@template_id4)and metric.beneficiary_ID is not null 
	and bu_parent.org_Name IN (Select distinct org_name from @benList where org_id IN $P!{selectFromList.convben})	
	and (metric.name NOT like '%Savings Categories%')and metric.name <> 'Operating Income - Total' 
	AND (@all = 1 OR (@all=0 AND metric.template_item_id IN $P!{selectFromList.lineitem})) 
	and child_depth = 0
	)X
WHERE top_parent IN ('Ecolab', 'WinningasOne', 'Renaissance', 'GetFIT')
AND conv_project_type IN $P!{selectFromList.projtype}
AND region IN $P!{selectFromList.region}
AND 
		(
			(@mgmtdept = 0)
			OR
			(@mgmtdept = 1 AND manage_dept IN  $P!{selectFromList.mgdept})
		)
group by top_parent, child_work_id, child_sequence_id, beneficiary, conv_beneficiary_id, conv_beneficiary, sector_benefit,sector_benefit_id,
	beneficiary_id, conv_sector_benefit, parent_wk_id, child_name, child_status_current, parent, proj_type, manage_dept, focus_area,
	program, mgr_role, location, imp_area, fn_resp_log, project_mgr, project_mgr_id, Fin_Rep, proj_spons_id, proj_spons,active_gate,
	pl_area, region, Fnction, top_ren, cntrl_tower, network_proj, proj_track_no,Fin_Rep_id, metric_lineitem_id,
	region_tagid, proj_type_tagid, Fnction_tagid, top_ren_tagid, cntrl_tower_tagid, network_proj_tagid,
	manage_dept_tagid, focus_area_tagid, program_tagid, mgr_role_tagid, location_tagid, imp_area_tagid, fn_resp_log_tagid,	
	metric_lineitem, benefit_type, conv_project_type, Category, mdate, [YTD Actual/Fcst],[YTD BUD],
	[FY Actual/Fcst0],[FY BUD0], [FY Actual/Fcst1],[FY BUD1],[FY Actual/Fcst2],[FY BUD2],
	[YTD Actual/Fcst vs. Budget],[FY Actual/Fcst vs. Budget]
order by child_name, mdate

Post #1384989

Jason-299789

Posted Thursday, November 15, 2012 1:28 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 838, Visits: 2,200

I've had a quick look at the query and you have an awful lof of correlated sub queries, this could be where the performance issue is as each query would by run for every row executed.

I'm just not sure how you would rewrite them as they seem to be heavily targeted to focus on a specific row, which rules out the benefit of a single join to get all the data, and I'm not sure a Scalar or Inline table function would help either.

I'm also sure some of the cases could be resolved such as the one for conv_Beneficiary and conv_Beneficiary_id, with a simple join as as the maintenance is likely to tiresome and prone to mistakes if things change.

What is your scope to change things in terms of adding new tables to the db if required?

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1384997

farooq.hbs

Posted Thursday, November 15, 2012 1:57 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, March 18, 2013 1:44 AM
Points: 99, Visits: 160

Thanks for the reply.

No we cannot crest another table but we can do with scalar or table variable, if necessary.
Yup there is lot of correlated queries too..

However, please let me know where i can optimize the query like you said on simple join, if found anymore like so that query optimized and execute fastly.

Little little things make things easy.

Post #1385007

Jason-299789

Posted Thursday, November 15, 2012 2:23 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 838, Visits: 2,200

Its a shame you have no scope to be able to add lookups.

Is this for an SQL 2008 (or greater) edition?

You might be able to work with the table variable @benList, can you post the table definitions and sample data for the map_item and map_item_details as there might be a better way to do that.

I do feel the key to increaseing performance though is removing in those correlated sub queries.

One quick question can anyone explain what the $P is for at the bottom of the query as I'm not familiar with the syntax.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1385016

Bhuvnesh

Posted Thursday, November 15, 2012 2:53 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

Post the execution plan here , so that people can have more clear view to help you

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1385022

farooq.hbs

Posted Thursday, November 15, 2012 3:13 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, March 18, 2013 1:44 AM
Points: 99, Visits: 160

It is report Query..$P is an varaible(Filters) to which report runs.

There query has more than 50 tables to it, so cant post such definition..

The above returning exact data but performing slowly.

And for the table definition map_item and map_item_details

USE [reporting_Ecolab]
GO

/****** Object:  Table [dbo].[Map_Item]    Script Date: 11/15/2012 15:39:29 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Map_Item](
	[map_item_id] [dbo].[ci_guid] NOT NULL,
	[map_id] [dbo].[ci_guid] NOT NULL,
	[parent_id] [dbo].[ci_guid] NULL,
	[display_sequence] [smallint] NULL,
	[locked] [dbo].[ci_boolean] NOT NULL,
	[color] [int] NULL,
 CONSTRAINT [PK_MI] PRIMARY KEY CLUSTERED 
(
	[map_item_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Map_Item]  WITH CHECK ADD  CONSTRAINT [FK_M_MI] FOREIGN KEY([map_id])
REFERENCES [dbo].[Map] ([map_id])
GO

ALTER TABLE [dbo].[Map_Item] CHECK CONSTRAINT [FK_M_MI]
GO

ALTER TABLE [dbo].[Map_Item]  WITH CHECK ADD  CONSTRAINT [FK_MI_MI] FOREIGN KEY([parent_id])
REFERENCES [dbo].[Map_Item] ([map_item_id])
GO

ALTER TABLE [dbo].[Map_Item] CHECK CONSTRAINT [FK_MI_MI]
GO

ALTER TABLE [dbo].[Map_Item] ADD  DEFAULT ('N') FOR [locked]
GO

USE [reporting_Ecolab]
GO

/****** Object:  Table [dbo].[Map_Item_Details]    Script Date: 11/15/2012 15:42:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Map_Item_Details](
	[map_item_id] [dbo].[ci_guid] NOT NULL,
	[locale_id] [dbo].[ci_guid] NOT NULL,
	[name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_MapItemDetails] PRIMARY KEY NONCLUSTERED 
(
	[map_item_id] ASC,
	[locale_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Map_Item_Details]  WITH CHECK ADD  CONSTRAINT [FK_MapItemDetails_Locale] FOREIGN KEY([locale_id])
REFERENCES [dbo].[Locale] ([locale_id])
GO

ALTER TABLE [dbo].[Map_Item_Details] CHECK CONSTRAINT [FK_MapItemDetails_Locale]
GO

ALTER TABLE [dbo].[Map_Item_Details]  WITH CHECK ADD  CONSTRAINT [FK_MapItemDetails_MapItem] FOREIGN KEY([map_item_id])
REFERENCES [dbo].[Map_Item] ([map_item_id])
GO

ALTER TABLE [dbo].[Map_Item_Details] CHECK CONSTRAINT [FK_MapItemDetails_MapItem]
GO

Post #1385031

Bhuvnesh

Posted Thursday, November 15, 2012 3:41 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

Bhuvnesh (11/15/2012)

Post the execution plan here , so that people can have more clear view to help you

execution plaan need here

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1385051

aaron.reese

Posted Thursday, November 15, 2012 3:42 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 246, Visits: 452

I don't know whether these will be giving you a performance hit, but for me the large CASE statements would be a maintenance issue.

In these circumstances I like to create a #TempTable at the top of the script that holds the lookup table between the Input value in and the output value that you require for the column, then replace the CASE with a LEFT JOIN to the table and COALESCE() the output to trap for a missing link. It is easier to maintain and keeps the bulk of the code clean. Plus you can add indexes which may help the optimiser. You may need to take care on the LIKE ('Supply Chain%') because you can't link on that.

You have got an ISNULL that is working on a combination of values. Some of these appear to be financial and you are doing abs(value) = 0. If the value is zero its absolute sign is irrelevant and you are running through a function that is not required. Also the statement has != in it. See if you can work the logic so that they are = signs and then reverse the whole comare with a NOT(). This may help to make the query SARGable.

I can't see any reason why the (SELECT fieldname from ViewTag...) corelated subqueries could't be a joined table and the filter criteria is always the same value.

Post #1385053

Jason-299789

Posted Thursday, November 15, 2012 4:11 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 838, Visits: 2,200

Aaron,

I wonder if using a CTE the cross tab trick to generate a wide list for the tagset names and tag_id's for each child_work_id would work, you could then join it back to the main view_work_hierarchy.

eg,

WITH Cte_Cross_Tab AS
(
Select
	child_work_id 
	MAX(region) region
	MAX(proj_type) proj_type
From 
(Select
	w.child_work_id 
	,CASE vt.tagset_name
		WHEN '03. Region' THEN vt.tag_name
		ELSE NULL
	END region
	,CASE vt.tagset_name
		WHEN '03. Region' THEN vt.tag_id
		ELSE NULL
	END region_tagid
	,CASE vt.tagset_name
		WHEN '05. Project Type' THEN vt.tag_name
		ELSE NULL
	END proj_type
	,CASE vt.tagset_name
		WHEN '05. Project Type' THEN vt.tag_id
		ELSE NULL
	END proj_type_tagid
--repeat for each type.
From view_work_hierarchy w
LEFT JOIN view_tag vt
	ON  vt.object_id = w.child_work_id) x
group by child_work_id
)
Select ct.*
from view_work_hierarchy w
JOIN Cte_Cross_Tab ct on w.child_work_id=ct.child_work_id

Might be worth trying.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1385075

aaron.reese

Posted Thursday, November 15, 2012 5:35 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 3:38 AM
Points: 246, Visits: 452

And that us why MI Developers hate 'lazy' programmers that use EAV tables.
By lazy, i really mean misguided or uneducated. Entity Attribute Variables make ut easy to enhance the functionality of the software, but makes data extraction almost impossible.

Post #1385103

« Prev Topic | Next Topic »

30 posts, Page 1 of 3

1 2 3 »»»

Permissions