Hi, I had QODBC 8 and QB Enterprise Solutions USA ver. 8 running for months querying the Invoice table using the following SQL script:

SELECT * FROM Invoice WHERE TimeModified >= {ts '2008-10-17 00:00:00.001'} and TimeCreated > {ts '2008-07-15 00:00:00.000'} and ClassRefListID='40000-1110299257'

This worked fine 100% of the time, I ran it daily at 10PM. I also used the SAME query for Credit Memos. Credit Memos still work using this format, Invoices do not - every query to the invoice table takes forever an yields zero results, I constantly see 2 boxes for the ODBC driver - one waiting on QB and one optimizing the table; both are for invoice.

Optimizer is on, at the start of every new connection w/ load updated data first

real time

multi-table sync and optimize data after an insert or update are both UNCHECKED.

I am using QODBC version 9.000.00.253 and QBES 9 -- this query will not work for invoices since I updated to 9.

Try:

SELECT * FROM Invoice UNOPTIMIZED WHERE TimeModified >= {ts '2008-10-17 00:00:00.001'} and TimeCreated > {ts '2008-07-15 00:00:00.000'} and ClassRefListID='40000-1110299257'

If that gets you the results you're expecting then re-optimize your invoice table by doing:

SELECT * FROM Invoice VERIFY WHERE ClassRefListID='40000-1110299257'

The VERIFY operation will take a while and must be allowed to complete.

Tom,

      The 1st query (UNOPTIMIZED) worked perfectly. I am running the VERIFY query now. For my system, which runs daily and updates the invoices in my database, should I be running the standard query (the first one I posted) the unoptimized one, or the verify one? I guess what I mean is is the very query a one-time thing?

Tom, thanks for getting me all fixed up - works great now.

If I am querying the inventory table, should I use unoptimized for that as well to have the latest QuantityOnHand information?

I am only exporting tables for the most part (CreditMemo, CreditMemoLine, Invoice, InvoiceLine, all the group & discount info, & iteminventory) -- should I be using the optimizer at all?

Thanks for the help.

Well it goes like this, if you're doing single table queries that are well targeted on subsets of data, for example new invoices since yesterday, then using the UNOPTIMIZED tag is best. But if you're doing a multi-table join to work out Sales Commisions for the last six months, then I would use the NOSYNC tag. So there's no hard rule, it depends on each query and what it needs to do ..... that's why we have the query tags.

See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more information.