JOINing ORDER and Performance

Hi ,
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
DuttIt shouldn't make a difference, as long as the semantics of the query stay t
he same (consider outer
joins). The optimizer is free to re-arrange at will as long as semantics sta
y the same. You might
see a small difference, since optimizer has "early out" strategies, but if y
ou do see a big
difference, you have found a weakness in the optimizer and MS would like to
know about it
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171436684.446317.116710@.a34g2000cwb.googlegroups.com...
> Hi ,
> There r 5 tables A,B,C,D,E(related each other) with
> 1,10,100,1000,10000 records respectively.
> Will there b any Performance difference w.r.t. the order in which they
> r joined?
> If so,what is the best order to INNER JOIN them?
>
> Practically,I observed that starting wiith larger table can give
> better performance,but I was unable to conclude y?
> Pls help.
> Thanks,
> Dutt
>|||OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...|||Yes, you should never return more columns than needed. If you return more co
lumns than needed, you
will suffer from a number of technical reasons:
1. More data need to be sent to the client.
2. More data need to be stored between the execution steps (possibly materia
lized to tempdb).
3. You diminish the chance for covering indexes to be used. A covering index
is a non-clustered
index containing all the information that a query need from a table. This wa
y, SQL Server don't have
to access each page for each row, all information is already in the non-clus
tered index. This can
make a huge performance difference.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171441015.708396.44700@.k78g2000cwa.googlegroups.com...
> OK...Tibor, but,clarify me a small doubt.
> If the joining table is so large and we require only a few fields,
> selecting only the required fiedls do increase the performance?
> Pls explain...
>|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>It shouldn't make a difference, as long as the semantics of the query stay
the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics st
ay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if
you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know ab
out it
Ha.
J.|||> Ha.
Get your point... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7m46t2drr3jd953nef4plamko1feu7ulql@.
4ax.com...
> On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>
> Ha.
> J.
>

JOINing ORDER and Performance

Hi ,
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
DuttIt shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
see a small difference, since optimizer has "early out" strategies, but if you do see a big
difference, you have found a weakness in the optimizer and MS would like to know about it
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171436684.446317.116710@.a34g2000cwb.googlegroups.com...
> Hi ,
> There r 5 tables A,B,C,D,E(related each other) with
> 1,10,100,1000,10000 records respectively.
> Will there b any Performance difference w.r.t. the order in which they
> r joined?
> If so,what is the best order to INNER JOIN them?
>
> Practically,I observed that starting wiith larger table can give
> better performance,but I was unable to conclude y?
> Pls help.
> Thanks,
> Dutt
>|||OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...|||Yes, you should never return more columns than needed. If you return more columns than needed, you
will suffer from a number of technical reasons:
1. More data need to be sent to the client.
2. More data need to be stored between the execution steps (possibly materialized to tempdb).
3. You diminish the chance for covering indexes to be used. A covering index is a non-clustered
index containing all the information that a query need from a table. This way, SQL Server don't have
to access each page for each row, all information is already in the non-clustered index. This can
make a huge performance difference.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1171441015.708396.44700@.k78g2000cwa.googlegroups.com...
> OK...Tibor, but,clarify me a small doubt.
> If the joining table is so large and we require only a few fields,
> selecting only the required fiedls do increase the performance?
> Pls explain...
>|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>It shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know about it
Ha.
J.|||> Ha.
Get your point... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:7m46t2drr3jd953nef4plamko1feu7ulql@.4ax.com...
> On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>>It shouldn't make a difference, as long as the semantics of the query stay the same (consider
>>outer
>>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>>difference, you have found a weakness in the optimizer and MS would like to know about it
> Ha.
> J.
>

JOINing ORDER and Performance

Hi ,
There r 5 tables A,B,C,D,E(related each other) with
1,10,100,1000,10000 records respectively.
Will there b any Performance difference w.r.t. the order in which they
r joined?
If so,what is the best order to INNER JOIN them?
Practically,I observed that starting wiith larger table can give
better performance,but I was unable to conclude y?
Pls help.
Thanks,
Dutt
OK...Tibor, but,clarify me a small doubt.
If the joining table is so large and we require only a few fields,
selecting only the required fiedls do increase the performance?
Pls explain...
|||On Wed, 14 Feb 2007 08:32:48 +0100, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:

>It shouldn't make a difference, as long as the semantics of the query stay the same (consider outer
>joins). The optimizer is free to re-arrange at will as long as semantics stay the same. You might
>see a small difference, since optimizer has "early out" strategies, but if you do see a big
>difference, you have found a weakness in the optimizer and MS would like to know about it
Ha.
J.

JOIN with ORDER BY clause?

like so often my Forums database design (in its simplest form) is:
Forums
-ForumID
-Title
-Category
ForumsMsgs
-fmID
-DateIn
-AuthorID
-Message

I need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?

SELECT f.*,fm.*

FROM Forums f

JOIN ForumsMsgs fm ON (f.ForumID=fm.fmID)

WHERE fm.DateIn IN (SELECT MIN(DateIn) FROM ForumsMsgs fm2 WHERE fm2.AuthorID=fm.AuthorID UNION SELECT MAX(DateIn) FROM ForumsMsgs fm2 WHERE fm2.AuthorID=fm.AuthorID)

Should work for you, although there may be multiple records returned if the author was able to create multiple messages with the same DateIn, and I'm not sure how you wanted to handle the case in which the author has only left a single message (Making both the first and last message the same one). There are better ways of doing the same thing, but I don't see a unique/primary key on the ForumsMsgs table (MsgID?)

|||

The following query is based on these assumptions for your two tables. ForumsMsgs.fmID is the Foriegn Key field for Forums.ForumID. I don't see any really good indication of what makes the record unique in ForumsMsgs, but you need a single value Primary Key field in ForumMsgs for this process to work. Let's call it msgID which could be a INT Identity(1,1) field.

The concept is to retreive a single PK record from ForumMsgs for each Forums.ForumID entry that matches your Order by criteria. Since you want both the newest and oldest messages, this will be done in two steps with a UNION operator.

The inner query (a) returns two records for each ForumID one each oldest/newest. This result is used to join back into the source tables to get your results from Forums (f) and ForumsMsgs (m). A SELECT DISTINCT is used so that you don't return two records that are identical, which will happen if a forum has only single message, makeing it both the oldest and newest.

SELECT DISTINCT
f.ForumID
, f.Title
, f.Category
, m.msgID
, m.DateIn
, m.AuthorID
FROM
( -- Inner Query to get the Oldest/Newest Message IDs
-- Gets the forum oldest message ID
SELECT
ForumID
, ( -- New Field that represents the msgID (Primary Key entry)
-- that matches the ForumID record
SELECT TOP 1
msgID
FROM ForumsMsgs
WHERE
ForumID = fmID
ORDER BY
fmID
, DateIn ASC
) AS msgID
FROM Forums
UNION
-- Gets the forum newest message ID
SELECT
ForumID
, ( -- New Field that represents the msgID (Primary Key entry)
-- that matches the ForumID record
SELECT TOP 1
msgID
FROM ForumsMsgs
WHERE
ForumID = fmID
ORDER BY
fmID
, DateIn DESC
) AS msgID
FROM Forums
) a
JOIN Forums f
ON a.ForumID = f.ForumID
JOIN ForumsMsgs m
ON a.msgID = m.msgID

Not knowing the size of your content, this will most likely run fine. However if very large applications, you may want to run the inner query (a) into a temporary table, then use the temporary table to join back into (f) and (m).

If you have the SQL Server AdventureWorks database installed, you can see the same effect with the following query, which returns the highest order quantity item from the Details table for each Header record. The where clause at the is used to limit the number of records returned for this example (CustomerID between 11000 and 13000).

SELECT
h.SalesOrderID
, h.CustomerID
, (
SELECT TOP 1
d.SalesOrderID
, d.SalesOrderDetailID
FROM sales.SalesOrderDetail d
WHERE
h.SalesOrderID = d.SalesOrderID
ORDER BY
d.SalesOrderID
, d.OrderQty desc
) a
FROM Sales.SalesOrderHeader h
JOIN sales.SalesOrderDetail d
ON a.SalesOrderDetailID = d.SalesOrderDetailID
where
CustomerID between 11000 and 13000

Hope this helps. Hopefully I did not make a syntax error, not having your tables I could not verify that.

Mike

JOIN Process Order and Performance Comparisons

Hi all,
A common SQL that I do is joining parent and child tables together (1-M
relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
number of rows.
Q1) Compare the two statements (that give the same result) below, from a
programming point of view, which one is more efficient?
Statement 1
--
SELECT *
FROM Invoice Ivo
INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
AND IvoItm.ProductType = 1 --This line is processed in WHERE.
Statement 2
--
SELECT *
FROM Invoice Ivo
INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
AND IvoItm.ProductType = 1 --This line is processed in JOIN.
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
This is something which I have been wondering for quite sometime. After
reading MSDN article "Join Fundamentals" stating, it says the JOIN statement
s
are processed first.
Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
process the JOIN?
Q3) If it does the latter 1st, would it filter out the MANY rows in IvoItm,
before doing the JOINS? Therefore improving performance, as the amount of
data to join is reduced in the IvoItm?
Q4) Using the same analogy in Q3, would there be performance gain if I
rewrite the statement using sub-query to do the filtering 1st?
SELECT *
FROM Invoice Ivo
INNER JOIN (
SELECT *
FROM InvoiceItem
WHERE ProductType = 1 --This line is processed in sub-query.
) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
Q5) And would above be efficient than using the Statement 1 and 2?Answers inline:
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"HardKhor" <HardKhor@.discussions.microsoft.com> wrote in message
news:3E92F8CB-3E8A-4A4C-A0D9-71B707658D1A@.microsoft.com...
> Hi all,
> A common SQL that I do is joining parent and child tables together (1-M
> relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
> number of rows.
> Q1) Compare the two statements (that give the same result) below, from a
> programming point of view, which one is more efficient?
> Statement 1
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> AND IvoItm.ProductType = 1 --This line is processed in WHERE.
> Statement 2
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> AND IvoItm.ProductType = 1 --This line is processed in JOIN.
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
These two statements are probably going to perform equivalently, as they are
mathematically equivalent. If this was an outer join, then it will make a
difference. First thing to do is to check the plan using Query analyzer. It
should be the exact same plan.
Logically, all of the JOIN operators will be dealt with, building the set
with Invoice.* + InvoiceLineItem, eliminating rows where the join criteria
fails. Then for every row in the output you apply the where clause.
However, the optimizer can can reorganize the query to make it execute
better as long as the same results would be the same.
For the rest of your questions, try looking at the plan first. It may answer
the questions for you as the answers may be different based on the number of
rows in each table.

> This is something which I have been wondering for quite sometime. After
> reading MSDN article "Join Fundamentals" stating, it says the JOIN
> statements
> are processed first.
>
This is true logically, but it is not required if the results are the same

> Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
> this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
> process the JOIN?
> Q3) If it does the latter 1st, would it filter out the MANY rows in
> IvoItm,
> before doing the JOINS? Therefore improving performance, as the amount of
> data to join is reduced in the IvoItm?
> Q4) Using the same analogy in Q3, would there be performance gain if I
> rewrite the statement using sub-query to do the filtering 1st?
> SELECT *
> FROM Invoice Ivo
> INNER JOIN (
> SELECT *
> FROM InvoiceItem
> WHERE ProductType = 1 --This line is processed in sub-query.
> ) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> Q5) And would above be efficient than using the Statement 1 and 2?|||As noted by Louis, for Inner Joins it doesn't matter whether you specify
the predicates in the WHERE clause or in the JOIN ON clause. For Outer
Joins the meaning is different.
What the query optimizer will do, is analyse which indexes your tables
have and whether they can be sed or scanned in order to reduce the
I/O needed to retrieve the actual data. Then it will do an access path
analysis to see in which order the joins would be fastest. If there is
an appropriate index, then physically, the (partial) filtering will
occur before the join.
Suppose you have a clustered index on Invoice(Date). Then you will
probably see a clustered index s on table Invoice, regardless whether
you used syntax 1 or 2. BTW: the only way to really tell is check the
query plan.
HTH,
Gert-Jan
HardKhor wrote:
> Hi all,
> A common SQL that I do is joining parent and child tables together (1-M
> relationship), e.g. Invoice and InvoiceItem tables. These tables have huge
> number of rows.
> Q1) Compare the two statements (that give the same result) below, from a
> programming point of view, which one is more efficient?
> Statement 1
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItem ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> AND IvoItm.ProductType = 1 --This line is processed in WHERE.
> Statement 2
> --
> SELECT *
> FROM Invoice Ivo
> INNER JOIN InvoiceItem IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> AND IvoItm.ProductType = 1 --This line is processed in JOIN.
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> This is something which I have been wondering for quite sometime. After
> reading MSDN article "Join Fundamentals" stating, it says the JOIN stateme
nts
> are processed first.
> Q2) So in statement 2, does SQL Server process the JOIN 1st, then process
> this filter "AND IvoItm.ProductType = 1", OR process that filter 1st, then
> process the JOIN?
> Q3) If it does the latter 1st, would it filter out the MANY rows in IvoItm
,
> before doing the JOINS? Therefore improving performance, as the amount of
> data to join is reduced in the IvoItm?
> Q4) Using the same analogy in Q3, would there be performance gain if I
> rewrite the statement using sub-query to do the filtering 1st?
> SELECT *
> FROM Invoice Ivo
> INNER JOIN (
> SELECT *
> FROM InvoiceItem
> WHERE ProductType = 1 --This line is processed in sub-query.
> ) IvoItm ON Ivo.RecNum = IvoItm.InvRecNum
> WHERE Ivo.Date IS BETWEEN '2004-01-01 00:00:00' TO '2004-12-31 23:59:59'
> Q5) And would above be efficient than using the Statement 1 and 2?

Join order considerations in SQL Server (2005)

I'm doing a bit of research on join order in terms of how it affects performance in SQL Server (specifically 2005).

Can someone point me to some sites that do a good job of explaining this, or, can you give me a good overview of the topic?

As far as performance goes, I think you will be fine regardless of your join order for the most part. In outer joins (think of a LEFT OUTER JOIN) the order of your tables will matter because the result set would be different depending on which table is the outer and which table is the inner. That is not so much a performance consideration as it is a results consideration.

One motto I always live by is to try it and see. So you can get a lot of replies with links, but I say look at the query plans (You can choose display estimated query plan or if you have a test system you can display the actual execution plan when the query finishes) and play with different join orders.

If you are relatively new to query plans there are a ton of great books. I would highly reccomend Itzik Ben Gan's Inside SQL Server: T-SQL Programming book for his treatment of this topic.

|||

Justin:

If you are wanting to enforce the join order and go rules based you need to SET FORCEPLAN ON; however, this is not something that should normally be practiced. Our standards here pretty much forbid this use of this setting.

Dave