March 2, 2012 at 11:19 pm
Hi,
I am having some serious issue while loading Staging Table from Data Store. My query to load Staging table is taking more then 24 hours to execute.The three main tables are item_ledger_entry = 12 Million,Value_Entry=28 Million and Gl_Entry = 58 Million. The issue is Group By clause with is taking 95% CUP cost for SORT.
Please Help.
Thanks in Advance.
Following is my Query.
SELECT
dim_company.dim_company_key,
ISNULL(dim_location.dim_location_key,0) dim_location_key,
ISNULL(dim_item.dim_item_key,0) dim_item_key,
ISNULL(dim_posting_date.dim_posting_date_key,0) dim_posting_date_key,
ISNULL(dim_commencement_date.dim_commencement_date_key,0) dim_commencement_date_key,
ISNULL(dim_discontinued_date.dim_discontinued_date_key,0) dim_discontinued_date_key,
ISNULL(dim_season.dim_season_key,0) dim_season_key,
ISNULL(dim_currency.dim_currency_key,0) dim_currency_key,
dim_days_aging.dim_aging_days_key,
dim_days_in_range.dim_range_days_key,
ods_item_ledger_entry.company_code,
ods_item_ledger_entry.entry_no_,
ods_item_ledger_entry.item_no_,
ods_item_ledger_entry.document_no_,
ods_item_ledger_entry.posting_date,
ods_item_ledger_entry.entry_type,
ods_item_ledger_entry.source_no_,
ods_item.season,
ods_item_ledger_entry.description,
ods_item_ledger_entry.location_code,
ods_item.vendor_no_,
ods_item.commencement_date,
work_item_current_status.discontinued_date,
dim_company.local_currency,
ods_item_ledger_entry.quantity,
ods_item_ledger_entry.invoiced_quantity,
ods_item_company.Unit_Cost,
ods_item_company.unit_cost / ISNULL(dim_exchange_rate.exchange_rate_amount,1),
ods_item_ledger_entry.quantity * ods_item_company.unit_cost cost_amount,
(ods_item_ledger_entry.quantity * ods_item_company.unit_cost) / ISNULL(dim_exchange_rate.exchange_rate_amount,1) cost_amount_NZD,
SUM(ods_value_entry.cost_per_unit) cost_per_unit,
SUM(ods_value_entry.cost_per_unit) / ISNULL(dim_exchange_rate.exchange_rate_amount,1) cost_per_unit_NZD,
dim_exchange_rate.exchange_rate_amount,
SUM(ods_gl_entry.amount) cogs_amount,
SUM(ods_gl_entry.amount) / ISNULL(dim_exchange_rate.exchange_rate_amount,1) cogs_amount_NZD,
ods_item_company.Unit_Price,
ods_item_company.unit_price / ISNULL(dim_exchange_rate.exchange_rate_amount,1) unit_price_NZD,
ods_item_ledger_entry.quantity * ods_item_company.unit_price inventory_value,
(ods_item_ledger_entry.quantity * ods_item_company.unit_price ) / ISNULL(dim_exchange_rate.exchange_rate_amount,1) inventory_value_NZD,
ISNULL(DATEDIFF(d, COALESCE( work_item_current_status.discontinued_date ,GETDATE()),GETDATE()),0) days_aging,
ISNULL(DATEDIFF(d, ods_item.commencement_date ,COALESCE( work_item_current_status.discontinued_date ,GETDATE())),0) days_in_range,
@v_dss_update_time
FROM
ods_item_ledger_entry with (NOLOCK)
left outer JOIN
ods_item with (NOLOCK)
ON ods_item_ledger_entry.item_no_ = ods_item.no_
LEFT outer JOIN
dim_company with (NOLOCK)
ON ods_item_ledger_entry.company_code = dim_company.company_code
LEFT outer JOIN
ods_item_company with (NOLOCK)
ON ods_item_ledger_entry.company_code = ods_item_company.company_code
AND ods_item_ledger_entry.item_no_ = ods_item_company.no_
LEFT outer JOIN
dim_exchange_rate with (NOLOCK)
ON dim_company.local_currency = dim_exchange_rate.currency_code
AND ods_item_ledger_entry.posting_date between dim_exchange_rate.start_date and dim_exchange_rate.end_date
LEFT outer JOIN
ods_value_entry with (NOLOCK)
ON ods_item_ledger_entry.entry_no_ = dbo.ods_value_entry.item_ledger_entry_no_
LEFT outer JOIN
dim_gl_total_account with (NOLOCK)
ON ods_item_ledger_entry.company_code = dim_gl_total_account.company_code
AND 'Total Cost of Sales' = dim_gl_total_account.gl_account_name
LEFT outer JOIN
ods_gl_entry with (NOLOCK)
ON ods_value_entry.g_l_entry_no___bal__account_ = ods_gl_entry.entry_no_
AND ods_gl_entry.g_l_account_no_ between dim_gl_total_account.gl_account_start and dim_gl_total_account.gl_account_end
LEFT outer JOIN
work_item_current_status with (NOLOCK)
ON ods_item.no_ = work_item_current_status.item_number
LEFT outer JOIN
dim_days_aging with (NOLOCK)
ON ISNULL(DATEDIFF(d, COALESCE( work_item_current_status.discontinued_date ,GETDATE()),GETDATE()),0) = dim_days_aging.dim_aging_days_key
LEFT outer JOIN
dim_days_in_range with (NOLOCK)
ON ISNULL(DATEDIFF(d, ods_item.commencement_date ,COALESCE( work_item_current_status.discontinued_date ,GETDATE())),0) = dim_days_in_range.dim_range_days_key
LEFT OUTER JOIN dim_location with (NOLOCK)
ON ods_item_ledger_entry.company_code = dim_location.company_code
AND ods_item_ledger_entry.location_code = dim_location.location_code
LEFT OUTER JOIN dim_item with (NOLOCK)
ON ods_item_ledger_entry.item_no_ = dim_item.item_number
LEFT OUTER JOIN dim_posting_date with (NOLOCK)
ON CONVERT(DATETIME,(CONVERT(VARCHAR,ods_item_ledger_entry.posting_date,112))) = dim_posting_date.posting_date
LEFT OUTER JOIN dim_commencement_date with (NOLOCK)
ON CONVERT(DATETIME,(CONVERT(VARCHAR,ods_item.commencement_date,112))) = dim_commencement_date.commencement_date
LEFT OUTER JOIN dim_discontinued_date with (NOLOCK)
ON CONVERT(DATETIME,(CONVERT(VARCHAR,work_item_current_status.discontinued_date,112))) = dim_discontinued_date.discontinued_date
LEFT OUTER JOIN dim_season with (NOLOCK)
ON ods_item_ledger_entry.company_code = dim_season.company_code
AND ods_item.season = dim_season.season_code
LEFT OUTER JOIN dim_currency with (NOLOCK)
ON dim_company.local_currency = dim_currency.currency_code
GROUP BY
ods_item_ledger_entry.entry_no_
, ods_item_ledger_entry.item_no_
, ods_item_ledger_entry.posting_date
, ods_item_ledger_entry.entry_type
, ods_item_ledger_entry.source_no_
, ods_item_ledger_entry.document_no_
, ods_item_ledger_entry.description
, ods_item_ledger_entry.location_code
, ods_item_ledger_entry.quantity
, ods_item_ledger_entry.invoiced_quantity
, ods_item_company.unit_price
, ods_item_company.unit_cost
, ods_item.vendor_no_
, ods_item_ledger_entry.company_code
, ods_item.commencement_date
, work_item_current_status.discontinued_date
, ods_item.season
, ISNULL(DATEDIFF(d, COALESCE( work_item_current_status.discontinued_date ,GETDATE()),GETDATE()),0)
, ISNULL(DATEDIFF(d, ods_item.commencement_date ,COALESCE( work_item_current_status.discontinued_date ,GETDATE())),0)
, dim_location.dim_location_key
, dim_commencement_date.dim_commencement_date_key
, dim_discontinued_date.dim_discontinued_date_key
, dim_item.dim_item_key
, dim_posting_date.dim_posting_date_key
, dim_season.dim_season_key
, dim_company.dim_company_key
, dim_exchange_rate.exchange_rate_amount
, dim_currency.dim_currency_key
, dim_company.local_currency
, dim_days_aging.dim_aging_days_key
, dim_days_in_range.dim_range_days_key
March 3, 2012 at 11:13 am
Please post the execution plan to analyze the query.
March 5, 2012 at 12:50 am
Maybe you should try this in the TSQL forums. A lot of experts monitor those.
ps: read the link in my signature about posting questions. You'll receive answers a lot faster.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply