I have a table of Accounts that I get a selection from:
Select *
into \memory\tempAcct
from Account where ........

Now I need the corresponding transactions for all of them, not one account
at a time.
So which method would generally produce the fastest result?
A:
select * from Transactions where AccountID in (select AccountID from
\memory\tempAcct)

or B.
select T.*
from \memory\tempAcct A
left outer join Transactions T on A.AccountID=T.AccountID

Or, are there other considerations?

TIA,
Bill

Bill

My bet would be on the join, but the best way is to suck it and see - do it in DBSys, produce the plan for each version and just have a look


Roy Lambert

"B Miller" <bmiller@nospam.com> wrote in message
news:8045C6E6-F3FE-45E4-B4E7-84B18FB06C07@news.elevatesoft.com...
>I have a table of Accounts that I get a selection from:
> Select *
> into \memory\tempAcct
> from Account where ........
>
> Now I need the corresponding transactions for all of them, not one account
> at a time.
> So which method would generally produce the fastest result?
> A:
> select * from Transactions where AccountID in (select AccountID from
> \memory\tempAcct)
>
> or B.
> select T.*
> from \memory\tempAcct A
> left outer join Transactions T on A.AccountID=T.AccountID
>

It depends on the size of the first selection. My choice would be B, since
it would work OK regardless of size. I would strongly recommend creating an
index on the memory table account ID.

> Or, are there other considerations?

Well, the obvious question is why go thru the create memory table in the
first place? You could  just join the accounts table to the transactions
table, and have the where clause as part of the single query. Eliminate the
middleman.

Robert