>Hi all,
>
>I have 2 SQL select below, the 1st one ran for more than 2 hours and I have
>to stop by doing CTRL ALT-DEL to stop without any results
>
>The second one run for 5 minutes , I got the results.
>
>How much is the difference between the 2 SQL select below,
>please guide me how to optimize the 1st one if it is possible
>
>It is about 30000 records for both [qry - VarCreditMarge_BE] and [qry - VarJunMarge_MatchAccount_BE] queries inside the select itself.
>
>TIA
>
>

>
>**1st:
>
>SELECT DISTINCT [qry - VarCreditMarge_BE].*
>FROM [qry - VarCreditMarge_BE]
>WHERE [qry - VarCreditMarge_BE].vcnumca2 in
>  (select  [qry - VarJunMarge_MatchAccount_BE].vcnumca2  from [qry - VarJunMarge_MatchAccount_BE]
>                where  [qry - VarJunMarge_MatchAccount_BE].MatchAccount=0)
>
>
>**second:
>
>SELECT DISTINCT [qry - VarCreditMarge_BE].*, [qry - VarJunMarge_MatchAccount_BE].[MatchAccount]
>FROM [qry - VarCreditMarge_BE] INNER JOIN [qry - VarJunMarge_MatchAccount_BE] ON [qry - VarCreditMarge_BE].[vcnumca2]=[qry - VarJunMarge_MatchAccount_BE].[vcnumca2]
>WHERE [qry - VarJunMarge_MatchAccount_BE].[MatchAccount]=0
>
>

My experience is that the subquery in the filter seems to run for every record in the FROM clause. You can speed things up by breaking this up into two queries--the first is the one you're running in your WHERE clause and then run the second query with an INNER JOIN to the results from the first query.

select  [qry - VarJunMarge_MatchAccount_BE].vcnumca2  
from [qry - VarJunMarge_MatchAccount_BE]
where  [qry - VarJunMarge_MatchAccount_BE].MatchAccount=0 
INTO CURSOR T1

SELECT DISTINCT [qry - VarCreditMarge_BE].*
FROM [qry - VarCreditMarge_BE] INNER JOIN T1 ;
ON [qry - VarCreditMarge_BE].vcnumca2 = [qry - VarJunMarge_MatchAccount_BE].vcnumca2

HTH