March 10, 2009 at 12:12 am
Hi,
I have a store proc which is taking like 10 mins to bring back 13000 rows. I have to admit that it is trying to aggregate on about 1 million rows.
I have attached a copy of my code and a copy of the execution plan. From the execution plan I cannot see what is wrong but that's most probably because I have looked at it for so long.
any help is appreciated.
DECLARE @locFinYr varchar(50)
SET @locFinYr = @FinYr - 1
print @locFinYr
SELECT
client_idAS [Client Id]
, fiscal_yearAS fisc_yr_val
, ledger_type_codeAS gl_type_code
, client_nameAS [Client Name]
, company_codeAS coy_cd_val
, business_unit_idAS bu_val
, object_accountAS obj_ac_val
, subsidiary_accountAS subsid_ac_val
, currency_codeAS currcy_cd
, fiscal_calendar_pattern_codeAS fisc_pattn_cd_val
, bu_nameAS [Property Name]
, bu_type_codeAS bu_type_cd
, description_1AS bu_1_desc
, account_descriptionAS [Account DESC]
, level_of_detail_codeAS [Level OF Detail]
, company_nameAS Company
, grouping_code_01AS COA_Cat_01_Cd
, grouping_name_01AS COA_Cat_01_Nm
, grouping_code_04AS COA_Cat_04_Cd
, grouping_name_04AS COA_Cat_04_Nm
, grouping_code_16AS COA_Cat_16_Cd
, grouping_name_16AS COA_Cat_16_Nm
, category_02_codeAS BU_Cat_02_Cd
, category_02_nameAS BU_Cat_02_Nm
, manager_codeAS bu_cat_16_cd
, manager_nameAS BU_Cat_16_Nm
, facility_manager_codeAS bu_cat_17_cd
, facility_manager_nameAS BU_Cat_17_Nm
, SUM(balance_year_budget_amount)AS [YEAR Budget]
, SUM(balance_year_forecast_amount)AS [YEAR Forecast]
, SUM(month_actual_amount)AS MthActual
, SUM(month_budget_amount)AS MthBudget
, SUM(ytd_actual_amount)AS YTDActual
, SUM(ytd_budget_amount)AS YTDBudget
, SUM(month_forecast_amount)AS MthFC
, SUM(ytd_forecast_amount)AS YTDFC
, SUM(PriorYTDActual)AS PriorYTDActual
FROM
(
SELECT
glbbm.gl_client_idAS client_id
, glbbm.gl_fiscal_year AS fiscal_year
, glbbm.gl_type_codeASledger_type_code
, bu.bu_client_nameAS client_name
, glbbm.gl_company_codeAS company_code
, glbbm.gl_business_unit_idAS business_unit_id
, glbbm.gl_object_accountAS object_account
, glbbm.gl_subsidiary_accountAS subsidiary_account
, glbbm.gl_currency_codeAS currency_code
, company.coy_fiscal_calendar_pattern_codeAS fiscal_calendar_pattern_code
, bu.bu_name AS bu_name
, bu.bu_type_code ASbu_type_code
, bu.bu_description_1 AS description_1
, coa.coa_account_descriptionAS account_description
, coa.coa_level_of_detail_code AS level_of_detail_code
, company.coy_nameAS company_name
, coa.coa_grouping_code_01 AS grouping_code_01
, coa.coa_grouping_name_01 AS grouping_name_01
, coa.coa_grouping_code_04 AS grouping_code_04
, coa.coa_grouping_name_04 AS grouping_name_04
, coa.coa_grouping_code_16 AS grouping_code_16
, coa.coa_grouping_name_16 AS grouping_name_16
, bu.bu_category_02_codeAS category_02_code
, bu.bu_category_02_nameAS category_02_name
, bu.bu_manager_codeAS manager_code
, bu.bu_manager_nameAS manager_name
, bu.bu_facility_manager_codeAS facility_manager_code
, bu.bu_facility_manager_nameAS facility_manager_name
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_balance_year_budget_amount
ENDASbalance_year_budget_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_balance_year_forecast_amount
ENDASbalance_year_forecast_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_month_actual_amount
ENDASmonth_actual_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_month_budget_amount
ENDAS month_budget_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_ytd_actual_amount
ENDAS ytd_actual_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_ytd_budget_amount
ENDASytd_budget_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_month_forecast_amount
ENDAS month_forecast_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_ytd_forecast_amount
ENDASytd_forecast_amount
, CASE
WHEN glbbm.gl_type_code = 'AA'
THEN CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN glbbm.gl_month_balance_ytd_amount
ELSE 0
END
ENDAS PriorYTDActual
, gl_month_end_datetime
FROM temp_glbbm glbbm (NOLOCK)
INNER JOIN rep_business_unit bu (NOLOCK)
ON glbbm.rep_bu_key = bu.rep_bu_key
INNER JOIN rep_company company (NOLOCK)
ON glbbm.rep_coy_key = company.rep_coy_key
INNER JOIN rep_chart_of_accounts coa (NOLOCK)
ON glbbm.rep_coa_key = coa.rep_coa_key
WHEREbu.bu_client_id IN ('000', '001')
ANDglbbm.gl_fiscal_year >= @locFinYr
ANDglbbm.gl_fiscal_year <= @FinYr
AND glbbm.gl_type_code IN ('AA' , 'BA' , 'FC')
ANDglbbm.rep_gl_delete_flag = 'N'
ANDglbbm.gl_company_code IN ('00011', '00013', '00015', '00016', '00020', '00021', '00022', '00023', '00024', '00026', '00900')
ANDglbbm.gl_company_code IN (SELECT * FROM fn_rs_string_to_varchar_table(@Company, ','))
ANDcoa.coa_grouping_code_01 IN (SELECT * FROM fn_rs_string_to_varchar_table(@COA_CC1, ',') )
ANDcoa.coa_grouping_code_16 IN (SELECT * FROM fn_rs_string_to_varchar_table(@COA_CC16, ',') )
ANDbu.bu_manager_code IN (SELECT * FROM fn_rs_string_to_varchar_table(@BUCC16, ',') )
ANDbu.bu_facility_manager_code IN (SELECT * FROM fn_rs_string_to_varchar_table(@BUCC17, ',') )
ANDNOT (bu.bu_category_02_code IN ('BAL','ZRO'))
ANDcoa.coa_grouping_code_04 IN ('U01' , 'U02' , 'U03')
ANDNOT (coa.coa_grouping_code_01 IN ('CAS', 'NAS', 'CLI', 'NLI'))
) AS MainData1
GROUP BY
client_id
, fiscal_year
, ledger_type_code
, bu_type_code
, client_name
, company_code
, business_unit_id
, object_account
, subsidiary_account
, currency_code
, fiscal_calendar_pattern_code
, bu_name
, description_1
, account_description
, level_of_detail_code
, company_name
, grouping_code_01
, grouping_name_01
, grouping_code_04
, grouping_name_04
, grouping_code_16
, grouping_name_16
, category_02_code
, category_02_name
, manager_code
, manager_name
, facility_manager_code
, facility_manager_name
UNION
SELECT client_idAS [Client Id]
, fiscal_yearAS fisc_yr_val
, ledger_type_codeAS ledg_type_cd
, client_nameAS [Client Name]
, company_codeAS coy_cd_val
, business_unit_idAS bu_val
, object_accountAS obj_ac_val
, subsidiary_accountAS subsid_ac_val
, currency_codeAS currcy_cd
, fiscal_calendar_pattern_codeAS fisc_pattn_cd_val
, bu_nameAS [Property Name]
, bu_type_codeAS bu_type_cd
, description_1AS bu_1_desc
, account_descriptionAS [Account DESC]
, level_of_detail_codeAS [Level OF Detail]
, company_nameAS Company
, grouping_code_01AS COA_Cat_01_Cd
, grouping_name_01AS COA_Cat_01_Nm
, grouping_code_04AS COA_Cat_04_Cd
, grouping_name_04AS COA_Cat_04_Nm
, grouping_code_16AS COA_Cat_16_Cd
, grouping_name_16AS COA_Cat_16_Nm
, category_02_codeAS BU_Cat_02_Cd
, category_02_nameAS BU_Cat_02_Nm
, manager_codeAS bu_cat_16_cd
, manager_nameAS BU_Cat_16_Nm
, facility_manager_codeAS bu_cat_17_cd
, facility_manager_nameAS BU_Cat_17_Nm
, SUM(balance_year_budget_amount)AS [YEAR Budget]
, SUM(balance_year_forecast_amount)AS [YEAR Forecast]
, SUM(month_actual_amount)AS MthActual
, SUM(month_budget_amount)AS MthBudget
, SUM(ytd_actual_amount)AS YTDActual
, SUM(ytd_budget_amount)AS YTDBudget
, SUM(month_forecast_amount)AS MthFC
, SUM(ytd_forecast_amount)AS YTDFC
, SUM(PriorYTDActual)AS PriorYTDActual
FROM
(SELECT
glbbm.gl_fiscal_year AS fiscal_year
, glbbm.gl_client_idAS client_id
, bu.bu_client_nameAS client_name
, glbbm.gl_company_codeAS company_code
, CASE
WHEN glbbm.gl_company_code = '00905'
THEN 'BHP BU'
WHEN glbbm.gl_company_code = '00906'
THEN 'RWH BU'
WHEN glbbm.gl_company_code = '00915'
THEN 'AMP BU'
ENDAS business_unit_id
, glbbm.gl_object_accountAS object_account
, glbbm.gl_subsidiary_accountAS subsidiary_account
, glbbm.gl_currency_codeAS currency_code
, company.coy_fiscal_calendar_pattern_codeAS fiscal_calendar_pattern_code
, CASE
WHEN glbbm.gl_company_code = '00905'
THEN 'BHP Properties'
WHEN glbbm.gl_company_code = '00906'
THEN 'RWH Properties'
WHEN glbbm.gl_company_code = '00915'
THEN 'AMP Properties'
ENDAS bu_name
, ''ASbu_type_code
, CASE
WHEN glbbm.gl_company_code = '00905'
THEN 'BHP BUs'
WHEN glbbm.gl_company_code = '00906'
THEN 'RWH BUs'
WHEN glbbm.gl_company_code = '00915'
THEN 'AMP BUs'
ENDAS description_1
, coa.coa_account_descriptionAS account_description
, coa.coa_level_of_detail_code AS level_of_detail_code
, company.coy_nameAS company_name
, coa.coa_grouping_code_01 AS grouping_code_01
, coa.coa_grouping_name_01 AS grouping_name_01
, coa.coa_grouping_code_04 AS grouping_code_04
, coa.coa_grouping_name_04 AS grouping_name_04
, coa.coa_grouping_code_16 AS grouping_code_16
, coa.coa_grouping_name_16 AS grouping_name_16
, bu.bu_category_02_codeAS category_02_code
, bu.bu_category_02_nameAS category_02_name
, bu.bu_manager_codeAS manager_code
, bu.bu_manager_nameAS manager_name
, bu.bu_facility_manager_codeAS facility_manager_code
, bu.bu_facility_manager_nameAS facility_manager_name
, glbbm.gl_type_codeASledger_type_code
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_balance_year_budget_amount
ENDASbalance_year_budget_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_balance_year_forecast_amount
ENDASbalance_year_forecast_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_month_actual_amount
ENDASmonth_actual_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_month_budget_amount
ENDAS month_budget_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_ytd_actual_amount
ENDAS ytd_actual_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_ytd_budget_amount
ENDASytd_budget_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_month_forecast_amount
ENDAS month_forecast_amount
, CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN 0
ELSE glbbm.gl_ytd_forecast_amount
ENDASytd_forecast_amount
, CASE
WHEN glbbm.gl_type_code = 'AA'
THEN CASE
WHEN glbbm.gl_month_end_datetime = DATEADD(yy, - 1, DATEADD(mm, DATEDIFF(m, 0, @mth) + 1, - 1))
THEN glbbm.gl_month_balance_ytd_amount
ELSE 0
END
ENDAS PriorYTDActual
FROM temp_glbbm glbbm (NOLOCK)
INNER JOIN rep_business_unit bu (NOLOCK)
ON glbbm.rep_bu_key = bu.rep_bu_key
INNER JOIN rep_company company (NOLOCK)
ON glbbm.rep_coy_key = company.rep_coy_key
INNER JOIN rep_chart_of_accounts coa (NOLOCK)
ON glbbm.rep_coa_key = coa.rep_coa_key
WHEREglbbm.gl_fiscal_year >= @locFinYr
ANDglbbm.gl_fiscal_year <= @FinYr
AND glbbm.gl_type_code IN ('AA' , 'BA' , 'FC')
ANDglbbm.gl_company_code IN ('00100','00200','00300')
ANDglbbm.gl_company_code IN (SELECT * FROM fn_rs_string_to_varchar_table(@Company, ','))
ANDcoa.coa_grouping_code_01 IN (SELECT * FROM fn_rs_string_to_varchar_table(@COA_CC1, ',') )
ANDcoa.coa_grouping_code_16 IN (SELECT * FROM fn_rs_string_to_varchar_table(@COA_CC16, ',') )
ANDbu.bu_manager_code IN (SELECT * FROM fn_rs_string_to_varchar_table(@BUCC16, ',') )
ANDbu.bu_facility_manager_code IN (SELECT * FROM fn_rs_string_to_varchar_table(@BUCC17, ',') )
ANDNOT(bu.bu_category_02_code IN ('BAL', 'ZRO'))
ANDcoa.coa_grouping_code_04 IN ('U01' , 'U02' , 'U03')
ANDNOT( coa.coa_grouping_code_01 IN ('CAS', 'NAS', 'CLI', 'NLI'))
) AS MainData2
GROUP BY client_id
, fiscal_year
, ledger_type_code
, bu_type_code
, client_name
, company_code
, business_unit_id
, object_account
, subsidiary_account
, currency_code
, fiscal_calendar_pattern_code
, bu_name
, description_1
, account_description
, level_of_detail_code
, company_name
, grouping_code_01
, grouping_name_01
, grouping_code_04
, grouping_name_04
, grouping_code_16
, grouping_name_16
, category_02_code
, category_02_name
, manager_code
, manager_name
, facility_manager_code
, facility_manager_name
I understand this query is not the prettiest but it is what is required by the business.
This function fn_rs_string_to_varchar_table is used to change a parameter like 'a,b,c,d,e' into a table because trying to do like against the parameter produces a result like
where type_code in ('a,b,c,d,e')
instead of
where type_code in ('a','b','c','d','e')
I have a clustered index on my temp table on fiscal_year, client id, month and object account
March 10, 2009 at 3:52 am
I see lots of table scans: can't you get rid of them with some kind of index?
-- Gianluca Sartori
March 10, 2009 at 3:58 am
Hi,
but the table scan uses very little resources.
March 10, 2009 at 3:58 am
You might be able avoid the key lookup (28%) by including the following columns on your index
[DWH_Reporting].[dbo].[rep_business_unit].rep_bu_key, [DWH_Reporting].[dbo].[rep_business_unit].bu_client_name, [DWH_Reporting].[dbo].[rep_business_unit].bu_manager_name, [DWH_Reporting].[dbo].[rep_business_unit].bu_facility_manager_name, [DWH_Reporting].[dbo].[rep_business_unit].bu_category_02_name
Also, can the union be a union all rather than a union?
March 10, 2009 at 4:05 am
As David mentioned already, you have a couple of key lookups that I would want to address as IO can be pretty expensive and it appears that most of your cost according to the plan appears to be with those.
Another thought would be to take the execution of function fn_rs_string_to_varchar_table that you have in the where clause and move that to the top of the query and put those into #temp tables and join to them. I have found that the optimizer likes that a bit more and typically you can work with the query a bit more when you do it that way.
Of course, the "NOT IN"'s can be a touch expensive as well when you are looking at large tables. 😉
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 10, 2009 at 4:13 am
chrisau168 (3/10/2009)
Hi,but the table scan uses very little resources.
Yes, it's true, but costs are relative and a cost of 0% doesn't mean the operation comes for free, especially in a very complex query. Avoiding scans is the first thing I do when I try to tune a query.
Another thing I would do is to take the constants and fixed sets out of the query: create some variables for calculated constants ad (indexed) temp tables for functions results. I think this will help a bit.
-- Gianluca Sartori
March 10, 2009 at 4:18 am
Gianluca Sartori (3/10/2009)
chrisau168 (3/10/2009)
Hi,but the table scan uses very little resources.
Yes, it's true, but costs are relative and a cost of 0% doesn't mean the operation comes for free, especially in a very complex query. Avoiding scans is the first thing I do when I try to tune a query.
Another thing I would do is to take the constants and fixed sets out of the query: create some variables for calculated constants ad (indexed) temp tables for functions results. I think this will help a bit.
The table scans are actually the execution of the function and I would try to do that differently as I mentioned in my previous post. It does make things a bit easier to work with and I think you will find that the optimizer will provide a cleaner plan to work with.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 10, 2009 at 4:23 am
I completely agree with David. I didn't see his reply and I ended up saying the same things.
Gianluca
-- Gianluca Sartori
March 10, 2009 at 4:37 am
ok will take what has been said and will apply thanks for the help.
March 10, 2009 at 4:42 am
Please post back with how things work our for you. Thanks!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
March 10, 2009 at 4:45 am
Hi David,
just a quick question and forgive me if it sounds stupid. But I would like to know how did you know the 28% was a key look up. To be it said clustered index seek doesnt that mean it is using the clustered index to do the search? I am looking inside the block and the numbers next to the estimated cost, estimated io and estimated cpu are all very low.
One more thing how did you come up with that index?
I have an index for some of those items but not all of them.
March 10, 2009 at 5:00 am
chrisau168 (3/10/2009)
But I would like to know how did you know the 28% was a key look up. To be it said clustered index seek doesnt that mean it is using the clustered index to do the search?
http://sqlinthewild.co.za/index.php/2009/01/27/a-bookmark-lookup-by-any-other-name/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2009 at 5:16 am
Hi Gail,
thanks for the post. Does that apply to me because in your post the NCI and CI must point to the same table with a nested join which does not seem to be the case in my execution plan or am I missing something here.
Thanks
March 10, 2009 at 5:28 am
chrisau168 (3/10/2009)
Does that apply to me because in your post the NCI and CI must point to the same table with a nested join which does not seem to be the case in my execution plan or am I missing something here.
The key lookup doesn't always immediately follow the NC index seek. That 28% is definitly a key lookup though. This is what I see with 2008's management studio. I've highlighted the key lookup and the originating index seek
I'm guessing you're using 2005's management studio and it's either unpatched or with SP1 (the client tools, not the server), which is why you see it as a clustered index seek.
Can you run the query with SET STATISTICS IO ON and post the IO stats outputs?
One of the problems with UDFs is that the optimiser often misestimates row counts and costs, making the resulting exec plan misleading. I can see one call to that function that has estimated rows 1, actual rows 5.5 million. The percentages are based on the estimated costs and, as such, are pretty meaningless here.
What does that function do and is there any way that you can replace it with either an inline table-valued function or derived table, etc? My guess is that the function is the root of the problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 10, 2009 at 5:37 am
Could we also see the code for you string to table function.
I'd think this is a multline table valued function, which is not ideal.
Perhaps I could turn it into an in-line function, as far as I know this will make a difference as the function won't have to run for every row of the query.
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply