May 31, 2004 at 1:37 am
Is there any performance implication using one (say WHERE) over the other (JOIN BY). Which would I use in which scenario ?
June 1, 2004 at 3:45 am
Remember these basic things:
1. When you do a normail SELECT * FROM Table, Table1, Table2 WHERE blah blah, in effect all the data is selected from the tables before doing the comparisons in the where clause.
2. When you do a join the data is filtered from the start if you handle you "ON" in the joins correctly. All parameters used normally in a where clause should be inluded in a join
3. To look at the cost effect of this on resources you can have a look at the execution plan in Query Analyzer.
June 1, 2004 at 7:52 am
Some general checking I did a while back, not scientific but representative, I saw no real difference.
Keep in mind the "Where" style Joins are NOT ANSI compliant, could be dropped sometime in the future. Although all of the major DBMSs do support either.
KlK
June 1, 2004 at 7:56 am
On very big tables it makes a HUGE difference in speed doing all comparisions in the joins. Especially if you try to join tables with 30 million plus records. On smaller DB's you will not neccesarely see the difference but the associated cost is much higher.
R
June 1, 2004 at 8:03 am
yes Join is made more faster ... because it join 2 tables more faster (Pk and Fk)
Alamir Mohamed
Alamir_mohamed@yahoo.com
June 1, 2004 at 9:16 am
I agree. When you view the execution plan I haven't seen a difference between pre-sql92 and sql92 joins. It generates the same plan.
The ANSI compliant joins are much easier to read and won't be deprecated.
steve
June 1, 2004 at 11:45 am
I don't know how to use the Execution Plan. But looks to me that it could be very valuable. I'll have to make sure that I understand it well.
June 2, 2004 at 12:10 am
Alamir said:
> yes Join is made more faster ... because it join 2 tables more
> faster (Pk and Fk)
Which is true, providing you are joining on defined keys. If you are joining on non-keys, or calculated fields (e.g. joining fields in views), I would habour a guess that it makes not difference at all.
I believe (although I've not checked) that if you look at the query execution plans for JOIN vs. WHERE it actually amounts to the same thing. This would make sense to me. Its a not essentially a different operation, but as somebody said, its a matter of when the filtering occurs.
It does make a significant difference when the condition is in a HAVING clause.
pk
June 2, 2004 at 1:03 am
I also think that where statement is transfered into Join (implicitly) ... and there is a very very small wasting time.
the important thing is to make Index on the 2 columns (PK and FK) .. because if you don't make this , I think the where statement will not be transfered to Join ..
Alamir Mohamed
Alamir_mohamed@yahoo.com
June 2, 2004 at 8:01 am
When you define a new table, and you define a PK, aren't you already creating one index? You then can create more indexes on FK and any other column you wish.
June 23, 2004 at 3:04 pm
From above:
I believe (although I've not checked) that if you look at the query execution plans for JOIN vs. WHERE it actually amounts to the same thing. This would make sense to me. Its a not essentially a different operation, but as somebody said, its a matter of when the filtering occurs.
It does make a significant difference when the condition is in a HAVING clause.
_______
My comment:
Unlike comparing the location of the criteria between the JOIN and WHERE, the HAVING clause evaluates the results of a GROUP BY statement, so criteria in HAVING is not the same as the same criteria in JOIN and/or WHERE
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply