March 11, 2014 at 10:29 pm
I have written my query like this:
select a.ID, a.Name
from
Table2 b
INNER JOIN Table3 c on b.ID = c.ID
INNER JOIN Table4 d on c.ID = d.ID
INNER JOIN Table1 a on d.ID = a.ID
The guy who reviewed my code that there will be performance enhancement if I used this way.
select a.ID, a.Name
from Table1 a
INNER JOIN Table2 b on a.ID = b.ID
INNER JOIN Table3 c on b.ID = c.ID
INNER JOIN Table4 d on c.ID = d.ID
Any valuable suggestion on this:
Also will it have any performance enhancement if I convert this query into a stored proc.
March 12, 2014 at 12:51 am
did you find any difference in execution query plan after this change ?
March 12, 2014 at 3:06 am
Query Cost is same for both the queries. It does not involve any table scans in both the queries.
Execution query plan is the same.
What about the performance enhancement when converted into stored proc?
March 12, 2014 at 3:14 am
execution plan will be same in a procedure as well.
March 12, 2014 at 3:20 am
Junglee_George (3/11/2014)
The guy who reviewed my code that there will be performance enhancement if I used this way.
Did he explain why he believes that?
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
March 12, 2014 at 3:26 am
Based on what you're showing, those two queries are identical. There are so few tables the optimizer is going to rearrange the order to suit itself any way. There's no way that would make a difference based on what you've shown.
Assuming no WHERE clause, this query will return an identical execution plan in a stored procedure and won't run faster. The difference would be if you had a WHERE clause and you were using parameters instead of local variables. In most cases, but not all, you'll see better performance out of parameters due to parameter sniffing helping you out.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 12, 2014 at 6:50 am
Based on the information above, there's no performance benefit.
The only time I've seen performance benefit by re-ordering the tables is in previous roles where I've had to override the query engine using force order or join hints.
In many cases this was because updating statistics wasn't permitted due to extremely high concurrency.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply