November 15, 2012 at 12:07 am
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 portfolioON 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
November 15, 2012 at 1:28 am
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
November 15, 2012 at 1:57 am
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.
November 15, 2012 at 2:23 am
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
November 15, 2012 at 2:53 am
Post the execution plan here , so that people can have more clear view to help you
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 15, 2012 at 3:13 am
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,
[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
November 15, 2012 at 3:41 am
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 15, 2012 at 3:42 am
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.
November 15, 2012 at 4:11 am
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
November 15, 2012 at 5:35 am
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.
November 15, 2012 at 6:38 am
Thankfully I've never had the pleasure of knowingly working with an EVA, and I've heard enough horror stories about them to not want to.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 15, 2012 at 11:35 am
aaron.reese (11/15/2012)
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.
That's a pretty “misguided or uneducated” post for something that should be classified as "It Depends". Are you insinuating that I'm "misguided or uneducated" because I use EAVs?
It's not just "misguided or uneducated" people that would make and use an EAV. Some fairly intelligent people make them because there are a great many good and proper reasons to use EAVs (Audit tables, configuration tables, specialized multi-device data collection table, common performance by department and date, avoidance of extremely wide tables with extremely sparse columns, etc, etc) and, used properly, can provide some serious benefits with very little work.
Of course, you have to actually know the proper methods for using EAVs and the proper methods for building them. Problems asssociated with many types of EAVs are frequently nothing more than a manifestation of people who think they're "guided and educated" and it frequently turns out that those supposedly educated people just don't know how to use EAVs. For example, I know a whole lot of really sharp people that don't know that the datatype of the value inserted into an EAV can automatically be included in the data and be used when reading the data. And, no... I'm not talking about creating a separate column for the datatype.
Don't take the "lazy, misguided, or uneducated" way out by summarily dismissing EAVs. 😉 Like everything else in the world of SQL Server, "It Depends".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 15, 2012 at 11:44 am
Jeff Moden (11/15/2012)
aaron.reese (11/15/2012)
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.
That's a pretty “misguided or uneducated” post for something that should be classified as "It Depends". Are you insinuating that I'm "misguided or uneducated" because I use EAVs?
It's not just "misguided or uneducated" people that would make and use an EAV. Some fairly intelligent people make them because there are a great many good and proper reasons to use EAVs (Audit tables, configuration tables, specialized multi-device data collection table, common performance by department and date, avoidance of extremely wide tables with extremely sparse columns, etc, etc) and, used properly, can provide some serious benefits with very little work.
Of course, you have to actually know the proper methods for using EAVs and the proper methods for building them. Problems asssociated with many types of EAVs are frequently nothing more than a manifestation of people who think they're "guided and educated" and it frequently turns out that those supposedly educated people just don't know how to use EAVs. For example, I know a whole lot of really sharp people that don't know that the datatype of the value inserted into an EAV can automatically be included in the data and be used when reading the data. And, no... I'm not talking about creating a separate column for the datatype.
Don't take the "lazy, misguided, or uneducated" way out by summarily dismissing EAVs. 😉 Like everything else in the world of SQL Server, "It Depends".
MADNESS! MADNESS I TELL YOU! LOL... I am with Jeff on this one, having used EAV methodology on a project that really would have suffered any other way. Its like cursors, its not that cursors are evil, its what you do in them thats so bad.
I am curious though Jeff, what is this storing of the datatype magic you speak of? :w00t:
November 15, 2012 at 1:57 pm
Ooops!
Its not often I get flamed on here and I certainly didn't mean to hijack the thread.
Of all the people who post on here Jeff and GSquared are the two I respect the most. I think Jeff kind of made my point for me: Before you go down the EAV route, consider the implications carefully. I agree that there are SOME instances where it is convenient, but in my experience they are generally used to quickly add some data capture to an entity (e.g. I need to be able to store the ISBN number against an item in book category and the Dryness rating of a wine in the booze category). Easy programming to add a dynamic field to the form and create a table (itemID,DataType,Value) and link DataTypes to categories; but a nightmare when you need to report on those fields as the first thing you need to do is pivot all the data for a report and create dozens of joins to the same table
Can we get Joe C to referee? :w00t:
November 16, 2012 at 12:27 am
aaron.reese (11/15/2012)
Ooops!Its not often I get flamed on here and I certainly didn't mean to hijack the thread.
Of all the people who post on here Jeff and GSquared are the two I respect the most. I think Jeff kind of made my point for me: Before you go down the EAV route, consider the implications carefully. I agree that there are SOME instances where it is convenient, but in my experience they are generally used to quickly add some data capture to an entity (e.g. I need to be able to store the ISBN number against an item in book category and the Dryness rating of a wine in the booze category). Easy programming to add a dynamic field to the form and create a table (itemID,DataType,Value) and link DataTypes to categories; but a nightmare when you need to report on those fields as the first thing you need to do is pivot all the data for a report and create dozens of joins to the same table
Can we get Joe C to referee? :w00t:
Thanks for the compliment, Aaron, but I didn't flame you. I just fed back some of what you dished out. Apparently you didn't like it any more than I did. The reason why I jumped on you a bit was because of the JC-like wording. We don't need another flamer like that. 😉
And, no... you don't need to create "dozens of joins" to the same table if you do it right. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply