We have serious problems, any query against the InvoiceLine table is extremely slow.

- When we reload the data from the Optiomizer tab is runs for 12 hours+ (we gave up and killed the process) we only have 90,000 rows in this table....

- If we run a select query filtering on the Modified date, optimized or unoptimized it is loading only 1 row every 2 seconds (until we give up as we calculate that we will have to wait 25 hours until it is done) see SQL.

Select * from InvoiceLibe WHERE (TimeModified >= {ts'" & strLastModDate & ".001'} AND TimeModified < {ts'" & Format(DateAdd("d", 1, Now()), "yyyy-mm-dd hh:mm:ss") & ".001'})"

- We notice that the process that is running the Application (MSAccess VBA using ADO 2.6) which is performing the QODBC queries continues to consume memory (1.5GB ++ )over time - it appears to have a memory leak.

- We have tried to change the Batch size - this does not help

- When we run the SQL from the VB Demo application - it does not return any rows whatsoever.

- Our company has not been able to generate Invoices, reports and statements for over 2 weeks now and I am getting serious greif from management.

Using QuickBooks 2007 Premier USA Edition Release R8P and QODBC v7.10.00.231 I conducted the following tests using VB Demo:

Using QODBC Optimizer
Select {fn CURTIME()} AS "RowTime", "TimeModified", "CustomerRefListID",
"RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount" from InvoiceLine
WHERE (TimeModified >= {ts'2001-09-01 00:00:00.001'}
AND TimeModified < {ts'2007-12-31 00:00:00.001'})

Start Time: 17:18:01- Finish Time:17:18:14 - 13 seconds for 846 rows

Calling directly from QuickBooks using UNOPTIMIZED tag
Select {fn CURTIME()} AS "RowTime", "TimeModified", "CustomerRefListID",
"RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount" from InvoiceLine UNOPTIMIZED
WHERE (TimeModified >= {ts'2001-09-01 00:00:00.001'}
AND TimeModified < {ts'2007-12-31 00:00:00.001'})

Start Time: 17:27:50 - Finish Time:17:28:04 - 14 seconds for 846 rows

Using QODBC Optimizer
Select {fn CURTIME()} AS "RowTime", "TimeModified", "CustomerRefListID",
"RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount" from InvoiceLine
WHERE (TimeModified >= {ts'2007-12-15 00:00:00.001'}
AND TimeModified < {ts'2007-12-16 00:00:00.001'})

Start Time: 17:23:26 - Finish Time:17:23:28 - 2 seconds for 115 rows

Calling directly from QuickBooks using UNOPTIMIZED tag
Select {fn CURTIME()} AS "RowTime", "TimeModified", "CustomerRefListID",
"RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount" from InvoiceLine UNOPTIMIZED
WHERE (TimeModified >= {ts'2007-12-15 00:00:00.001'}
AND TimeModified < {ts'2007-12-16 00:00:00.001'})

Start Time: 17:26:11 - Finish Time:17:26:13 - 2 seconds for 115 rows

This clearly indicates there are no issues with QODBC when using the most current US update patch for QuickBooks 2007. 

Testing using QuickBooks 2006/07 Premier Australian Edition Release R1P and QODBC v7.10.00.231 also returned the expected performance results when executed in VB Demo:

Using QODBC Optimizer
Select {fn CURTIME()} AS "RowTime", "TimeModified", "CustomerRefListID",
"RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount" from InvoiceLine
WHERE (TimeModified >= {ts'2001-09-01 00:00:00.001'}
AND TimeModified < {ts'2007-12-31 00:00:00.001'})

Start Time: 12:35:59 - Finish Time: 12:36:03 - 4 seconds for 219 rows

Calling directly from QuickBooks using UNOPTIMIZED tag
Select {fn CURTIME()} AS "RowTime", "TimeModified", "CustomerRefListID",
"RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount" from InvoiceLine UNOPTIMIZED
WHERE (TimeModified >= {ts'2001-09-01 00:00:00.001'}
AND TimeModified < {ts'2007-12-31 00:00:00.001'})

Start Time: 12:37:40 - Finish Time: 12:37:43 - 3 seconds for 219 rows

You can try changing the recordset cursors type to forward only and to use the server instead of the client in your MSAccess VBA using ADO 2.6 application :
adOpenForwardOnly
adLockOptimistic
adUseServer

However, you will need to resolve your issues first using VB Demo. Your optimized file is also most likely corrupt, see: How do I switch OFF or RESET the QODBC optimizer? to reset the file.

See also: Can I get some examples of how to use QODBC via Visual Basic? and Ado Item Query returns EOF in Delphi when wrong Cursor type is used for further information on Cursors.

Thanks - A bit more info:

I changed the cursor to adOpenForwardOnly. This is appears to have improved the performance somewhat.

I am running the following SQL

Select * from InvoiceLine WHERE (TimeModified >= {ts'2001-01-01 00:00:00.001'}  AND TimeModified < {ts'2007-12-31 00:00:00.001'})

This will return aproxiumately 90,000 rows

Whe returned I loop through the recordset and insert the data into a SQL Server database.

The process starts off relatively fast but slows progressively until it reaches 40,000 records at which point it is only doing 1 rows every 3 or 4 seconds. I also notice that the Access process consumes more and more memory 500 MB+.

Note the chunk size is set to 2000.

This query:

Select * from InvoiceLine WHERE (TimeModified >= {ts'2001-01-01 00:00:00.001'}  AND TimeModified < {ts'2007-12-31 00:00:00.001'})

extracts all the transactions from 2001and will overrun your system resources. Normally you would only be extracting modified transactions since, say, yesterday:

Select {fn CURTIME()} AS "RowTime", "TimeModified", "CustomerRefListID",
"RefNumber", "InvoiceLineItemRefListID", "InvoiceLineDesc", "InvoiceLineRate",
"InvoiceLineAmount" from InvoiceLine
WHERE (TimeModified >= {ts'2007-09-12 00:00:00.001'}
AND TimeModified < {ts'2007-09-15 00:00:00.001'})

If you're going to extract all the transactions since 2001, do it year blocks like this:

For 2001
Select * from InvoiceLine WHERE (TimeModified >= {ts'2001-01-01 00:00:00.001'}  AND TimeModified < {ts'2002-01-01 00:00:00.001'})
For 2002
Select * from InvoiceLine WHERE (TimeModified >= {ts'2002-01-01 00:00:00.001'}  AND TimeModified < {ts'2003-01-01 00:00:00.001'})
For 2003
Select * from InvoiceLine WHERE (TimeModified >= {ts'2003-01-01 00:00:00.001'}  AND TimeModified < {ts'2004-01-01 00:00:00.001'})
For 2004
Select * from InvoiceLine WHERE (TimeModified >= {ts'2004-01-01 00:00:00.001'}  AND TimeModified < {ts'2005-01-01 00:00:00.001'})
For 2005
Select * from InvoiceLine WHERE (TimeModified >= {ts'2005-01-01 00:00:00.001'}  AND TimeModified < {ts'2006-01-01 00:00:00.001'})
For 2006
Select * from InvoiceLine WHERE (TimeModified >= {ts'2006-01-01 00:00:00.001'}  AND TimeModified < {ts'2007-01-01 00:00:00.001'})
For 2007
Select * from InvoiceLine WHERE (TimeModified >= {ts'2007-01-01 00:00:00.001'}  AND TimeModified < {ts'2008-01-01 00:00:00.001'})