August 27, 2009 at 11:08 am
Which Join having more I/O and CPU Cyecle ?
Hash join or Merge join or Loop join?
August 27, 2009 at 11:22 am
It depends.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2009 at 11:41 am
Are you familiar with what the differences are between the three and where they are appropriate to use?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 27, 2009 at 11:59 am
If not, start with this, and check out the blog posts it links to as well.
http://sqlinthewild.co.za/index.php/2007/12/30/execution-plan-operations-joins/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2009 at 5:03 pm
Yes, i have read all the difference.
As per Loop join have more and cpu cycle i think, Am i right?
August 27, 2009 at 10:12 pm
Again that would depend. Read Gails blog that she posted earlier on this topic.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 27, 2009 at 11:10 pm
Paresh Prajapati (8/27/2009)
As per Loop join have more and cpu cycle i think, Am i right?
No you're not. It depends on what they're joining.
If one join type always used more cpu, more IO, more time there would be no reason for it to be there, no reason for it to be used. The fact that there are three join types, all used by SQL under different circumstances says that there are advantages to all three, under different circumstances.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 28, 2009 at 7:41 am
Paresh Prajapati (8/27/2009)
Yes, i have read all the difference.As per Loop join have more and cpu cycle i think, Am i right?
Test a loop join from one table with two rows to one table with ten rows, with a foreign key constraint on the columns you're joining on, then test a hash join between those two. See which is more efficient.
Try it again with two rows and 10-thousand rows.
Then try it again with 10,000 rows and 100,000 rows.
Once you've done that, you'll probably get the idea. Seeing and doing will lead to understanding.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply