Best way to write sql
-
This is a very simple question. If you do a inner join between table a and table b
which order is the best
a join b on (a.col1 = b.col1)
or a join b on (b.col = a.col)
-
Although this is highly debatable due to the fact that query optimizer handles the re-order in this case (as you can see in the execution plan), I will say that it has been a long time practice to order your joins and where clauses in the correct order, meaning the order you state the tables (in this case a.col1 = b.col1). Also, I will go out on a limb here, and state that relying on the optimizer to handle that is simply bad practice from the standpoint of making it work harder (I have no empirical proof of this). I look at it from the standpoint of ANY interpretation involved causes extra work. Let's theorize that in one query, you are unable to measure the difference. Would that still be true at one hundred queries? One thousand? one million? I'm sure you see what I'm saying. I've been involved in thousands of projects over the years, and have heard over and over that little things like this don't really matter (got timelines, you know). Of course, once the database goes production, and the app hitting it grows to be a critical daily thing, hit by thousands of users per hour, statements like that tend to rear up and bite people in the as?. And of course, by that time, the company is crying about how they wish they had implemented best practice to start with.......
Just my opinion, and I'm braced for the flames.....
-
I doubt it makes much difference, but I agree that I think a good (if not best!) practice is to do the joins in the same order as the tables are listed, every little bit helps when you're reading someone elses code. Throw in some white space too!
Andy
-
Shouldn't matter. This is one of the HUGE things we have over Oracle. Less DBA time spent monkeying with orders of stuff.
Or less developers writing code we have to rewrite for no reason.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Of all the queries I have tried both on I have not seen in the situation on the a=b or b=a for the on to make a difference. The query optimizer will pick based on statistics and index density how to look up the data and has some impact on how the join will occurr.
-
Thanks for all contributions. I believe in this situation sql use hash function for matching operation. So Does the efficiency of the search depends on the order of the input variables to the hash function?.
Am I confusing everybody or making un-necessary queries or both ?. As the first reponse inddicated 'Every little thing matters when it comes to large hits'
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply