How QODBC Optimizer Works

Problem Description:

I am using QODBC to access QuickBooks Data. I am using the QODBC optimizer, and I want to know more about How Optimizer Works.

I am able to connect to QODBC but when I fetch any table it takes a lot of time.

I am able to fetch records from a few tables but for other tables, QODBC is just processing and not returning any records.

Solution:

The optimizer brings some data to a local cache to increase query retrieval performance. Below is the default setting of QODBC Optimizer.

The QODBC Optimizer is designed to improve performance when accessing QuickBooks data by caching results locally. This article explains how the optimizer works by default, step-by-step.

When you run a query through QODBC (for example, SELECT * FROM Invoice), QODBC communicates with the QuickBooks SDK to retrieve the requested data. Since the QuickBooks company file is not a traditional database, but rather a flat file system, data retrieval and conversion are handled in a specialized way to optimize speed and reliability.

Here is how the optimizer works with the default configuration.

Step-by-Step Process

Step 1: Query Execution

You execute a query such as:

Step 2: Check for Optimizer Data

QODBC checks the Optimizer Table to see if data for the requested table (in this example, Invoice) already exists.

Step 3: First-Time Data Retrieval

Assuming there are 50,000 records in the Invoice table.

If this is the first time accessing the table:

• QODBC retrieves all records from QuickBooks, including data from related child tables (for example, Invoice, InvoiceLine, and InvoiceLinkedTxn).

• The QuickBooks SDK returns data in XML format, in batches of 500 records per request.

• QODBC converts this XML data into a table format for use by your application.

Note: Since this is a full data retrieval, it may take some time. Please do not close or terminate the process during this initial load.

Step 4: Data Storage in Optimizer

After retrieving the data:

• QODBC saves the data into the Optimizer File.

• The optimizer records the timestamp of the data retrieval.

Step 5: Data Returned to Application

Once the optimizer is updated, QODBC reads from the optimizer file and returns the results to your application.

Step 6: Subsequent Queries

The next time you execute the same query (e.g., SELECT * FROM Invoice):

1. QODBC again checks the Optimizer Table for Invoice.

2. It finds that the data already exists and retrieves the last synchronization timestamp.

3. QODBC requests only the changes (added, modified, or deleted records) since the last timestamp using the QuickBooks SDK.

Step 7: Delta Update

Because QODBC now retrieves only changed data, the process is much faster.
For example:

• Out of 50,000 total records, only 60 have changed (40 modified, 10 added, 10 deleted).

• QODBC processes only those 60 records instead of the full 50,000.

Step 8: Optimizer Update

QODBC applies the delta changes to the optimizer file to keep it up to date.

Step 9: Return Updated Data

Finally, QODBC reads the updated data from the optimizer file and returns the complete result set to the calling application

Key Points to Remember

• The first query on any table will take longer because QODBC must retrieve and cache all records.

• Subsequent queries are significantly faster, as only new or changed data is fetched.

• Do not interrupt the first-time data load process to avoid incomplete optimizer data.

Question:

I am fetching the top 10 records from Invoice (or any other table), and QODBC is still reading all the records from QuickBooks.

Answer:

If you are not using the optimizer or if you are querying the table for the first time, QODBC will have to build the optimizer before returning the records.

Even if you have requested the top 10, QODBC has to fetch all the records from QuickBooks to show the result matching the criteria.