May 9, 2011 at 12:32 am
Who Uses the Physical joins(Nested,Merge,Hash) of SqlServer?Why it has created?
How it is different from inner,outer,... joins?
http://www.mssqltips.com/tip.asp?tip=2115
Thanks
May 9, 2011 at 2:14 am
Those aren't instead of Inner/Outer, they are used as well as inner/outer.
They're query hints, used to force the optimiser to use a particular join type. Shouldn't in general be used unless you have a really good reason and know exactly what they do.
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
May 9, 2011 at 6:14 am
To add one thing to what Gail said:
... and you've tested them extensively on the query that you're planning to use them on.
Test, test, test and then test some more. Most hints are not needed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 9, 2011 at 6:57 am
Grant Fritchey (5/9/2011)
To add one thing to what Gail said:... and you've tested them extensively on the query that you're planning to use them on.
Test, test, test and then test some more. Most hints are not needed.
And, even after you've tested, you need to re-test if the data changes substantially.
Or just not use them.
- 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
May 9, 2011 at 7:01 am
forsqlserver (5/9/2011)
How it is different from inner,outer,... joins?
http://www.mssqltips.com/tip.asp?tip=2115%5B/quote%5D
I've used a forced Merge Join one time. Was a query that most often operated on very small data sets, but occassionally had to deal with huge data sets, and the indexes being used were ordered correctly for Merge. Because the commonest result was tiny (five or six rows), the execution plan defaulted to Nested Sets, and was fast on that, but when it hit the larger sets, it would still use Nested Sets, and it commonly got rear-ended by continental drift in these cases, and was heard screaming at glaciers to "slow down you maniacs". Forcing the join type made the smaller sets a tiny bit slower, but made the larger sets fast enough for actual use. Overall, it was worth the tradeoff.
That's the only time I've ever forced a join hint, in 10 years of coding T-SQL. Never needed to any other time.
- 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
May 9, 2011 at 7:05 am
May I know what other internals tasks of query optimizer...
Thanks
May 9, 2011 at 7:08 am
forsqlserver (5/9/2011)
May I know what other internals tasks of query optimizer...
There have been books written on that. Literally.
Might be a little too advanced at this point. I'd suggest focus on more basic stuff for now, internals when you have a good understanding of the basics.
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
May 9, 2011 at 7:15 am
Thanks Gail...u really caring for sql guys...
Thanks
May 9, 2011 at 7:23 am
If you really do want to learn the internals, Gail is right (something you can just say about most things, most of the time), Benjamin's book is a great place to go. You can also look at Kalen Delaney's book SQL Server 2008 Internals and, not quite as thorough, but very approachable and a great place to start, Christian Bolton's SQL Server 2008 Internals & Troubleshooting. If you're interested in how to interpret what's happening with the optimizer, you need to get into execution plans. Look at the books in my signature below for that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 9, 2011 at 11:22 pm
Thanks...Manytimes u got in the book is easy than to search on Internet...:-)
Thanks
May 9, 2011 at 11:34 pm
Forcing join type is also useful when you are looking for ways to optimize a query and you would like to see what's missing to get a more efficient join.
Maybe the index could be ordered in another way, a column added or some small things changed that would not have much impact anywhere else but could improve the situation in this case.
May 10, 2011 at 12:32 am
another thing to keep in mind with any hints is :
As with data volume changes, you'll also have to check their working and the need for them every time you apply an hotfix, cumulative update package or service pack !
Of course, with upgrades to a new rtm of sqlserver, these will also need extra attention.
Sooner or later Hints can and will bite you in the back.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply