TSQL Optimization - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

TSQL Optimization

Author

Topic

ashwani30th
Starting Member

1 Post

Posted - 2012-10-07 : 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.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-10-07 : 13:35:41

Could you also post - the Execution Plan

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-07 : 14:01:20

did you check the execution plan? what does it suggest?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources