Performance - Joins vs Filters

Hi
I reckon this is a "how long's a piece of string"-type of question but I'll
try it anyway. If you could provide any pointers, even if it is not a direct
answer then I'd be really grateful.
I've written an app generates SQL. I'm joining many tables and it's stable.
However, I now need to enhance it some more and link in another table. I
have the option of extending the WHERE clause instead of modifying the
joining mechanism in the FROM clause. Extending the WHERE clause means
adding a subselect and the way to do this is *far* easier to implement than
to rework the joining mechanisms to include an extra table - most especially
for Left Joins.
So my preference would be just extend the filter but I'm not sure about the
impact on performance. Will left joining from the additional table (and
extending the filter) be significantly faster than SubSelecting from it and
using an IN?
Thanks
Simonit depends. You need to do your own benchmarks|||Recently, I developed a data warehouse and in terms of SQL performance tips
etc. I was a complete novice. I did alot of investigation and research into
the fastest way to query and I found that subselects in general were a big
performance hit. I found by creating intermediate tables that I could join
into other queries that performance was greatly enhanced. Of course this is
subjective to the scenario and I imagine there are plenty of exceptions, why
dont you try both and find out?
"Simon Woods" wrote:

> Hi
> I reckon this is a "how long's a piece of string"-type of question but I'l
l
> try it anyway. If you could provide any pointers, even if it is not a dire
ct
> answer then I'd be really grateful.
> I've written an app generates SQL. I'm joining many tables and it's stable
.
> However, I now need to enhance it some more and link in another table. I
> have the option of extending the WHERE clause instead of modifying the
> joining mechanism in the FROM clause. Extending the WHERE clause means
> adding a subselect and the way to do this is *far* easier to implement tha
n
> to rework the joining mechanisms to include an extra table - most especial
ly
> for Left Joins.
> So my preference would be just extend the filter but I'm not sure about th
e
> impact on performance. Will left joining from the additional table (and
> extending the filter) be significantly faster than SubSelecting from it an
d
> using an IN?
> Thanks
> Simon
>
>|||Simon,
Why don't you find out instead of guess? Type the two
possible queries you're considering into query analyzer
and either compare their execution plans or test them on
sample data.
If for some reason you can't test the queries you're considering,
and want more advice here, you'll have better luck if you
post specific queries along with the relevant CREATE TABLE
statements and some sample data.
Steve Kass
Drew University
Simon Woods wrote:

>Hi
>I reckon this is a "how long's a piece of string"-type of question but I'll
>try it anyway. If you could provide any pointers, even if it is not a direc
t
>answer then I'd be really grateful.
>I've written an app generates SQL. I'm joining many tables and it's stable.
>However, I now need to enhance it some more and link in another table. I
>have the option of extending the WHERE clause instead of modifying the
>joining mechanism in the FROM clause. Extending the WHERE clause means
>adding a subselect and the way to do this is *far* easier to implement than
>to rework the joining mechanisms to include an extra table - most especiall
y
>for Left Joins.
>So my preference would be just extend the filter but I'm not sure about the
>impact on performance. Will left joining from the additional table (and
>extending the filter) be significantly faster than SubSelecting from it and
>using an IN?
>Thanks
>Simon
>
>|||Well, first question in my mind is "do you need any of this data for
output?" If yes, then join, if no, then where clause. If it is too slow,
then optimize.
If you are just filtering data, then an exists in the where should be
faster, it will certainly express what you are trying to do in a more
correct manner.
And as everyone else has stated, test it out :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Simon Woods" <simonSPAMMENOT.woods@.virginNOTMESPAM.net> wrote in message
news:eQ94X$tEGHA.4036@.TK2MSFTNGP09.phx.gbl...
> Hi
> I reckon this is a "how long's a piece of string"-type of question but
> I'll try it anyway. If you could provide any pointers, even if it is not a
> direct answer then I'd be really grateful.
> I've written an app generates SQL. I'm joining many tables and it's
> stable. However, I now need to enhance it some more and link in another
> table. I have the option of extending the WHERE clause instead of
> modifying the joining mechanism in the FROM clause. Extending the WHERE
> clause means adding a subselect and the way to do this is *far* easier to
> implement than to rework the joining mechanisms to include an extra
> table - most especially for Left Joins.
> So my preference would be just extend the filter but I'm not sure about
> the impact on performance. Will left joining from the additional table
> (and extending the filter) be significantly faster than SubSelecting from
> it and using an IN?
> Thanks
> Simon
>|||On Fri, 6 Jan 2006 16:40:02 -0000, "Simon Woods"
<simonSPAMMENOT.woods@.virginNOTMESPAM.net> wrote:
>So my preference would be just extend the filter but I'm not sure about the
>impact on performance. Will left joining from the additional table (and
>extending the filter) be significantly faster than SubSelecting from it and
>using an IN?
If you're very lucky, the optimizer will turn out exactly the same
code for any of the top three or four ways to code it.
Actually, it's pretty common to see that.
J.|||Hi Simon
can't argue with Alexander and Steve's recommendation of "try both and
find out which is better"!
However, my experience (10 years) is that subselects are almost always
less efficient than joins. I never use them now, and I'm pleasantly
surprised again and again at how SQL Server can gobble up the most
evil-looking multiple join operations and flip the results back in
seconds.
The divantage of joins as you say is that they can take quite a bit
of work to get right.
try out the subselect with some (sufficiently large set of) sample
data, I reckon.
cheers
Seb