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 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: 

sp_optimizefullsync ALL
 


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


Or

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 query retrieval performance. I suggest you please let it run the above command entirely. 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 entirely. Do not kill this command. It is not advisable to kill/close the application while building the Optimizer file.
 
Please Note: Optimizer file is for each user and cannot be shared.

4. Backup Optimizer File.
 
Please backup 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 file using changed and deleted data. It is useful for ensuring 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, showing 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.

 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

(2 vote(s))
Helpful
Not helpful

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