Knowledgebase: TroubleShooting
[QODBC-ALL] Troubleshooting: Slow Performance / Cannot Get Full Records from QuickBooks / How to find missing data
Posted by Rajendra Dewani (QODBC Support) on 16 October 2012 11:13 AM

Troubleshooting: Slow Performance / Cannot Get Full Records from QuickBooks / How to find missing data

Problem Description 1:

1. QODBC is showing Partial data, or a few of my data is missing in the result set.

2. I can create an IRL and IR using QODBC, and I am able to see the IR in QB. But when I am running the QODBC Test Tool or running SQL using QODBC, it says record not found. I am unable to retrieve anything that I added after 11/18. But I can see the data in QB and process it in QB.

I also tried creating a Bill in QB and tried to use a simple SQL to run in QODBC Test Tool nothing showed up.

Problem Description 2:

We have a custom report that summarizes custom data fields in QuickBooks. When we updated from QB ES 15 to 16, the report stopped working, stating that we needed to update the QODBC driver version. We installed the version within QuickBooks, however, the report would return 'blank' reports using a list of 30-40 different parameters. An investigation by a Crystal developer revealed that the report stops returning ANY data at a certain number of parameters passed but works as designed up to that point. We didn't see this limitation prior to the upgrade from 15 to 16. The report is used to predict and budget revenue. 

Problem Description 3:

Every two weeks, we use an access database to read employee paycheque details from QuickBooks.

The last two times, the first time we ran the query, we were missing exactly one record.

Both times, simply rerunning the query gave us the full expected recordset.

We did not even close and re-open the database or QuickBooks between each running of the query.

What could be causing this? 

Problem Description 4:

Last Friday, we ran updates on QuickBooks 2015. For some reason, our MS Access application stops getting results from queries. I noticed QODBC Test Tool still works.

I even tried a simple query, and it just hangs with the circle 

Problem Description 5:

When I filter the QODBC Transaction Table to only show Income, Expense, Other Income, and Other Expense and only transactions in our current Fiscal Year, it does not match the QB generated P&L Report for the same time frame. It seems like random sections of certain journal entries is missing from QODBC. I tried "Rebuilding" the QB Company File, but it did not help. Any ideas?



1. Rebuild your company file

To Rebuild the QuickBooks company file, please select the File->Utilities->Rebuild Data option:

Please make sure you follow the instruction of QuickBooks and make a backup before rebuilding. 

2. Delete Optimizer file 

Check the Use Optimizer option to reactivate and reset the Optimizer.

See also: How do I setup the QODBC Optimizer? Where are the Optimizer options? 

In Case, your Optimizer file is Corrupted you can delete the Optimizer file and QODBC will generate a new one in the next data connect.

For QODBC and Above, you can also Reset the Optimizer file with a Single Click from
QODBC Setup Screen->Optimizer Window->Reset Optimizer File(s)



Also Refer: How to Remove Optimizer file

If the above steps do not work, Please follow below Manual Steps for resetting Optimizer

1) Close all applications other than QuickBooks/Reckon.

2) Find the file with .opt extension at the path you had assigned to "QODBC optimizer" and delete the optimizer file.
(The default path is %AppData%\QODBC Driver for QuickBooks\Optimizer.
Here are the steps to locate the folder
Click on Start Button,
Click on Run,
Type the below command & press enter
%appdata%\QODBC Driver for QuickBooks\Optimizer
This should open a folder)

And also, please refer to How to switch OFF or RESET the QODBC Optimizer for more information about resetting the QODBC optimizer. 

3. Build Optimizer file

Please run below command in QODBC Test Tool: 

sp_optimizefullsync ALL

 QODBC Support Wizard is used to test QODBC SQL queries only and is not a development tool.


sp_optimizefullsync SalesOrder
sp_optimizefullsync Invoice


Please Note: You need to optimize the parent table only. There is no need to optimize the child table separately.

For Example:

When you optimize the "Invoice" table, QODBC will automatically optimize the child table "InvoiceLine", i.e., Invoice & InvoiceLine will be optimized.

When you optimize the "Bill" table, QODBC will automatically optimize the child tables "BillItemLine' & "BillExpenseLine", i.e. Bill, BillItemLine & BillExpenseLine will be optimized.

Please refer: How to execute SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC for Selected Tables

The sp_optimizefullsync command will bring some data to a local cache to increase retrieval performance for queries. I would like to suggest you to, please let it run the above command completely. The above command may take some time, depending on your records in QuickBooks. It may take 4-24 hours or more, depending on the number of records in the QuickBooks company file. Please let this command run completely do not kill this command. It is not advisable to kill/close the application while it is building the Optimizer file.
Please Note: Optimizer file is for each user and cannot be shared.

4. Backup Optimizer File.
Please take a backup of the Optimizer file in your backup folder after the Optimizer file build-up. This might help you if the optimizer file gets corrupted. You can save the number of hours for building a new optimizer file if you have this file. You can restore the backup in case of Optimizer corruption. You need to remove the corrupted .opt file & place the Optimizer backup file in your Optimizer path (the default path is %AppData%\QODBC Driver for QuickBooks\Optimizer). After restoring the .opt file you need to run sp_optimizeupdatesync ALL to get delta changes. This command will synchronize all table data with the QuickBooks data file using changed and deleted data. It is useful for making sure that the Optimizer is up to date with the QuickBooks company file.  

5. Close QODBC Test Tool. 

6. Enable QODBC Status Panel:

Please also enable the QODBC status panel via QODBC Setup Screen->Message Window->Select "Display Driver Status" and "Display optimizer Status" options.


And then the next time you run a query, if you see “Waiting for QuickBooks”, it means QuickBooks is taking the time to process the request. There will be a status panel at the lower right corner of your screen, which will be shown a window with information on what QODBC is working at. Please note the step on which QODBC spends the most time or gets stuck. 

7. Start your application again & test again. 

Additional Information:

Test Without QODBC optimizer

First, please turn off your QODBC optimizer (QODBC Setup Screen->Optimizer Window->Uncheck "Use Optimizer" option), then use your query in QODBC Test Tool to test again to see if it can bring back those missing records. And if it works, then please follow the below steps


To temporarily bypass your optimizer settings and extract the latest information, you can add the unoptimized tag after the table name like this to your queries:

    select * from InvoiceLine unoptimized where TxnDate >= {d'2006-03-06'}.




Suppose you have a large company file with multiple years. QuickBooks and QuickBooks SDK would require more time to process your data required.

We would suggest splitting the company file to make it smaller. You can keep the historical data in one company file and the latest one in another.

This way, your company file will be smaller and with a smaller number of records.

Refer to


If the Above Solutions don't work, please post a ticket to us, and we will do a further investigation into your issue.

Also, Refer:
Troubleshooting - Cannot load Customer table records


Keywords: qb odbc slow, multiple web queries into excel slow running, excel running background query slow, qodbc and excel connection speed

(1 vote(s))
Not helpful

Comments (0)
Post a new comment
Full Name:
CAPTCHA Verification 
Please complete the captcha below (we use this to prevent automated submissions).