September 9, 2011 at 3:42 pm
Hi all,
I ran a query and it took almost 21 seconds. But I used merge join
and end up in 6 seconds.
Select *
table a
inner merge join table b on a.id = b.id
By using this merge or hash join cause any overhead work load? I am very new to SQL Server and I am using 2000, 2005 server.
Thank you very much
Dave
September 9, 2011 at 4:01 pm
Join hints (and just about all other hints) are for when you are absolutely, completely, 100% sure you know better than the query optimiser, and you know why the optimiser is not selecting the plans you want.
Otherwise, stay away from them. Far too easy to degrade performance badly.
As for why the difference in this case, could be any number of reasons. Did you run both queries multiple times and is the performance difference consistent?
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
September 9, 2011 at 4:33 pm
I saw on the the SQLShare Demos and they used a 3 hints.
They ran profiler, executed the queried and showed what the best hint was in this scenario.
Perhaps that was the exception rather than norm.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 10, 2011 at 1:47 am
I do performance tuning for a number of clients, have done for over 5 years. I can count on one hand the number of times I've needed a join or index hint.
I've used things like Optimise For, Option Recompile and maxdop hints a little more often, but almost never index or join hints.
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
September 10, 2011 at 2:01 am
What are some of the more common techniques that you use when you performance tune?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 10, 2011 at 2:38 am
Fix non-SARGable predicates, add indexes, fix catch-all queries (very common), suggest application rewrites (to stop being chatty), simplify SQL code, remove cursors.
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
September 10, 2011 at 8:41 am
GilaMonster (9/10/2011)
Fix non-SARGable predicates, add indexes, fix catch-all queries (very common), suggest application rewrites (to stop being chatty), simplify SQL code, remove cursors.
Another one that is sometimes very useful is to add non-key columns to an existing non-clustered index (to eliminate RID or bookmark lookups).
Tom
September 10, 2011 at 9:46 am
replace 'Add indexes' with 'add, widen and drop indexes'.
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
September 10, 2011 at 12:12 pm
If you find SQL Server "miss" when it chooses between loop, merge and hash join, I would have a look at the Actual Execution plan (deviation between estimated and actual number of rows or executions), and the statistics possibly causing the query optimizer to miss.
September 11, 2011 at 3:45 am
For Server Performance I had an Audit Checklist which I used as a aid in identifying performance bottlenecks. I was very methodical.
Unfortunately I have not bee able to locate it on the Web.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply