Joins Position

Hi
Does changing Join sequence in any query effect performance?
I had a problem in a query which was performing very poorly but after I
changed the positions it is performing very well.
Any reason for this.
Lalit
Can you provide the whole SQL statement? (before and after)
Lalit wrote:
> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after
> I changed the positions it is performing very well.
> Any reason for this.
> Lalit
|||This is SQL Statement that performed fine.
I interchanged second and third JOINs. Both are Linked
with the first one.
SELECT DISTINCT GMTranSuper.GTS_VrNo [Name],
GMTranSuper.GTS_Id [Id]
FROM (SELECT GTS_Id, GTS_VrNo, GTS_Wt
FROM GMTranSuper
WHERE GTS_AllowMulRcpts = 0
AND ISNULL(GTS_RetdGTSId,
0) = 0) AS GMTranSuper
LEFT JOIN (SELECT GTIW_GTSId, SUM
(GTIWD_IssWt) AS IssWt,
SUM
(GTIWD_RetdWt) AS RetdWt
FROM GMTranIssWkr
INNER JOIN
GMTranIssWkrDet ON GMTranIssWkr.GTIW_Id =
GMTranIssWkrDet.GTIWD_GTIWId
--WHERE GTIW_GTSId
IS NOT NULL
GROUP BY
GTIW_GTSId) AS IssWkr
ON GMTranSuper.GTS_Id =
IssWkr.GTIW_GTSId
LEFT JOIN (SELECT GTS_RetdGTSId,
SUM(GTS_Wt) AS RetdWt
FROM GMTranSuper
--WHERE
GTS_RetdGTSId IS NOT NULL
GROUP BY
GTS_RetdGTSId) AS RetSuper
ON GMTranSuper.GTS_Id =
RetSuper.GTS_RetdGTSId
WHERE(GMTranSuper.GTS_Wt - (ISNULL
(RetSuper.RetdWt, 0) +
ISNULL(IssWkr.IssWt, 0) -
ISNULL(IssWkr.RetdWt, 0))) > 0
ORDER BY GMTranSuper.GTS_VrNo DESC
Following SQL Statement is that one that had the problem
SELECT DISTINCT GMTranSuper.GTS_VrNo [Name],
GMTranSuper.GTS_Id [Id]
FROM (SELECT GTS_Id, GTS_VrNo, GTS_Wt
FROM GMTranSuper
WHERE GTS_AllowMulRcpts = 0
AND ISNULL(GTS_RetdGTSId,
0) = 0) AS GMTranSuper
LEFT JOIN (SELECT GTS_RetdGTSId,
SUM(GTS_Wt) AS RetdWt
FROM GMTranSuper
--WHERE
GTS_RetdGTSId IS NOT NULL
GROUP BY
GTS_RetdGTSId) AS RetSuper
ON GMTranSuper.GTS_Id =
RetSuper.GTS_RetdGTSId
LEFT JOIN (SELECT GTIW_GTSId, SUM
(GTIWD_IssWt) AS IssWt,
SUM
(GTIWD_RetdWt) AS RetdWt
FROM GMTranIssWkr
INNER JOIN
GMTranIssWkrDet ON GMTranIssWkr.GTIW_Id =
GMTranIssWkrDet.GTIWD_GTIWId
--WHERE GTIW_GTSId
IS NOT NULL
GROUP BY
GTIW_GTSId) AS IssWkr
ON GMTranSuper.GTS_Id =
IssWkr.GTIW_GTSId
WHERE(GMTranSuper.GTS_Wt - (ISNULL
(RetSuper.RetdWt, 0) +
ISNULL(IssWkr.IssWt, 0) -
ISNULL(IssWkr.RetdWt, 0))) > 0
ORDER BY GMTranSuper.GTS_VrNo DESC
Lalit
|||Theoretically the answer is no but you seem to have found a situation
when it does. Post the query and let everyone see what has happened.
Lalit wrote:

> Hi
> Does changing Join sequence in any query effect performance?
> I had a problem in a query which was performing very poorly but after I
> changed the positions it is performing very well.
> Any reason for this.
> Lalit
>
>
sql