The Transaction table will contain a large amount of transaction lines as there will be a line for every transaction made and modified, so a:

SELECT * FROM Transaction

query will take forever. While limiting the transactions to say only transactions modified this month (Apr 2007) is better:

SELECT * FROM Transaction where TimeModified >= {ts '2007-04-01 00:00:00.000'} and TimeModified < {ts '2007-05-01 00:00:00.000'}

You should optimize the Transaction table by running:

sp_optimizeupdatesync Transaction

in VB Demo first and then use the NOSYNC tag to queries the optimized table directly:

SELECT * FROM Transaction NOSYNC where TimeModified >= {ts '2007-01-01 00:00:00.000'} and TimeModified < {ts '2008-01-01 00:00:00.000'}

See: Using DTS to Load QuickBooks Data into Microsoft SQL Server for more.

Ok what you are missing here is that the TRANSACTION table isn't really a table but a glorified report table view. The transaction table should be used as a flat table only. There is no QODBC jump-in between any table the TRANSACTION table. As far linking tables are concerned, you should always use jump-in columns or a optimized index. Run:-

sp_column tablename

to see what columns jump-in to other table columns natively under QuickBooks. We also have:

sp_statistics tableaname

which is a stored procedure that returns all index information (including QuickBooks and optimizer only FQSPECIAL indexes).

Using sp_statistics BillPaymentCheckLine for example, we can see that there is now a special QODBC optimized index to use for the AppliedToTxnTxnID lookups for bill payments which doesn't exist as a native lookup in QuickBooks:-