[QODBC-Desktop] - Slow Query Performance After Upgrading QODBC: Impact of the VERIFY Statement
Problem Description:
After upgrading QODBC to version 24.0.0.x (build 358 or later), some users notice that queries which previously completed in a few seconds suddenly take several minutes to finish. The performance degradation is most pronounced when the VERIFY statement is included in queries.
Affected products: QODBC Desktop Expected outcome: After removing the VERIFY statement from queries and rebuilding the Optimizer, query execution times return to their expected performance levels (seconds instead of minutes).
Symptoms:
- Queries that previously completed in a few seconds now take multiple minutes (e.g., 12–14 minutes for queries that used to run in 6–11 seconds).
- The performance slowdown begins immediately after upgrading QODBC to version 24.0.0.x.
- The issue is intermittent - some queries are affected while others run normally.
- The problem often resolves temporarily when the Optimizer file is rebuilt, but recurs over time.
- Testing with the QODBC Test Tool reveals a dramatic difference between queries with and without the VERIFY statement (e.g., 837 seconds with VERIFY vs. 6 seconds without).
Cause:
The root cause is the use of the VERIFY statement in optimized QODBC queries.
When a query includes the VERIFY keyword, it forces a full resync between the QODBC Optimizer local cache and QuickBooks before the query starts. This operation is functionally equivalent to executing sp_optimizefullsync ALL, which rebuilds the entire Optimizer cache for every single query execution.
In QODBC versions prior to 24.0, the VERIFY statement may have been necessary because the Optimizer cache synchronization was less reliable. Starting with version 24.0.0.x (build 358+), the Optimizer's incremental sync mechanism is significantly improved, making the VERIFY statement largely unnecessary for most queries.
Important
The VERIFY statement is not broken - it works exactly as designed. However, using it on every query negates the performance benefits of the QODBC Optimizer by forcing a complete cache rebuild each time.
Note
From internal analysis, QODBC v358 and later versions are actually faster than previous versions when queries are written without the VERIFY statement. The perceived slowdown was caused by continuing to use a now-unnecessary optimization.
Environment:
| Product | Version / Detail |
| QODBC Desktop |
24.0.0.358 and later (affected if VERIFY statement is used) |
| QuickBooks Desktop |
All editions (Enterprise, Pro, Premier) |
| Data Source Tools |
MS Access, Excel Power Query, SQL Server linked tables, any ODBC consumer |
Solution:
- Rebuild the QODBC Optimizer cache. Delete the existing Optimizer file and rebuild it to ensure a clean, up-to-date cache. This removes any stale or corrupted cached data that may be contributing to slow performance.
How to Remove the Optimizer File
- Regenerate the Optimizer: Open the QODBC Setup Screen and navigate to Optimizer → Reload All Data. Alternatively, run the following command in the QODBC 32-Bit Test Tool:
sp_optimizefullsync ALL
This process may take 4–24 hours or more depending on the size of your QuickBooks company file. Allow it to complete without interruption - do not close the application or kill the process while it is running.
- Remove the VERIFY statement from all queries. Search your stored procedures, SQL scripts, and application code for any query containing the
VERIFY keyword and remove it. The Optimizer handles synchronization automatically in v358+ without needing an explicit verification step on every query.
- Test your queries using the QODBC Test Tool. Execute each query both with and without the VERIFY statement and record the execution times. You should observe a significant performance improvement (often several orders of magnitude) when VERIFY is removed.
Open the QODBC 32-Bit Test Tool and run your queries normally, noting the elapsed time.
- Re-link MS Access tables (if applicable). If you use QODBC with MS Access through linked tables, delete the existing linked tables and re-link them to ensure the connection is using the updated Optimizer cache.
How to Re-Link QuickBooks Data in MS Access
- Test your application. Run your full application workflow and confirm that query execution times have returned to expected levels.
When Should You Use the VERIFY Statement?
The VERIFY statement forces a complete synchronization between the Optimizer cache and QuickBooks. In most cases, this is unnecessary. However, you may want to use it occasionally (e.g., once per day or before a critical report) if:
- You need guaranteed real-time data from QuickBooks at a specific point in time.
- The Optimizer cache may have become out of sync after an unexpected interruption.
- You are running a critical financial or compliance report that requires absolute data freshness.
In these cases, run VERIFY as a one-time action before the report, not as part of every data retrieval query.
SQL Examples:
Before - Query with VERIFY (slow):
SELECT ListID, FullName, Balance FROM Customer WHERE IsActive = True VERIFY
Execution time: ~837 seconds (with VERIFY)
After - Query without VERIFY (fast):
SELECT ListID, FullName, Balance FROM Customer WHERE IsActive = True
Execution time: ~6 seconds (without VERIFY)
Example - Full Invoice query with date filtering:
SELECT TxnDate, RefNumber, SubTotal FROM Invoice WHERE TxnDate >= {d'2026-01-01'} ORDER BY TxnDate DESC
Example - Optimized query with JOIN:
SELECT cust.ListID, cust.FullName, inv.RefNumber, inv.SubTotal FROM Customer cust INNER JOIN Invoice inv ON cust.ListID = inv.CustomerRefListID WHERE inv.TxnDate >= {d'2026-01-01'}
Example Scenarios:
Scenario 1 - Daily ETL job slows down after upgrade:
A business uses a SQL Server stored procedure with QODBC to pull customer and invoice data every night. After upgrading QODBC from 23.0 to 24.0, the ETL job that previously completed in 10 minutes suddenly took 13 hours. The issue was traced to a VERIFY statement in the main query. Removing VERIFY reduced the job runtime back to under 5 minutes.
Scenario 2 - MS Access report takes too long:
An MS Access application uses QODBC linked tables for monthly financial reporting. After the QODBC upgrade, reports that once took seconds now take over 10 minutes. Rebuilding the Optimizer cache and removing VERIFY from the underlying queries restored normal performance.
Additional Notes:
- Why did it work before? In earlier versions of QODBC, the Optimizer cache synchronization was less reliable. The VERIFY statement ensured data consistency at the cost of performance. Starting with version 24.0.x, the Optimizer's incremental sync is much more efficient - explicit verification on every query is no longer necessary.
- Performance baseline: In testing, QODBC v358+ consistently outperforms earlier versions when queries are written correctly (without VERIFY). The slowdown reported by users was not due to the new version being slower - it was caused by applying an old optimization pattern that is now counterproductive.
- Optimizer rebuild interval: You do not need to rebuild the Optimizer daily. Let the incremental sync mechanism handle routine updates. A full rebuild (sp_optimizefullsync) is sufficient once every few weeks or after significant data changes in QuickBooks.
- For MS Access users: After rebuilding the Optimizer, always re-link your QODBC tables in MS Access to ensure they connect to the updated cache.
Related Articles:
How to Setup QODBC Optimizer and Where Are the Optimizer Options Troubleshooting: Slow Performance / Cannot Get Full Records from QuickBooks How to Remove the Optimizer File How to Re-Link QuickBooks Data in MS Access Understanding QODBC Optimizer Behavior
Keywords:
QODBC Slow Query Performance VERIFY Statement Optimizer sp_optimizefullsync Upgrade QODBC 24.0 Build 358 Full Resync Cache Rebuild MS Access SQL Server QuickBooks Desktop Execution Time Optimized Query
Support Tickets: FYF-842-89808
|