Does It Matter...

MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it very clear to anyone
who inherits the code (or myself, when I go senile) which criteria are for
the relationship and which criteria are meant to limit the end result.
For OUTER JOINs, it can certainly matter, but it depends on your desired
result. You may exclude rows by moving criteria from ON to WHERE or vice
versa. I don't know of any situations in INNER JOIN where this is true, but
I bet Itzik or Steve will reproduce one if it exists.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you
place additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows
before joining them? or should I put anything that is not pertinent to the
join itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Pre SQL 2000 SP4 I would say "Yes" it does matter.
Post SP4 I would say "No" it doesn't matter.
SP4 has given some huge performance gains at my site.
However, try it for yourself. Use Profiler / view the Execution plan etc.
To establish your version use SELECT @.@.VERSION.
http://www.aspfaq.com/SQL2000Builds.asp
--
HTH. Ryan
"rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.764
@.TK2MSFTNGP03.phx.gbl...
MSSQl 2000
When creating a select statement with joins... does it matter where you plac
e additional where-clause criteria.
Considering the two examples below, is it more efficient to place additional
filtering criteria within the join section.? Does it weed out extra rows be
fore joining them? or should I put anything that is not pertinent to the joi
n itself down below in its own where clause?
EXAMPLE 1
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'
inner join table_c C ON C.column_2 = B.column_2
EXAMPLE 2
SELECT
A.column_1
B.column_2
C.column_3
FROM
table_a A
inner join table_b B ON B.column_1 = A.column_1 and
inner join table_c C ON C.column_2 = B.column_2
WHERE
B.column_2 = 1 and
B.column_3 = 'boys' and
B.column_4 between '01/01/06' and '01/31/06'|||Thanks Aaron,
Actually I'm more concerned with performance at this point.
Any thoughts on that...
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:OUZNW4nfGH
A.2032@.TK2MSFTNGP02.phx.gbl...
> For INNER JOINs, I like to put the JOIN criteria in the ON clause and the
filtering criteria in the WHERE clause. This makes it
> very clear to anyone who inherits the code (or myself, when I go senile) w
hich criteria are for the relationship and which
> criteria are meant to limit the end result.
> For OUTER JOINs, it can certainly matter, but it depends on your desired r
esult. You may exclude rows by moving criteria from ON
> to WHERE or vice versa. I don't know of any situations in INNER JOIN wher
e this is true, but I bet Itzik or Steve will reproduce
> one if it exists.
>
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message news:u8ljCwnfGHA.7
64@.TK2MSFTNGP03.phx.gbl...
> MSSQl 2000
> When creating a select statement with joins... does it matter where you pl
ace additional where-clause criteria.
> Considering the two examples below, is it more efficient to place addition
al filtering criteria within the join section.? Does it
> weed out extra rows before joining them? or should I put anything that is
not pertinent to the join itself down below in its own
> where clause?
> EXAMPLE 1
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
> inner join table_c C ON C.column_2 = B.column_2
>
> EXAMPLE 2
> SELECT
> A.column_1
> B.column_2
> C.column_3
> FROM
> table_a A
> inner join table_b B ON B.column_1 = A.column_1 and
> inner join table_c C ON C.column_2 = B.column_2
> WHERE
> B.column_2 = 1 and
> B.column_3 = 'boys' and
> B.column_4 between '01/01/06' and '01/31/06'
>
>
>
>|||I think as a rule, the optimizer will do the same thing regardless, although
as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When optimizing
the engine will check only so many possible paths before determining which
one to use, so on larger more complex queries the order of the joins and
criteria can determine which paths get evaluated before it gives up and
chooses one.
I think the bottom line is theoretically it doesn't matter, but the only way
to be totally certain is to test it out both ways. Not just the location of
the criteria, but the order of the tables as well. For simpler queries with
a handful of joins and filter criteria, when the optimizer can afford to
calculate every possibility, it should work the same. If you have dozens of
tables and just as many filters involved, it is worth playing with different
scenarios to see if it makes a difference. With more complex queries the
optimizer can find literally billions of possible execution plans, and
influencing it to look at the right ones can be hit or miss.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> Thanks Aaron,
> Actually I'm more concerned with performance at this point.
> Any thoughts on that...
> Robert
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
the filtering criteria in the WHERE clause. This makes it
which criteria are for the relationship and which
result. You may exclude rows by moving criteria from ON
where this is true, but I bet Itzik or Steve will reproduce
news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
place additional where-clause criteria.
additional filtering criteria within the join section.? Does it
is not pertinent to the join itself down below in its own
'01/31/06'
>|||I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what the others have said. MS considers is more correct
to place the items on the join itself, as this will help SQL to choose the
best execution plan. The idea is that placing more items on the join, means
that the selected table will return less results, before the Join is
executed.
The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the optimising and tuning course for SQL 2005!
My personal view is to use the Join in preference to the Where clause, I
find that it helps to make the syntax clearer and easier to understand.
Regards
Colin Dawson
www.cjdawson.com
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>I think as a rule, the optimizer will do the same thing regardless,
>although
> as Ryan pointed out pre SQL 200 SP 4 it makes a difference. When
> optimizing
> the engine will check only so many possible paths before determining which
> one to use, so on larger more complex queries the order of the joins and
> criteria can determine which paths get evaluated before it gives up and
> chooses one.
> I think the bottom line is theoretically it doesn't matter, but the only
> way
> to be totally certain is to test it out both ways. Not just the location
> of
> the criteria, but the order of the tables as well. For simpler queries
> with
> a handful of joins and filter criteria, when the optimizer can afford to
> calculate every possibility, it should work the same. If you have dozens
> of
> tables and just as many filters involved, it is worth playing with
> different
> scenarios to see if it makes a difference. With more complex queries the
> optimizer can find literally billions of possible execution plans, and
> influencing it to look at the right ones can be hit or miss.
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:O1pbODofGHA.4776@.TK2MSFTNGP05.phx.gbl...
> message news:OUZNW4nfGHA.2032@.TK2MSFTNGP02.phx.gbl...
> the filtering criteria in the WHERE clause. This makes it
> which criteria are for the relationship and which
> result. You may exclude rows by moving criteria from ON
> where this is true, but I bet Itzik or Steve will reproduce
> news:u8ljCwnfGHA.764@.TK2MSFTNGP03.phx.gbl...
> place additional where-clause criteria.
> additional filtering criteria within the join section.? Does it
> is not pertinent to the join itself down below in its own
> '01/31/06'
>|||> The blurb says that the Join syntax is evaluated before the Where syntax.
That is only the logical order. For inner joins, it doesn't matter, and I do
n't even think that the
optimizer know what join type you expressed (the query is transformed into a
tree structure before
the optimizer gets hold of it). The optimizer is free to transform the query
in any way as long as
it returns the same information as if it executed the query as per the rules
for the logical order.

> The course is the optimizing and tuning course for SQL 2005!
Interesting. Which one? There are two such courses, one for "admins" and one
for "developers". Also,
can you point to the module and perhaps even page number and I'll have a loo
k at how they phrase it.

> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
I absolutely agree.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>I just did one of the MS Courses last w which covered this. Whilst the
reality seems to be what
>the others have said. MS considers is more correct to place the items on t
he join itself, as this
>will help SQL to choose the best execution plan. The idea is that placing
more items on the join,
>means that the selected table will return less results, before the Join is
executed.
> The blurb says that the Join syntax is evaluated before the Where syntax.
The course is the
> optimising and tuning course for SQL 2005!
> My personal view is to use the Join in preference to the Where clause, I f
ind that it helps to
> make the syntax clearer and easier to understand.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:eqcg3SofGHA.2208@.TK2MSFTNGP05.phx.gbl...
>|||Hello Tibor
The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
The bit that I was referring two use in Unit 3. Specifically the Query
logical flow diagram on page 2.
Basically it shows the flow as
From & Join --> Where --> Select -- > .... (lots more stuff)
From experience I do agree that it doesn't seem to matter as the query
optimiser does make changes to the query as typed, into how it wants to
produce the results.
Regards
Colin Dawson
www.cjdawson.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> That is only the logical order. For inner joins, it doesn't matter, and I
> don't even think that the optimizer know what join type you expressed (the
> query is transformed into a tree structure before the optimizer gets hold
> of it). The optimizer is free to transform the query in any way as long as
> it returns the same information as if it executed the query as per the
> rules for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and
> one for "developers". Also, can you point to the module and perhaps even
> page number and I'll have a look at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...
>|||My personal preference is to use the primary key/foreign key relation
columns in the join clause, and all other predicates in the where
clause. This is a very consistent syntax that underscores the table
relations and automatically moves all filters to the where clause.
As mentioned before it is a different story for outer joins...
Gert-Jan
Tibor Karaszi wrote:
>
> That is only the logical order. For inner joins, it doesn't matter, and I
don't even think that the
> optimizer know what join type you expressed (the query is transformed into
a tree structure before
> the optimizer gets hold of it). The optimizer is free to transform the que
ry in any way as long as
> it returns the same information as if it executed the query as per the rul
es for the logical order.
>
> Interesting. Which one? There are two such courses, one for "admins" and o
ne for "developers". Also,
> can you point to the module and perhaps even page number and I'll have a l
ook at how they phrase it.
>
> I absolutely agree.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:C1Icg.74278$wl.24621@.text.news.blueyonder.co.uk...|||Colin,

> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
Thanks. I had a quick look through the courses after I posted prior reply, a
nd I guessed this was
the one. The important part here is that it is the *logical* flow. Quote fro
m the same page:
"
Note that
although there is a guaranteed logical order, this is not true of the actual
physical order. The
query
processor can process the query in a different order but still ensure the sa
me results, if it can
find a
more efficient method for doing so.
"
If the optimizer had to respect the logical flow, then almost every query wo
uld give us horrendous
performance:
FROM, grab all columns, and even cross join if old style join syntax
WHERE remove the rows that doesn't satisfies the conditions (including the j
oin if old-style join)
GROUP BY
HAVING
SELECT, until now we had all the columns from all the tables
ORDER BY, not until now could we sort the rows
TOP, ouch, all rows had to be sorted until we throw away all but "top n".
:-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:VsIcg.74297$wl.32163@.text.news.blueyonder.co.uk...
> Hello Tibor
> The course is 2784A: Tuning and Optimising Database Queries User Microsoft
SQL Server 2005
> The bit that I was referring two use in Unit 3. Specifically the Query log
ical flow diagram on
> page 2.
> Basically it shows the flow as
> From & Join --> Where --> Select -- > .... (lots more stuff)
>
> From experience I do agree that it doesn't seem to matter as the query opt
imiser does make changes
> to the query as typed, into how it wants to produce the results.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OHntJPpfGHA.4304@.TK2MSFTNGP05.phx.gbl...
>