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 2012 Forums
 Transact-SQL (2012)
 Please Help with SQL hash join

Author  Topic 

xiebo2010cx
Starting Member

4 Posts

Posted - 2013-09-29 : 20:46:58

SQL Gurus, please help with my prod SQL join performance. it is really killing me...


Table combineddata has 15 million rows
Table tblSpreadsStage1 has 70million rows

The below join takes too long, checked the execution plan, HASH MATCH takes 90% execution time.

1. already have index on join columns and where columns, want to seek the best ideas from Gurus on indexing here.
2. how can I re-write the the query to make it more efficient. now HASH JOIN takes 90% execution time...

SQL query plan is attached, please change to .sqlplan post-fix before opening it.

Thank you so, so much!!!

SELECT co1.cusip ,
AVG(co1.Yield) AS cusip1avgyield,
STDEV(co1.Yield) AS cusip1stddev,

co2.cusip AS cusip2 ,
AVG(co2.Yield) AS cusip2avgyield,
STDEV(co2.Yield) AS cusip2stddev,

COUNT(co1.cusip + co2.cusip) AS datacount ,
AVG(co1.Yield - co2.Yield) AS average ,

CASE WHEN STDEV(co1.Yield - co2.Yield) = 0
THEN .0000000000000001
ELSE ISNULL(STDEV(co1.Yield - co2.Yield),
.0000000000000001)
END AS standarddev

INTO [tblSpreadsStage2]
FROM dbo.combineddata co1 ,
dbo.combineddata co2 ,
dbo.tblSpreadsStage1 o
WHERE co1.Date = co2.date
AND co1.cusip = o.cusip
AND co2.Cusip = o.cusip2
AND co1.yield IS NOT NULL
AND co2.yield IS NOT NULL
AND co1.Yield != 0
AND co2.Yield != 0
-- and co1.Yield != co2.Yield
GROUP BY co1.cusip ,
co2.cusip

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-30 : 09:52:53
what does execution plan suggest?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-30 : 09:55:34
Also the way you've written it currently, the join may result in RBAR Triangular join

Make sure you read this

http://www.sqlservercentral.com/articles/T-SQL/61539/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

xiebo2010cx
Starting Member

4 Posts

Posted - 2013-09-30 : 12:33:05
thank you, visakhm.

The execution plan suggests 90% Hash Match, I am thinking to re-write the query to improve the query performance. Any help is greatly appreciated.

quote:
Originally posted by visakh16

what does execution plan suggest?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-03 : 23:26:33
how about inner loop join??
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-03 : 23:41:52
sorry, it might possible that you does not have index in the table...
Go to Top of Page
   

- Advertisement -