SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 TSQL Optimization
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ashwani30th
Starting Member

1 Posts

Posted - 10/07/2012 :  10:47:41  Show Profile  Reply with Quote
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.

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 10/07/2012 :  13:35:41  Show Profile  Visit jackv's Homepage  Reply with Quote
Could you also post - the Execution Plan

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/07/2012 :  14:01:20  Show Profile  Reply with Quote
did you check the execution plan? what does it suggest?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000