September 3, 2007 at 10:33 am
Hi Experts,
I have two tables namely TableA and TableB which contains data 5000 and 500000. If we join these two tables which will come first in the WHERE clause ?
Can anybody give me the exact answer with reason ?
Regards
Karthik
karthik
September 3, 2007 at 11:11 am
It depends.
We can guess, but showing your table's DDL will help us much.
SELECT *
FROM Table1
INNER JOIN Table2 ON Table2.Col1 = Table1.Col2
WHERE SCOPE_IDENTITY() = 99
As you can see, queries can look very different.
N 56°04'39.16"
E 12°55'05.25"
September 3, 2007 at 12:26 pm
It doesn't matter which comes first in the WHERE clause. Either one can be there and the optimizer will decide how to join the tables on its own.
September 4, 2007 at 12:11 am
Peter,
This is my code.
CompanyTrans Table : 180240
AcctEntry Table : 755320
-------------------------------------------------------------------SELECT DISTINCT Seq = identity (10),
A.CompanyInstitNbr, A.TransNbr,
CONVERT (char (10), A.PostingDate, 111) +
CONVERT (char (5), A.TransNbr) +
CONVERT (char (8), A.ValueDate, 1) Combo,
TransTypeCode Typ,
A.PostingDate,
CONVERT (int, NULL) MaxSeq,
CONVERT (char (4), AcctNbr) +
CONVERT (char (3), SubAcctNbr) + SubAcctCode Acct,
CONVERT (char (2), CONVERT (int, SIGN (AcctEntryUsdAmt + AcctEntryQty))) + '|' CrDr
INTO #NotMisc
FROM CompanyTrans C, AcctEntry A
WHERE A.CompanyInstitNbr = C.CompanyInstitNbr
AND A.TransNbr = C.TransNbr
AND TransTypeCode NOT LIKE 'MS%'
AND SIGN (AcctEntryUsdAmt + AcctEntryQty) <> 0
ORDER BY A.CompanyInstitNbr, A.TransNbr, AcctNbr, SubAcctNbr,SubAcctCode
------------------------------------------------------------------------
shall i include AcctEntry first or CompanyTrans table, which one will give less execution time ?
Regards
Karthik
karthik
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply