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 see the IR in QB. But when I run the QODBC Test Tool or 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 using 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. The report is used to predict and budget revenue. When we updated 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 before the upgrade from 15 to 16. 

Problem Description 3:

We use an access database every two weeks 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 record set.

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

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 hung with the circle 

Problem Description 5:

When I filter the QODBC Transaction Table to only show Income, Expense, Other Income, and Other Expenses 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 are missing from QODBC. I tried "Rebuilding" the QB Company File, but it did not help. Any ideas?

Solution:

1. Rebuild your company file

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

Please follow the instructions of QuickBooks and make a backup before rebuilding. 

2. Delete the Optimizer file 

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

See also: How do I set up 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 following data connections.

For QODBC 13.0.0.292 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 to the how to Remove Optimizer file.

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

1) Close all applications other than QuickBooks/Reckon.

2) Find the file with the .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 an Optimizer file

Please run the below command in QODBC Test Tool: 

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

5. Close QODBC Test Tool. 

6. Enable QODBC Status Panel:

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.

 Or

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'}.

Suggestion:

Suppose you have a large QuickBooks company file with several years of data.
The QuickBooks SDK has to scan and process many records to extract data, resulting in performance issues and slowness.
Historical data may not be required in day-to-day transactions/reports.
It is advisable to reduce the size of large QuickBooks company files for better performance.
To reduce the size of the QuickBooks company file, you can consider archiving old data.
To archive old data, you can create a copy of the company file and save the company file under a new name.
Condense the new QuickBooks Desktop company file.
You should notice performance gains when using a condensed QuickBooks company file.
You can always refer to the archived data at any time using the QuickBooks application or QODBC.

Refer to
https://quickbooks.intuit.com/learn-support/en-us/help-article/data-utilities/use-condense-data-utility/L7zprPIcj_US_en_US
https://quickbooks.intuit.com/learn-support/en-uk/other-questions/quickbooks-desktop-2020-condense-data-utility/00/532124
https://quickbooks.intuit.com/learn-support/forums/searchpage/tab/message?advanced=false&allow_punctuation=true&q=condense%20data
https://quickbooks.intuit.com/learn-support/en-us/other-questions/condensing-a-large-file/00/573043
https://quickbooks.intuit.com/learn-support/en-us/other-questions/best-way-to-split-one-company-file-into-two-originally-used-for/00/810101

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

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