November 16, 2012 at 12:36 am
DiverKas (11/15/2012)
I am curious though Jeff, what is this storing of the datatype magic you speak of? :w00t:
What's the one datatype that most people have never used and many don't even know exists? Worse yet, if they do know it exits, they've never read about how it stores meta-data which is the reason why it has a maximum length of 8,016 (8,000 max for the base datatype).
SQL_VARIANT
Even with a max length of 8,016, that still leaves room in the mythical 8,060 row for a couple of IDs and a couple of dates without anything going "out of row" (which, of course, is less of a problem since 2k5). It makes for a great EAV-style audit table.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2012 at 1:06 am
In the end I suppose EAV's live or die on the design and implementation like any DB, but we only hear the horror stories of using EAV's when it all goes horribly wrong, rather than when they are a raging success.
It would be interesting to hear from the original poster to see how hes getting on with tuning the query.
(dont know where i got EVA instead of EAV from).
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 16, 2012 at 3:26 am
Jason & Guys,
I'm bit confused with discussion going on here.
I worked with the jason post CTE method (Replacing Correlated Queries to CTE) but it is taking much more tym than now..
Could anyone help on this
November 16, 2012 at 3:40 am
farooq.hbs (11/16/2012)
I worked with the jason post CTE method (Replacing Correlated Queries to CTE) but it is taking much more tym than now..Could anyone help on this
use different and NEW post or thread for this
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 16, 2012 at 3:49 am
farooq.hbs (11/16/2012)
Jason & Guys,I'm bit confused with discussion going on here.
Sorry as tends to happen on message boards things sometimes get a little side tracked, hopefully we can bring it back on track.
I worked with the jason post CTE method (Replacing Correlated Queries to CTE) but it is taking much more tym than now..
Could anyone help on this.
I did hope the CTE would help allieviate the problem to some degree,but it seems from this post its made it worse.
Because of the complexity of the query it may be that you have to take a step back and look at the performance of each of the complex selects, tune those so that they're as efficient as possible and then try and reintegrate them.
Or use them to generate temporary table ready for a remerge which can sometime be quicker than writing one single uber query.
The problem that we face is that we dont have access to your database or data so a lot of suggestions are likely to be try in the realm of does this help?
You could try posting the SQL plan that is being used to see if someon can suggest index tweaks or if they spot something that looks out of place in the plan.
I take it the db has regular maintenance plans (index rebuilds, etc) run to keep fragmentation to a minimum.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 16, 2012 at 6:41 am
Jason-299789 (11/16/2012)
Or use them to generate temporary table ready for a remerge which can sometime be quicker than writing one single uber query.
I think that "Divide'n'Conquer" recommendation is the best start on this. It'll allow troubleshooting and optimmization of much smaller parts and, as you say, may be the fix all by itself without the need for extra optimization or tuning. I've used the method to turn many a 45 minute CPU pinning, IO paralyzing, resource hog into something that runs in just a second or three and causes a blip on the resources that most people wouldn't even notice.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2012 at 7:15 am
Hi guys,
This is i have from yrsday to the previous query..Still no improvement on the query..
Need some more inputs to it
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 ('Select All') THEN 1 else 0 end))
--DECLARE @sharedPortfolioId VARCHAR(100)
--SET @sharedPortfolioId = $P{sharedPortfolioId.portfolio}
DECLARE @now datetime
SET @now = '10-10-2011'---- Assigin the parameter here iteslf
DECLARE @prior_mo datetime
SET @prior_mo =
DATEADD(month,+ 1* DATEPART(month,getdate())-2,-------Add Getdate() instead off @today parameter
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 @ltion INTEGER
SET @ltion = CASE WHEN CHARINDEX('UNION ALL', 'Finance') > 0 THEN 0 ELSE 1 END----Add location Paramter here
DECLARE @mgmtdept INTEGER
SET @mgmtdept = CASE WHEN CHARINDEX('UNION ALL', 'GSC - Manufacturing') > 0 THEN 0 ELSE 1 END;---Add mgmtdept parameter here
-----------------tag values------------------------------
--DECLARE @Cte_Cross_Tab TABLE (work_id_tag VARCHAR(150),region VARCHAR(500),region_tagid VARCHAR(500),proj_type VARCHAR(500),proj_type_tagid VARCHAR(500),
--Fnction VARCHAR(500),Fnction_tagid VARCHAR(500),top_ren VARCHAR(500),top_ren_tagid VARCHAR(500),
--cntrl_tower VARCHAR(500),cntrl_tower_tagid VARCHAR(500),network_proj VARCHAR(500),network_proj_tagid VARCHAR(500),
--manage_dept VARCHAR(500),manage_dept_tagid VARCHAR(500),focus_area VARCHAR(500),focus_area_tagid VARCHAR(500),
--program VARCHAR(500),program_tagid VARCHAR(500),mgr_role VARCHAR(500),mgr_role_tagid VARCHAR(500),location VARCHAR(500),
--location_tagid VARCHAR(500),imp_area VARCHAR(500),imp_area_tagid VARCHAR(500),fn_resp_log VARCHAR(500),fn_resp_log_tagid VARCHAR(500))
--INSERT INTO @Cte_Cross_Tab
WITH Cte_Cross_Tab AS (
Select
work_id_tag = w.child_work_id,
child_seq = w.child_sequence_id,
child_name = w.child_name,
child_status_current = w.child_status_current,
child_parent_work_id =w.child_parent_work_id,
child_owner_id = w.child_owner_id,
child_active_gate = w.child_active_gate,
child_depth = w.child_depth,
region = MAX(CASE vt.tagset_name WHEN '03. Region' THEN vt.tag_name ELSE NULL END),
region_tagid =MAX(CASE vt.tagset_name WHEN '03. Region' THEN vt.tag_id ELSE NULL END),
proj_type = MAX(CASE vt.tagset_name WHEN '05. Project Type' THEN vt.tag_name ELSE NULL END),
proj_type_tagid = MAX(CASE vt.tagset_nameWHEN '05. Project Type' THEN vt.tag_id ELSE NULL END),
Fnction = MAX(CASE vt.tagset_nameWHEN 'Function' THEN vt.tag_name ELSE NULL END),
Fnction_tagid =MAX(CASE vt.tagset_name WHEN 'Function' THEN vt.tag_id ELSE NULL END),
top_ren = MAX(CASE vt.tagset_name WHEN 'Top Renaissance Initiatives' THEN vt.tag_name ELSE NULL END),
top_ren_tagid = MAX(CASE vt.tagset_nameWHEN 'Top Renaissance Initiatives' THEN vt.tag_id ELSE NULL END),
cntrl_tower = MAX(CASE vt.tagset_nameWHEN 'Mega Project / Control Tower' THEN vt.tag_name ELSE NULL END),
cntrl_tower_tagid = MAX(CASE vt.tagset_name WHEN 'Mega Project / Control Tower' THEN vt.tag_id ELSE NULL END ),
network_proj = MAX(CASE vt.tagset_name WHEN 'Matrix Program / Network Project' THEN vt.tag_name ELSE NULL END),
network_proj_tagid = MAX(CASE vt.tagset_nameWHEN 'Matrix Program / Network Project' THEN vt.tag_id ELSE NULL END),
manage_dept = MAX(CASE vt.tagset_nameWHEN '01. Managing Department' THEN vt.tag_name ELSE NULL END),
manage_dept_tagid = MAX(CASE vt.tagset_name WHEN '01. Managing Department' THEN vt.tag_id ELSE NULL END),
focus_area = MAX(CASE vt.tagset_name WHEN '06. Primary Strategic Focus Areas' THEN vt.tag_name ELSE NULL END),
focus_area_tagid = MAX(CASE vt.tagset_nameWHEN '06. Primary Strategic Focus Areas' THEN vt.tag_id ELSE NULL END),
program = MAX(CASE vt.tagset_name WHEN 'Program' THEN vt.tag_name ELSE NULL END),
program_tagid = MAX(CASE vt.tagset_nameWHEN 'Program' THEN vt.tag_id ELSE NULL END),
mgr_role = MAX(CASE vt.tagset_nameWHEN 'LSS Role' THEN vt.tag_name ELSE NULL END),
mgr_role_tagid = MAX(CASE vt.tagset_name WHEN 'LSS Role' THEN vt.tag_id ELSE NULL END),
location = MAX(CASE vt.tagset_name WHEN '04. Physical Location' THEN vt.tag_name ELSE NULL END),
location_tagid=MAX(CASE vt.tagset_nameWHEN '04. Physical Location' THEN vt.tag_id ELSE NULL END),
imp_area = MAX(CASE vt.tagset_nameWHEN 'Improvement Area' THEN vt.tag_name ELSE NULL END),
imp_area_tagid = MAX(CASE vt.tagset_name WHEN 'Improvement Area' THEN vt.tag_id ELSE NULL END),
fn_resp_log = MAX(CASE vt.tagset_name WHEN 'Functional Responsibility - Logistics' THEN vt.tag_name ELSE NULL END),
fn_resp_log_tagid = MAX(CASE vt.tagset_nameWHEN 'Functional Responsibility - Logistics' THEN vt.tag_id ELSE NULL END )
--conv_project_type = MAX(Case WHEN tagset_name = '05. Project Type' AND tag_name = 'LSS - DMAIC' THEN 'LSS'
-- WHEN tagset_name = '05. Project Type' AND tag_name like 'LSS Kaizen%' THEN 'LSS'
-- WHEN tagset_name = '05. Project Type' AND tag_name like 'Simplification' THEN 'LSS'
-- WHEN tagset_name = '05. Project Type' AND tag_name like 'Design Excellence' THEN 'LSS'
-- WHEN tagset_name = '05. Project Type' AND tag_name like 'DFLSS' THEN 'LSS'
-- WHEN tagset_name = '05. Project Type' AND tag_name like 'Renaissance%' THEN 'Renaissance'
-- WHEN tagset_name = '05. Project Type' AND tag_name like 'WAO%' THEN 'WAO'
-- WHEN tagset_name = '05. Project Type' AND tag_name like 'GetFIT%' THEN 'GetFIT'
--ELSE N'Non-LSS' END)
--from view_tag where object_id = ct.work_id_tag and tagset_name = '05. Project Type') as conv_project_type,
From view_work_hierarchy w
LEFT JOIN view_tag vt
ON vt.object_id = w.child_work_id
WHERE vt.tagset_name IN('03. Region','05. Project Type','Function','Top Renaissance Initiatives','Mega Project / Control Tower','Matrix Program / Network Project',
'01. Managing Department','06. Primary Strategic Focus Areas','Program','LSS Role','04. Physical Location','Improvement Area',
'Functional Responsibility - Logistics')
GROUP BY w.child_work_id,w.child_sequence_id,w.child_name,w.child_status_current,
w.child_parent_work_id,w.child_owner_id,w.child_active_gate,child_depth
)
--select * from Cte_Cross_Tab
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,work_id_tag, 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_seq, 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
CT.*,
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 = ct.work_id_tag 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 IN (N'Australia',N'New Zealand') THEN N'Pacific'
--WHEN bu_parent.org_Name = N'New Zealand' THEN N'Pacific'
WHEN bu_parent.org_Name IN (N'Japan',N'Korea') THEN N'Japan/Korea'
--WHEN bu_parent.org_Name = N'Korea' THEN N'Japan/Korea'
WHEN bu_parent.org_Name IN (N'Asia Pacific',N'Hong Kong',N'India',N'Indonesia',N'Malaysia',N'Philippines',N'Singapore',N'Taiwan',N'Thailand') 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 IN (N'Australia',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'New Zealand' THEN isnull((Select org_Id from @benList where org_name = N'Pacific'),bu_parent.org_Id)
WHEN bu_parent.org_Name IN (N'Japan',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'Korea' THEN isnull((Select org_Id from @benList where org_name = N'Japan/Korea'),bu_parent.org_Id)
WHEN bu_parent.org_Name IN (N'Asia Pacific',N'Hong Kong',N'India',N'Indonesia',N'Malaysia',N'Philippines',N'Singapore',N'Taiwan',N'Thailand')
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 = ct.child_parent_work_id) as parent,
(Select work_id from view_work par where par.work_id = ct.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 = ct.work_id_tag and tagset_name = '05. Project Type') as conv_project_type,
(Select value from view_custom_field where object_id = ct.work_id_tag and name = 'Project Tracking Number') as proj_track_no,
(Select first_name + ' ' + last_name from view_user where user_id = ct.child_owner_id) as project_mgr,
(Select user_id from view_user where user_id = ct.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 = ct.work_id_tag
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 = ct.work_id_tag
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 = ct.work_id_tag
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 = ct.work_id_tag
and role like 'ConfRole%' and crd.name = 'Project Sponsor' FOR XML PATH('')) as proj_spons_id,
(Select name from view_work where work_id = ct.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
Cte_Cross_Tab ct --on w.child_work_id=ct.work_id_tag
INNER JOIN dbo.View_Metric_Instance mi on ct.work_id_tag= 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 ('fs000080000gjt6ohd5g000000','fs000080000j9tr6udog000000','fs000080000jbbgikv70000000','fs000080000jd7ct2a10000000') and mdate between @yearstart and @prior_mo
THEN value ELSE 0 END) as calc_ytd_actfcst,
sum(CASE WHEN view_id IN ('fs000080000gjt6kd9i0000000','fs000080000j9trkt310000000','fs000080000jbbgp7kog000000','fs000080000jd7cvu31g000000') and mdate between @yearstart and @prior_mo
THEN value ELSE 0 END) as calc_ytd_bud,
sum(CASE WHEN view_id IN ('fs000080000gjt6ohd5g000000','fs000080000j9tr6udog000000','fs000080000jbbgikv70000000','fs000080000jd7ct2a10000000') and mdate between @prior_yearstart and @prior_yearend
THEN value ELSE 0 END) as calc_prior_fy_act,
sum(CASE WHEN view_id IN ('fs000080000gjt6ohd5g000000','fs000080000j9tr6udog000000','fs000080000jbbgikv70000000','fs000080000jd7ct2a10000000') and mdate between @yearstart and @yearend
THEN value ELSE 0 END) as calc_cur_fy_actfcst,
sum(CASE WHEN view_id IN ('fs000080000gjt6ohd5g000000','fs000080000j9tr6udog000000','fs000080000jbbgikv70000000','fs000080000jd7ct2a10000000') and mdate between @next_yearstart and @next_yearend
THEN value ELSE 0 END) as calc_nxt_fy_act,
sum(CASE WHEN view_id IN ('fs000080000gjt6kd9i0000000','fs000080000j9trkt310000000','fs000080000jbbgp7kog000000','fs000080000jd7cvu31g000000') and mdate between @prior_yearstart and @prior_yearend
THEN value ELSE 0 END) as calc_prior_fy_bud,
sum(CASE WHEN view_id IN ('fs000080000gjt6kd9i0000000','fs000080000j9trkt310000000','fs000080000jbbgp7kog000000','fs000080000jd7cvu31g000000') and mdate between @yearstart and @yearend
THEN value ELSE 0 END) as calc_cur_fy_bud,
sum(CASE WHEN view_id IN ('fs000080000gjt6kd9i0000000','fs000080000j9trkt310000000','fs000080000jbbgp7kog000000','fs000080000jd7cvu31g000000') 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 ('fs000080000gjt6ohd5g000000','fs000080000j9tr6udog000000','fs000080000jbbgikv70000000','fs000080000jd7ct2a10000000') and mdate between @yearstart and @prior_mo
THEN value ELSE 0 END))-(sum(CASE
WHEN view_id IN ('fs000080000gjt6kd9i0000000','fs000080000j9trkt310000000','fs000080000jbbgp7kog000000','fs000080000jd7cvu31g000000') and mdate between @yearstart and @prior_mo
THEN value ELSE 0 END))) as calc_ytd_actfcst_bud,
((Sum(CASE WHEN view_id IN ('fs000080000gjt6ohd5g000000','fs000080000j9tr6udog000000','fs000080000jbbgikv70000000','fs000080000jd7ct2a10000000') and mdate between @yearstart and @yearend
THEN value ELSE 0 END))-(sum(CASE
WHEN view_id IN ('fs000080000gjt6kd9i0000000','fs000080000j9trkt310000000','fs000080000jbbgp7kog000000','fs000080000jd7cvu31g000000') 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 ('fs000080000gjt6ohd5g000000','fs000080000j9tr6udog000000','fs000080000jbbgikv70000000','fs000080000jd7ct2a10000000','fs000080000gjt6kd9i0000000','fs000080000j9trkt310000000',
'fs000080000jbbgp7kog000000','fs000080000jd7cvu31g000000')
AND m.beneficiary_id IS NOT NULL
WHERE mi.metric_template_id IN ('fs000080000gjt6kd8i0000000','fs000080000j9tr6ubbg000000','fs000080000jbbgikq70000000','fs000080000jd7ct29jg000000')
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 = ct.work_id_tag and metric.beneficiary_id = ytd.beneficiary_id and ytd.item_name = metric.name
where
mi.metric_template_id IN ('fs000080000gjt6kd8i0000000','fs000080000j9tr6ubbg000000','fs000080000jbbgikq70000000','fs000080000jd7ct29jg000000')and metric.beneficiary_ID is not null
and bu_parent.org_Name IN (Select distinct org_name from @benList where org_id IN ('fs000080000gjt7fl2h0000000'))
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 ('fs000080000gjt6r0hmg000000')))
and ct.child_depth = 0
)X
WHERE top_parent IN ('Ecolab', 'WinningasOne', 'Renaissance', 'GetFIT')
AND conv_project_type IN ('Non-LSS','LSS')
AND region IN ('North America')
AND
(
(@mgmtdept = 0)
OR
(@mgmtdept = 1 AND manage_dept IN ('GSC - Manufacturing','Div - Finance'))
)
group by top_parent, work_id_tag, child_seq, 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 16, 2012 at 7:17 am
A couple of things to add
1) There is a table variable called @benList. I would try converting this to a temp table. The optimizer will only count a table variable as having one row.
2) There are two function calls to fn_advancedMetrics. Since you are passing variables to this function you should get a performance bump if you pass the results of the function to a temp table and use it in your joins.
November 16, 2012 at 7:39 am
1) There is a table variable called @benList. I would try converting this to a temp table. The optimizer will only count a table variable as having one row.
Ans: Not permitted to use temp table (#temp table) in our DB.
2) There are two function calls to fn_advancedMetrics. Since you are passing variables to this function you should get a performance bump if you pass the results of the function to a temp table and use it in your joins.
Ans :(results of the function to a temp table and use it in your joins.) I didnt get u properly. if u can give an example then it will be good.
November 16, 2012 at 7:41 am
Charles Hearn (11/16/2012)
A couple of things to add1) There is a table variable called @benList. I would try converting this to a temp table. The optimizer will only count a table variable as having one row.
2) There are two function calls to fn_advancedMetrics. Since you are passing variables to this function you should get a performance bump if you pass the results of the function to a temp table and use it in your joins.
If you use statement level recompile, a table variable will be recognized as having more that one row but, I agree with the idea of using a Temp Table instead because evenn if a more-than-one row count can be realized from a table variable, it still can't use statistics.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 16, 2012 at 10:20 am
farooq.hbs (11/16/2012)
1) There is a table variable called @benList. I would try converting this to a temp table. The optimizer will only count a table variable as having one row.Ans: Not permitted to use temp table (#temp table) in our DB.
Wow. Can you provide more details?
2) There are two function calls to fn_advancedMetrics. Since you are passing variables to this function you should get a performance bump if you pass the results of the function to a temp table and use it in your joins.
Ans :(results of the function to a temp table and use it in your joins.) I didnt get u properly. if u can give an example then it will be good.
--Do this only once
SELECT * -- add column list
INTO #Metric
FROM dbo.fn_advancedMetrics(@prior_yearstart,@next_yearend)
--join to the temp table each time instead of making the function call
INNER JOIN #Metric as metric ON mi.metric_instance_id = metric.metric_instance_id
However since you can't use temp tables I'm not sure how this will help.
November 16, 2012 at 11:49 am
I'll take a flyer having just scanned briefly the stored procedure in the OP.
Ideas to consider:
1. As someone said above: "divide and conquer" -- that is, see if you can turn some of the sub-queries into table-valued functions and then use CROSS APPLY (or OUTER APPLY) to join the results. You can also sometimes use CROSS APPLY with the sub-queries themselves (without a tvf) and that MAY benefit performance by whittling down the number of rows within the various inner queries before they are processed by an outer query.
2. Avoid using any scalar functions with the queries themselves. I'm not sure if you are doing that, but that can cause RBAR hell.
3. The stored procedure is mixing DDL and DML statements. (DECLARE @x; SET @x=1; DECLARE @y; SET @y=1; etc) Your procedure is quite complex and it's possible that you are having recompilation issues either from mixing the DDL and DML or due to other issues within the queries themselves. Here's a link that may give you some ideas to explore: Troubleshooting stored procedure recompilation
November 16, 2012 at 2:50 pm
farooq.hbs (11/16/2012)
1) There is a table variable called @benList. I would try converting this to a temp table. The optimizer will only count a table variable as having one row.Ans: Not permitted to use temp table (#temp table) in our DB.
2) There are two function calls to fn_advancedMetrics. Since you are passing variables to this function you should get a performance bump if you pass the results of the function to a temp table and use it in your joins.
Ans :(results of the function to a temp table and use it in your joins.) I didnt get u properly. if u can give an example then it will be good.
Missed this because I was apparently typing when you posted.
The idea of not being permitted to use Temp Tables in your database is a bit of a strange requirement by the people who control the database. Apparently they're unaware that Table Variables and Temp Tables both "live" in TempDB and that both start out in memory and only spill to disk if they get too big for memory.
In other words, Table Variables are NOT "memory only" and Temp Tables are NOT "disk only".
Further, some of the highest performane code I've ever written has used Temp Tables. I'd be happy to explain to your DBAs if they'd actually listen.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 19, 2012 at 10:46 am
Jeff Moden (11/16/2012)
DiverKas (11/15/2012)
I am curious though Jeff, what is this storing of the datatype magic you speak of? :w00t:What's the one datatype that most people have never used and many don't even know exists? Worse yet, if they do know it exits, they've never read about how it stores meta-data which is the reason why it has a maximum length of 8,016 (8,000 max for the base datatype).
SQL_VARIANT
Even with a max length of 8,016, that still leaves room in the mythical 8,060 row for a couple of IDs and a couple of dates without anything going "out of row" (which, of course, is less of a problem since 2k5). It makes for a great EAV-style audit table.
Ah yes, but I used to rule it out when I was a client side developer because of the lack of ODBC support. SSRS barks at it for generating reports as well in pre 2008 R2. But your point is valid.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply