June 8, 2012 at 3:30 am
pdanes (6/8/2012)
When I put in the two variations of the WHERE clause, filtering on either the main table or the appropriate aux table, I get identical results, but different query plans.
They are two logically different queries that in this case just happen to produce the same result. That's why you get different query plans, because to SQL those two queries are logically different, have different meanings and can produce different results.
Once more with feeling, this has nothing whatsoever to do with performance. You are not going to get performance improvements by minor rewrites of the query, the parser and the optimiser are smarter than that. All you're going to do is change the meaning of the query which may or may not change the results depending on the data that you have.
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
June 8, 2012 at 4:49 am
GilaMonster (6/8/2012)
They are two logically different queries that in this case just happen to produce the same result. That's why you get different query plans, because to SQL those two queries are logically different, have different meanings and can produce different results.Once more with feeling, this has nothing whatsoever to do with performance. You are not going to get performance improvements by minor rewrites of the query, the parser and the optimiser are smarter than that. All you're going to do is change the meaning of the query which may or may not change the results depending on the data that you have.
All right, I appreciate it, but we seem to be talking at cross purposes here. You insist that my two versions can produce different results, but I don't see how and I haven't been able to make it happen, no matter what sorts of conditions I try. Your example did, but it was constructed differently from mine. Whether it's ultimately a performance issue or not, both versions give me the results that I need and both have acceptable response times. I'm simply going to use whichever one is easier to code and get on with it.
Thank you for the effort you put into this - I don't wish to appear ungrateful, but we don't seem to be making any headway, so I suggest we drop it.
June 8, 2012 at 4:56 am
Sorry, but what did you want me to say? That one is faster than the other? That one is better than the other?
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
June 8, 2012 at 5:25 am
GilaMonster (6/8/2012)
Sorry, but what did you want me to say? That one is faster than the other? That one is better than the other?
No apologies necessary. As I wrote, I appreciate the effort you put into it. Not only answered questions, but your blogs and articles have helped me immensely in my understanding of SQL Server, more times than I can count.
I did think that one was likely to be better than the other, which is why I started this thread. You say they're different and can produce different results - you're about the last person that I would want to argue with regarding the functioning of this software, but I simply don't understand how that is. I've studied them, studied your examples, experimented with both and not advanced in my comprehension. It even seems to me that the results should be the same - I'm joining on a equal condition, so it doesn't seem that it should matter which side of the equals sign I look at.
You say they're different and with your expertise in the subject, I have to believe you, but I'm stumped. I don't even know which version 'should' be the correct one for me to use, since I can't figure out what's different between the two. You seemed to be getting irked with me, so I suggested we drop the matter and not waste any more of your time, since I'm obviously not making any progress in understanding this.
Maybe somewhere down the road I will run into some incorrect results that will finally make it clear to me, but for now, I'm simply stuck. I have to pick something, so I'm going to pick the easier of the two and hope for the best.
June 8, 2012 at 5:35 am
I'm not irked.
To summarise massively...
If you're doing a left join and filtering on the left-hand table, it is a left join and if there's no match for that row in the right-hand table, the row will still be returned
If you're doing a left join and filtering on the right-hand table, it's actually an inner join (unless your filter has OR <column> IS null) and if there's no match in the right-hand table the row will not be returned.
Play around with that setup (as simple as possible) and see if you can get your head around it.
If your data does not allow for that possibility, then use whichever you want.
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
June 10, 2012 at 10:32 am
GilaMonster (6/8/2012)
I'm not irked.
Oh, good - sometimes it's hard to tell via only text.
Play around with that setup (as simple as possible) and see if you can get your head around it.
I tried and no change. Maybe the key is what you say here: "… doing a left join and filtering on the left-hand table…"
Your example selected from what is the equivalent of the aux table in my setup, and left-joined to the main, like this:
SELECT * FROM #t1 LEFT OUTER JOIN #t2...
Naturally, in such a case, the behavior is as you say.
But I'm select from the main and left-joining to all my aux tables, which in your example would be the equivalent of
SELECT * FROM #t2 LEFT OUTER JOIN #t1...
which wording, incidentally, gives the exact same 'identical results' behavior in your example as I see in my own database and the made-up example with five aux tables that I posted yesterday.
I always do a Select on the main table with Left Joins to every one of my aux tables. If that was the whole story, of course I would get everything, every time. But I also have conditions, which I word one of two ways:
1. MainTable.ForeignKeyField = "SomeValue". That filters the main table to return only those records that match the given value, which then Left Joins to all the aux tables.
2. AuxTable.PrimaryKeyField = "SomeValue". That filters the aux table to only the value specified, and the left join on that table, as you say, then acts as an Inner Join, returning only those records that match the given value, while still retaining the Left Join behavior with all the other tables not specified in the condition.
Certainly, two different queries, hence two different execution plans, but identical results. Nothing I have tried, simple or complicated, has come up with anything different. Either way, I always get exactly what I want, exactly what I expect and I haven't a clue as to why I should ever expect anything else.
At this point, I'm completely out of ideas - I can't even think of any more experiments to try. If there is truly something else going on under all this, I'm simply going to have to accept that I'm too dense to see it.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply