Please Help with SQL hash join

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

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

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-10-03 : 23:26:33

how about inner loop join??

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...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources