With the release of QODBC v6.00.00.155 we had to do a little more checking for deleted transactions and payments so tables that reference a open balance now run slower. Most users tend to use queries like:
SELECT * from Customer
which will run slowly as QODBC has to display the correct current balance for each customer, while this will run much faster:
SELECT "Name","LastName","FirstName","BillAddressAddr1", "BillAddressAddr2","BillAddressCity", "BillAddressState","BillAddressPostalcode" from Customer
However can also override your QODBC Optimizer configuration screen settings to suit what you are actually doing by using the following tags:
VERIFIED | VERIFY - Forces Full Resync with QuickBooks on the optimized table before Query starts CALLDIRECT | UNOPTIMIZED - Passthru query directly to QuickBooks - use no optimizations OPTIMIZE | OPTIMIZED | NOSYNC - Passthru query to optimized table directly for maximum speed
Examples:
select * from InvoiceLine UNOPTIMIZED select * from InvoiceLine NOSYNC select * from InvoiceLine VERIFIED
For example, to read new (recent) InvoiceLines directly out of QuickBooks use:
select * from InvoiceLine UNOPTIMIZED WHERE Txndate > {d '2006-04-01'}
Or you can resync your optimized InvoiceLine table by first doing:
sp_optimizefullsync InvoiceLine
then read directly out of the optimized table by doing:
select * from InvoiceLine NOSYNC WHERE Txndate > {d '2006-04-01'}
This is very f...a.......s.................t.
Another good example of using NOSYNC would be to get for example all historical invoicelines before 2003:
SELECT * from InvoiceLine NOSYNC WHERE Txndate < {d '2004-01-01'}
as invocies before 2004 don't ever change, you can read them directly from the QODBC Optimizer. You just need to use unoptimized for crtical operations like Sales Commisions etc.
|