|
ashwani30th
Starting Member
1 Posts |
 Posted - 10/07/2012 : 10:47:41
|
Hi
My query is running very slow.
Sale table have 6 million records
data item table have 1 million record
assignment chage table have 85000 record
query returning 81056 rows and taking around 5 second
select
t3.start_date_id,t3.end_date_id,T0.Source_Id,T0.Destination_id,T0.Product_id,T3.New_source_Business_id,
case when Absolute_Value is null
then
(SUM(VOLUME) OVER(PARTITION BY t3.start_date_id,t3.end_date_id,T3.Source_business_Id,T3.Destination_business_id,T3.Product_id)) * (Percentage_Value)/100
else Absolute_Value end
New_VOLUME
,Percentage_Value
FROM sales_order (nolock) T0
INNER hash JOIN data_item t1 ON T0.data_item_id=t1.id and t1.Class=46
INNER JOIN rules_Assginment_Change T3 ON t3.data_set_id=21 and T3.Source_Business_Id=T0.source_id AND T3.Destination_Business_id=T0.destination_id AND T3.Product_id=T0.product_id
and t3.start_date_id=t1.start_date_id and t3.end_date_id=t1.end_date_id
where T0.data_selection=1
and exists(
SELECT * from data_set_item_mapping T2
where T0.data_item_id=t2.org_data_item_id AND T0.Version_id=T2.Org_Version_id
and T2.class_id=t1.Class and T2.data_set_id=21
)
Please suggest what things need to do to improve the query performance.
|
|
TSQL Optimization
