March 3, 2009 at 12:46 am
hi,
can anyone tell me why this is slow
use dwh_reporting
go
checkpoint
go
dbcc dropcleanbuffers
go
dbcc freeproccache
go
SELECT
glbbm.gl_client_id AS [Client Id]
, glbbm.gl_business_unit_id AS bu_val
, glbbm.gl_object_account AS obj_ac_val
, glbbm.gl_subsidiary_account AS subsid_ac_val
, glbbm.gl_fiscal_year AS fisc_yr_val
, glbbm.gl_type_code AS ledg_type_cd
, glbbm.gl_currency_code AS currcy_cd
, company.coy_fiscal_calendar_pattern_code AS fisc_pattn_cd_val
, bu.bu_name AS [Property Name]
, bu.bu_region_code AS bu_cat_02_cd
, bu.bu_property_category_03_code AS bu_cat_03_cd
, bu.bu_region_name AS Region
, bu.bu_property_category_03_name AS [Property Category 3]
, coa.coa_account_description AS [Account DESC]
, RTRIM(ISNULL(coa.coa_client_coa_code, 'Unknown')) AS Client_COA
, ISNULL(coa.coa_client_coa_name, 'Unknown') AS Client_COA_Desc
, glbbm.gl_balance_year_budget_amount AS [YEAR Budget]
, glbbm.gl_balance_year_forecast_amount AS [YEAR Forecast]
, glbbm.gl_month_actual_amount AS MthActual
, glbbm.gl_month_budget_amount AS MthBudget
, glbbm.gl_month_forecast_amount AS MthFC
, glbbm.gl_ytd_actual_amount AS YTDActual
, glbbm.gl_ytd_budget_amount AS YTDBudget
, glbbm.gl_ytd_forecast_amount AS YTDFC
, glbbm.gl_balance_year_actual_amount AS [YEAR Actual]
, glbbm.gl_balance_year_tax_amount AS [YEAR Tax]
, glbbm.gl_balance_year_amend_budget_b2_amount AS [YEAR Amend Budget B2]
, bu.bu_region_name AS Demographic
FROMtemp_glbbm glbbm
INNER JOIN rep_business_unit bu
ONglbbm.rep_bu_key = bu.rep_bu_key
INNER JOIN rep_company company
ONglbbm.rep_coy_key = company.rep_coy_key
INNER JOIN rep_chart_of_accounts coa
ONglbbm.rep_coa_key = coa.rep_coa_key
WHEREbu.bu_client_id = @Client
ANDglbbm.gl_fiscal_year = @FinYr
ANDglbbm.gl_month_end_datetime = @Mth
ANDglbbm.gl_object_account >= '500000'
ANDglbbm.gl_type_code IN ('AA', 'AT', 'AR', 'BA', 'FC')
ANDbu.bu_region_code IN (select * from fn_rs_string_to_varchar_table(@Region, ','))
ANDbu.bu_property_category_03_code IN (select * from fn_rs_string_to_varchar_table(@PropCat3, ','))
ORDER BY coa.coa_client_coa_code
, coa.coa_account_description
, bu.bu_property_category_03_name
, bu.bu_region_name
, bu.bu_name
I have attached the plan
March 3, 2009 at 2:45 am
Try this
SELECTglbbm.gl_client_id AS [Client Id],
glbbm.gl_business_unit_id AS bu_val,
glbbm.gl_object_account AS obj_ac_val,
glbbm.gl_subsidiary_account AS subsid_ac_val,
glbbm.gl_fiscal_year AS fisc_yr_val,
glbbm.gl_type_code AS ledg_type_cd,
glbbm.gl_currency_code AS currcy_cd,
company.coy_fiscal_calendar_pattern_code AS fisc_pattn_cd_val,
bu.bu_name AS [Property Name],
bu.bu_region_code AS bu_cat_02_cd,
bu.bu_property_category_03_code AS bu_cat_03_cd,
bu.bu_region_name AS Region,
bu.bu_property_category_03_name AS [Property Category 3],
coa.coa_account_description AS [Account DESC],
COALESCE(RTRIM(coa.coa_client_coa_code), 'Unknown')) AS Client_COA,
COALESCE(coa.coa_client_coa_name, 'Unknown') AS Client_COA_Desc,
glbbm.gl_balance_year_budget_amount AS [YEAR Budget],
glbbm.gl_balance_year_forecast_amount AS [YEAR Forecast],
glbbm.gl_month_actual_amount AS MthActual,
glbbm.gl_month_budget_amount AS MthBudget,
glbbm.gl_month_forecast_amount AS MthFC,
glbbm.gl_ytd_actual_amount AS YTDActual,
glbbm.gl_ytd_budget_amount AS YTDBudget,
glbbm.gl_ytd_forecast_amount AS YTDFC,
glbbm.gl_balance_year_actual_amount AS [YEAR Actual],
glbbm.gl_balance_year_tax_amount AS [YEAR Tax],
glbbm.gl_balance_year_amend_budget_b2_amount AS [YEAR Amend Budget B2],
bu.bu_region_name AS Demographic
FROMrep_chart_of_accounts AS coa
INNER JOINtemp_glbbm AS glbbm ON glbbm.rep_coa_key = coa.rep_coa_key
AND glbbm.gl_fiscal_year = @FinYr
AND glbbm.gl_month_end_datetime = @Mth
AND glbbm.gl_object_account >= '500000'
AND glbbm.gl_type_code IN ('AA', 'AT', 'AR', 'BA', 'FC')
INNER JOINrep_business_unit AS bu ON bu.rep_bu_key = glbbm.rep_bu_key
AND bu.bu_client_id = @Client
AND @Region LIKE '%,' + bu.bu_region_code + ',%'
AND @PropCat3 LIKE '%,' + bu.bu_property_category_03_code + ',%'
INNER JOINrep_company AS company ON company.rep_coy_key = glbbm.rep_coy_key
ORDER BYcoa.coa_client_coa_code,
coa.coa_account_description,
bu.bu_property_category_03_name,
bu.bu_region_name,
bu.bu_name
N 56°04'39.16"
E 12°55'05.25"
March 3, 2009 at 5:50 am
In addition to what the others have posted, you should take a look at updating your statistics and maybe defragging your indexes. You have estimated 4 rows, but actual 86000. This means the the choices made by the optimizer don't reflect the actual data involved. That means the plan is probably bad. You've also got table scans adding to the issue. Finally, the temp table has an index, but it's being scanned and then has a RID lookup. See if you can't get a better index on it and possibly cluster that index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 3, 2009 at 11:16 am
H,
thanks for the numerous responses. On the temp table besides the identity pk column being a candidate for the clustered index could you tell me what other column(s) would be a good candidate for the clustered index. Currently on the temp table I have no clustered index.
Thanks
March 3, 2009 at 11:58 am
Looking at your execution plan, you've got RID lookups which are using a row identifier to select the rows you need because of the lack of clustered indexes on those tables.
Look at the output list of temp_glbbm's RID lookup (23% cost) and it identifies two other columns - gl_client_id and gl_object_account. Similarly, the output list of the rep_chart_of_accounts RID lookup lists coa_client_coa_code, coa_client_coa_name and coa_account_description, as previously mentioned. Start with providing indexes that minimise/remove those RID lookups, check you have statistics on appropriate columns and tweak as per the previous advice. 🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply