I am working on a project for a client that is using Quickbooks version 7.   The essence of the project is to select a distinct set of items ordered by each client and then re-populate a custom field that has added to the customer table.   I am using QODBC 8.0 and access 2003.

After a number of trial and error attempts and slow response times from QB using the QODBC driver I have opted to do most of the work in access instead of QODBC to do the select distinct that I need.

At this point all I am trying to do is pull across the listid, customer name and item name from all invoices created since 1/1/2005 for active customers.  The append query has been running more than 4 hours so I am wondering what I have done wrong.    

here is the SQL that was created by access:
  INSERT INTO [PS Customer Work0] ( CustomerRefListID, CustomerRefFullName, TxnDate, Name )
SELECT InvoiceLine.CustomerRefListID, InvoiceLine.CustomerRefFullName, InvoiceLine.TxnDate, Item.Name
FROM (InvoiceLine INNER JOIN Customer ON InvoiceLine.CustomerRefListID = Customer.ListID) INNER JOIN Item ON InvoiceLine.InvoiceLineItemRefListID = Item.ListID
WHERE (((InvoiceLine.TxnDate)>[Forms]![Update Display Name]![DateLastRun]) AND ((Customer.IsActive)=True));

The QB invoice line file that I am processing has almost 600,000 records in it and there are about 4,000 customer.   I have imported the table directly into access for testing purposes and this query will process the file and produce the desired output in about 2 minutes, but when testing using linked tables that point to QB it has been over 4 hours and yet to finish.

Do you have any suggestion that will speed up this query?  

Tom,   Thanks for the quick reply!

If I remove Customer from the query then I can't test the column ISActive to select only the active customers in the result set.   Also in the customers' copy of QB the data in the column item.name is not the same as Invoiceline.InvoiceLineItemRefFullName.   For example,  Item.Name has a value of "PHL" and both item.FullName and Invoiceline.InvoiceLineItemRefFullName have the value "Power Heeler Boxed:PHL" for the same ListID and it is value from item.Name that I need in the process.

The process I am writing needs to run on a daily basis so that the new data can be available the start of each business day so I am even considering importing the Customer and Item table into Access since they are relatively small so that I can pull the data from InvoiceLine based on just Txndate without other joins.   I would prefer not to do this but speed of the query is this first query is critical since it has to process 600,000 records at minimum.

I have read about NOSYNC and UNOPTIMIZED options on the Select statement.  Would either of these help?   

I am open to any suggestions and appreciate your help.

Thanks! 

WHERE (((InvoiceLine.TxnDate)>[Forms]![Update Display Name]![DateLastRun])

to a constant:

WHERE InvoiceLine.TxnDate> {d'2008-01-01'}

QODBC sends a XML request to the QuickBooks SDK which returns a XML document. This can be very big, and for 600,000 rows it will bring any computer to a stand still. You need to limit your date ranges into smaller subsets like this:

WHERE InvoiceLine.TxnDate>= {d'2008-01-01'} and InvoiceLine.TxnDate< {d'2008-06-01'}

Tom,

I tested your suggestion of using  WHERE InvoiceLine.TxnDate> {d'2008-01-01'} and this syntax was rejected and produced an error message that said:  Malformed GUID in Query Expression '(((InvoiceLine.TxnDate)>{d'2008-01-01'}))'

I replaced {d'2008-01-01'}  with #1/1/2008# and the query executed successfully.   I tried your date syntax in VB Demo and it worked with no problem.  Why can't I use your suggested date format in a DoCmd.RunSQL?   I have also tried to test with using NOSYNC and UNOPTIMIZE and both of those syntax statement are rejected when I execute them as well.

By the way, I ran a full test using the original query I posted in this thread and it ran 12 hours before finally completing so I need to find a solution that will work in a reasonable time frame, so any suggestions are welcome.

See: How are dates formatted in SQL queries when using the QuickBooks generated time stamps? 

The fastest way to process the query is to use a single table. When using multiple tables the order of the tables is critical as well as the links between them and what filtering is used. Pass-through queries in MS Acces allow the use of the NOSYNC and UNOPTIMIZED tags.

As you are working with large datasets, set your Iterator or Chunk Size to 20,000:-

Iterator or Chunk Size determines the size of the rowsets that are retrieved from QuickBooks. Set this value high if you are exporting records from QuickBooks. The maximum Iterator or Chunk size that can be set is 100000 (higher than that and it resets itself back to the default of 500).

tom,

I have run into a complete stand still.   I ran a test last night using all of the suggestions that you have provided which are:

1. set chunck size to 200000
2. Run the query against a single file (InvoiceLine)
3. Use queries that grab data in smaller chunks  (2006, 2007, 2008) instead of grabbing them all at once
4. Run the application in single user mode to reduce QB overhead

The results are that just the first query grabbing the invoice line data for 2006 failed to finish in over 14 hours of processing.   I had all verbose Optimizer and driver staus boxes checked and from what I can see the optimizer hung while processing the INVOICE file as the message indicated that 0 out of 0 invoices had been processed.    The query that was running at the time only selected data from the InvoiceLine file so I don't understand why the optimizer is working on the INVOICE file.

To ensure that everything was working with clean files, I had also deleted and reallocated the optimizer file according to the instructions I found in your FAQ database.   The first step in my process was to import the Customer table and Item table into my Access database since they are fairly small.  Both of these tables were optimized successfully and my import of those tables completed successfully.  The next step was to run a SELECT query on INVOICE Line for all invoices where TxnDate was between 1/1/2006 and 12/31/2206.   This is where the optimizer stall trying to optimize the INVOICE table.

Suggestions?

Tom, 

I had unlinked and re-linked all tables before I started this so that I could move from my small test copy of QB over to the production version of QB.

Once this has been done, unlink all the QODBC tables in your existing MS Access .mdb(s) and then relink them to use these new connection settings.

For the complete details of all the QODBC optimizer settings, see: How do I setup the QODBC Optimizer? Where are the Optimizer options?