Help with BIG query

[ondvirg via AccessMonster.com]

I'm trying to return a subset of records from a SQL transaction table that
has over 35million records. I only need the transaction for about 37 accounts,
so I setup a table with the 37 numbers and then created a join in a query
between that table and the transaction table.

My problem is the query runs forever. However, if I remove the account list
table and simply type the numbers into the query criteria as an indude
statement, the query runs like lightning. What am I missing? I wanted to use
the account list as that changes from time to time and I don't want to keep
having to change the actual criteria of the query.

Any help is appreciated.
Thanks.

 

[KARL DEWEY]

I never had that many records but try using the field from the table as
criteria without joining.
If you want you can have another field for Active that is Yes/No. Just
update Active field to change which account to pull records for. Then
criteria on that field -1 (minus one).

 

[John]

You could try using a subquery, but I have no idea how fast that would
run on a table with 37 million records. Something like this:


SELECT * FROM transaction_table WHERE account_number IN (SELECT
account_number FROM account_table;

Of course, replace transaction_table, account_table, and account_number
with the appropriate table/field names.



If you don't mind using temporary tables, you could write some code that
would iterate through your list of account numbers and pull the
transactions for each account one at a time, storing them in a temporary
table.

 

[Clifford Bass]

Hi,

Sounds like it is not using an index when you attempt to do it with the
accounts table. Is the accounts table in the same database as the
transactions table? If not, that is probably the issue. If so, try the
following to see if that makes any difference.

Select [your fields list] from transactions
where accountnumber in
(select accountnumber from accounts)

Clifford Bass