Knowledgebase
[QODBC-ALL] How to setup QODBC Optimizer and where are the Optimizer options
Posted by Juliet (QODBC Support) on 21 February 2010 07:12 AM

Where are the Optimizer options

Locate QODBC Optimizer

     Locate the QODBC Driver for QuickBooks program group on your desktop. Click on the QODBC Setup Screen icon to launch the setup program supplied with QODBC.

      The following general setup screen will become available. Click on the Optimizer tab page.

QODBC Optimizer Setup

Instructions

     With QODBC Version 10.0 a new data importing technology has been added to QODBC resulting in faster initial data reads and subsequent queries than previous editions of the driver. Initial optimization of data has been benchmarked at up to a 30% reduction in load time. The first time you access a given QuickBooks table, QODBC by default optimizes access to that data, so the next time you need data from that table, it will be much faster.

     QODBC version 10.0 adds new SQL Syntax our customers have been asking for such as UNION statements and CASE statements. Also added is support for QuickBooks SDK 9 and US Multi-Currency support.

     The read-only desktop edition of QODBC has again been chosen to be included as a feature in the QuickBooks Enterprise Edition 2010 product, on the main file menu under Utilities. If you need a read-only edition of QODBC and have this version of QuickBooks, you are ready to go, no purchase required! We have also now been chosen to be included in QuickBooks Enterprise editions in UK, Canada, and Australia.

QODBC Optimizer Setup     

     My recommended settings for using QODBC are shown here, read below for an explanation of all the options available to you.

Use Optimizer brings some data to a local cache to increase retrieval performance for queries. Check to activate the Optimizer.

Optimizer Database Folder: specifies where the local data store will be saved on disk. The default location %AppData%\QODBC Driver for QuickBooks\Optimizer is under your Windows login account name under your Documents and Settings folder. The data will be stored in a file in this folder with a name similar to the company file name with a .opt extension. If you store that data somewhere other than the default click the browse button and select the destination folder. With very large company files we suggest running synchronization after hours.

Keep my optimized data up to date as of: when you choose the optimizer to synchronize itself with your Company data. Depending on how static your data need is and the size of the file, you can opt for various time frames of synchronization. The farther down the list you go the less synchronization will occur and your queries will run much faster, but will be using less current data than the company file itself.

The start of every query is the default setting using the most current data. If your query needs require the latest up to the minute data and speed is not the primary focus use this default setting. Please keep in mind that the QODBC Optimizer is local to your computer only, so when other users change things in QuickBooks how does QODBC know? Well, by default the optimizer will update new and changed entries in a table from QuickBooks first and then execute the query against the locally optimized table. This is faster than reading everything out of QuickBooks every time, especially the more data you have.

The start of every new connection (with "Load Updated Data" first) is used if you don't need to update the optimized data while you are for example running queries or importing or exporting data. The optimized data is updated upon a new connection and left updated until the next new connection occurs again.

The start of every new connection (with "Reload All Data" first) is used if you need to rebuild optimized data from scratch before running important queries (like Sales Commission) or importing or exporting data. The optimized data is rebuilt upon a new connection and left an updated until the next new connection occurs again.

The end of yesterday is used for a better balance between speed and data updating and would be the option that works best for most users. It only has to synchronize each table once per day and the data will be current as of the end of the previous day.

The end of last month option is a great speed boost for running last month or last year’s queries. It does not need to synchronize again to get the data.

The last time I pressed one of the load data buttons options allows you to control the synchronize timing. Synchronizing only occurs when you manually run it by pressing the Load Updated Data in the QODBC setup screen.

For balances and stock quantities: the option Nulls - will return NULLs for all fields that cannot be kept in sync, Dirty Reads - means return the most current value that was loaded into the optimizer cache, and Real Time uses the slower method to get the data directly out of QuickBooks making sure you get the most current value for the requested fields. (Real Time is the previous method used).

Optimize data after an Insert or Update causes QODBC to do Load Updated Data after an insert or update is executed on a table. Useful for programmers who want to test that their data has successfully been written to QuickBooks.

Multi-Table Sync option is unchecked by default for all non-USA enterprise users. If checked, the QODBC optimizer will use other tables to re-sync tables, however, this will slow down performance. When off all Multi-Table Sync fields become Real-Time fields. Note: This feature is basically ignored and set to unchecked if the version is QBOE or SDK is less than v3 (used by QuickBooks 2004 USA and all QuickBooks versions outside the USA). Prior to SDK v3 the other multi-sync tables did not exist so using real data for those fields is safer.

Load Updated Data: This button is used to Synchronize your optimized data with your company file. This will only load the data that has changed since the last automatic or manual synchronization.

Reload All Data: This button is used to completely rebuild the optimized data from scratch. It is useful when you start with a new company if you do not want to synchronize the tables as you first encounter them and make sure that your data is 100% current.

See Also: How to switch OFF or RESET the QODBC optimizer

Removing/deleting optimizer file.

To delete the optimizer file. Please close/disconnect all the applications connected to QODBC

Start "QODBC Setup Screen" & Switch to Optimizer tab

Click "Reset Optimizer File(s)"

Click "Yes" to delete all the Optimizer files.

Please note: All the Optimizer files under the Optimizer Database folder will be removed. If you wish to delete a single company file's optimizer.

Optimizer file can be found at "%appdata%\QODBC Driver for QuickBooks\Optimizer"

For Example: C:\Users\Raj\AppData\Roaming\QODBC Driver for QuickBooks\Optimizer

Use Command Line to run QODBC Optimizer

    You can also schedule full or update optimization overnight using either: QODBCFUL.EXEor QODBCUPD.EXE. Both set the error level variable if errors are detected, and error messages are written into the QODBCLOG.TXT log file. Passing a DSN on the command line to these programs will choose the QuickBooks company file you wish to use.

Note: QODBC "Auto Login" unattended mode needs to be setup for this work, see: How do I setup QODBC to auto-login into QuickBooks and start it if it's not running?

How to Use Optimizer in Query

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

     Or you also can fully resync your optimized specified table (InvoiceLine as example here) by running:

     sp_optimizefullsync InvoiceLine

and then update it manually at any point of time by doing:

     sp_optimizeupdatesync InvoiceLine

     Or you also can fully resync ALL the QODBC tables by running:

     sp_optimizefullsync All

and then manually update ALL the QODBC tables at any point of time by doing:-

     sp_optimizeupdatesync All

An Unofficial List of Columns/Fields affected by Null/Dirty Reads/Real Time

The following is an unofficial list of columns/fields affected by Null/Dirty Reads/Real Time as of March 27, 2007:

List Tables

Account

- Balance , TotalBalance

Customer

- Balance , TotalBalance , CreditCardInfoCreditCardNumber , CreditCardInfoExpirationMonth , CreditCardInfoExpirationYear , CreditCardInfoNameOnCard , CreditCardInfoCreditCardAddress , CreditCardInfoCreditCardPostalCode

ItemInventory

- QuantityOnHand , TotalValue , AverageCost , QuantityOnOrder , QuantityOnSalesOrder

ItemInventoryAssembly

- QuantityOnHand , TotalValue , AverageCost , QuantityOnOrder , QuantityOnSalesOrder

ItemInventoryAssemblyLine

- QuantityOnHand , TotalValue , AverageCost , QuantityOnOrder , QuantityOnSalesOrder

Vendor

- Balance

Transaction Tables

Bill

- AmountDue , IsPaid , OpenAmount

BillExpenseLine

- AmountDue , IsPaid , OpenAmount

BillItemLine

- AmountDue , IsPaid , OpenAmount

CreditMemo

- CreditRemaining

CreditMemoLine

- CreditRemaining

Invoice

- AppliedAmount , BalanceRemaining , IsPaid

InvoiceLine

- AppliedAmount , BalanceRemaining , IsPaid

PurchaseOrder

- IsFullyReceived , PurchaseOrderLineReceivedQuantity , PurchaseOrderLineIsFullyReceived

PurchaseOrderLine

- IsFullyReceived , PurchaseOrderLineReceivedQuantity , PurchaseOrderLineIsFullyReceived

Performance of Query

   Note: Most users tend to use queries like:

    SELECT * from Customer

which will run slowly as QODBC has to display the correct current balance for each customer, while this will run much faster:

    SELECT "Name","LastName","FirstName","BillAddressAddr1", "BillAddressAddr2","BillAddressCity", "BillAddressState","BillAddressPostalcode"
from Customer

Using Tags in Query to Setup Optimizer

    However can also override your QODBC Optimizer configuration screen settings to suit what you are actually doing by using the following tags:

    VERIFIED | VERIFY - Forces Full Resync with QuickBooks on the optimized table before Query starts
    CALLDIRECT | UNOPTIMIZED - Pass-through query directly to QuickBooks , use no optimizations
    OPTIMIZE | OPTIMIZED | NOSYNC - Pass-through query to optimized table directly for maximum speed

Examples:

    select * from InvoiceLine UNOPTIMIZED
    select * from InvoiceLine NOSYNC
    select * from InvoiceLine VERIFIED

    For example, to read new (recent) InvoiceLines directly out of QuickBooks use:

    select * from InvoiceLine UNOPTIMIZED WHERE Txndate> {d '2006-04-01'}

    Or you can resync your optimized InvoiceLine table by first doing:

    sp_optimizefullsync InvoiceLine

then read directly out of the optimized table by doing:

   select * from InvoiceLine NOSYNC WHERE Txndate> {d '2006-04-01'}

   This is very fast.

   Another good example of using NOSYNC would be to get for example all historical invoice lines before 2003:

   SELECT * from InvoiceLine NOSYNC WHERE Txndate< {d '2004-01-01'}

As invoices before 2004 don't ever change, you can read them directly from the QODBC Optimizer. You just need to use unoptimized for critical operations like Sales Commissions etc.

Optimizer Setup in MS SQL Server Linked Server

Note: "sp_optimizefullsync Customer" doesn't return any rows and cannot be run directly through a SQL Server linked server. A workaround would be, for an Update Sync:

SELECT * FROM OPENQUERY(QBLS, 'SELECT TxnID FROM Customer OPTIMIZE WHERE TxnID = "x"')

And for a Full Sync:

SELECT * FROM OPENQUERY(QBLS, 'SELECT TxnID FROM Customer VERIFY WHERE TxnID = "x"')

Typical Questions About Optimizer

Q: Is there any way to reduce the size of the optimizer file. Perhaps optimizing fewer tables?

A: Not Really. You can delete the .OPT file, then NOT run the "Reload All Data". QODBC will optimize only the tables that are referenced.
And yes, all tables are optimized into the .OPT file.

 

Q: Would turn on or off the multi-table sync make a difference? Yes, it would slow it down but would the data still be synced real time?

A: Multi-table sync applies to the computed QuickBooks fields, such as QtyOnHand, AccountBalance, IsPaid, IsReceived and similar fields. By turning Multi-Table Sync on it will touch more tables loading more table data into the .OPT

 

Q: Anything I can do on the server or network?

A: As far as putting the .OPT file there? Anything else, not really. The only issue I have seen there is a McAfee problem having a general issue with the .QBW file updates.

 

Q: Is it common for an optimized file to become corrupt twice a month? What would cause this issue?

A: I have not heard anyone complain about that happening that frequently, so no. Does Access shutdown or crash during use? That would be the biggest issue I can think of.

 

Q: Is there a way to automatically reload the file each night when employees leave?

A: In the QODBC Driver For QuickBooks folder is a file called QODBCUPD.exe. Running that program does a "Reload All Data". That can be scheduled, but it has to run as the logged in user.

(494 vote(s))
Helpful
Not helpful

Comments (9)
Kayleen
20 September 2012 04:39 PM
Me dull. You smart. That's just what I neeedd.
ROBERT W AYCOCK
01 February 2017 12:48 AM
Q: Can the .OPT file be copied to different users / computers? Can a single .OPT file be used for multiple users with concurrent access?
Jack
01 February 2017 07:30 AM
Hi Robert,

Optimizer file is user-specific & it cannot be shared you cannot copy or move optimizer files from one location to another. There is no way to make it shareable.

So please assign user directory path "%AppData%\QODBC Driver for QuickBooks\Optimizer" which will create Optimizer file user specific.

You need to build optimizer file path for each user separately.

Bill Bach
03 April 2017 09:59 PM
Does anyone know the answer to this question:

If I am running a process that accesses both Invoice and InvoiceLine, do I need to call sp_optimizeupdatesync for EACH table? Or only on one of them because they are related? If only one, does it matter which?
Jack
04 April 2017 07:38 AM
Hi Bill,

You need to optimize parent table only. There is no need to optimize child table separately.

For Example:

When you optimize "Invoice" table, QODBC will automatically optimize the child table "InvoiceLine", i.e Invoice & InvoiceLine will be optimized.

When you optimize "Bill" table, QODBC will automatically optimize the child tables "BillItemLine' & "BillExpenseLine", i.e Bill, BillItemLine & BillExpenseLine will be optimized.
Cheryl McLaughlin
12 December 2017 08:30 PM
A new column is being added to QuickBooks. How do I update the metadata so that it is available for another system. Would I use the steps above to do this?
Jack
13 December 2017 06:52 AM
Hi,

You will get updated data on executing the query.

There is no need to rebuild Optimizer file daily.

You may need to rebuild Optimizer file if Optimizer file got corrupted.

You can refer below mentioned link for verifying that Optimizer file corrupted or not:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2636/44/troubleshooting---how-to-verify-if-optimizer-file-corrupted-or-not

If Optimizer file is corrupted, then You need to reset your QODBC optimizer file using sp_optimizefullsync ALL command.

If you are still the facing issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
AK
13 May 2018 02:38 AM
How do I change Optimizer Database Folder form default %AppData%\QODBC Driver for QuickBooks\Optimizer to something else for all users? I've tried QODBC Driver Setup app - no go, registry settings - the app still defaults to %AppData%\QODBC Driver for QuickBooks\Optimizer directory. Please advise.
Rajendra Dewani
24 May 2018 07:44 AM
Start>>All Programs>> QODBC Driver for use with QuickBooks>> Configure QODBC Data Source>>Switch to "System DSN" Tab >> select DSN "QuickBooks Data">> click "configure" . Switch to Optimizer tab, change the optimizer folder & apply the changes.

If you are using MS Access, you will have to remove the linked tables and re-add the linked tables to refresh the Optimizer file path changes.
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).