[QODBC-ALL] How Optimizer Works
Posted by Jack - QODBC Support on 26 July 2017 09:55 AM
How Optimizer Works
I am using QODBC to access QuickBooks Data. I am using optimizer & I want to know How Optimizer Works.
The optimizer brings some data to a local cache to increase query retrieval performance. Below is the default setting of QODBC Optimizer.
Here is how the optimizer works with the default configuration.
1. You execute any query, for example: "Select * from Invoice."
2. QODBC will check the Optimizer table for the Invoice.
3. As it is the first time you are accessing the table, QODBC will get all the data of that table & its child tables (e.g., for the Invoice table, it is Invoice, InvoiceLine & InvoiceLinkedTxn). E.g., There are 50000 records.
QuickBooks company file is not a database file but a flat file system. QuickBooks SDK returns data in a set of 500 records per request, and it is in XML format.
QODBC has to convert that XML format to a table format.
The first time accessing any table will be slow. Please do not close or kill the process.
Querying the same table a second time onwards, QODBC will return the results faster.
4. QODBC will add the data received from QuickBooks SDK to the optimizer file. QODBC will update the timestamp to the optimizer file.
5. QODBC will then read records from the optimizer and file return the data request back to the calling application.
6. Next time, when you execute "Select * from Invoice."
7. QODBC will check the Optimizer table for the Invoice.
8. As there is data and the last timestamps, QODBC will get the last timestamp from the optimizer file.
9) QODBC will fetch all the data added/modified/deleted after the previous sync timestamp from QuickBooks using QuickBooks SDK.
9. As QODBC is querying limited data, the data transfer is faster. e.g., if there are 40 records modified and ten added, ten deleted instead of reading 50000 records, only 60 records will be processed.
10. QODBC will add the delta changes to the optimizer.
11. QODBC will then read records from the optimizer and file return the data request back to the calling application.
I am fetching the top 10 records from Invoice (or any other table), and QODBC is still reading all the records from QuickBooks.
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.