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 2012 Forums
 Transact-SQL (2012)
 Please Help with SQL hash join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xiebo2010cx
Starting Member

China
4 Posts

Posted - 09/29/2013 :  20:46:58  Show Profile  Reply with Quote

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

India
52325 Posts

Posted - 09/30/2013 :  09:52:53  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 09/30/2013 :  09:55:34  Show Profile  Reply with Quote
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

China
4 Posts

Posted - 09/30/2013 :  12:33:05  Show Profile  Reply with Quote
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

Malaysia
965 Posts

Posted - 10/03/2013 :  23:26:33  Show Profile  Reply with Quote
how about inner loop join??
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 10/03/2013 :  23:41:52  Show Profile  Reply with Quote
sorry, it might possible that you does not have index in the table...
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.07 seconds. Powered By: Snitz Forums 2000