Qodbc Driver Slow
Problem Description 1
I have tried several ways unsuccessfully to get a complete data dump of the InvoiceLine data from QB Ent. 6 Mfg. Ed. There are over 100K records in that data set. I had the client upgrade to QB Ent. 2006 from QB 2005 Pro just for this reason. I have tried the following methods for getting this data set:
SQL Server DTS copy to a SQL Server Table; MS Access Linked Table with a customized query;
Crystal Reports using a Command;
Crystal Reports using a Table;
VB Demo.
I have tried both optimized and unoptimized queries. They are all failing at one point or another. I did get SQL Server DTS to do one complete dump which took over 6 hours. Since then I have not even been able to get that to work again.
Since then I have Cleaned up the company data, made a portable file then re-opened it to reduce the file size and performed a re-build on the data several times. I have also re-installed QODBC a few times and also changed the optimizer directories in order to reset the optimizer which reports Data Load Successful when in fact it hasn t done anyting.
When running an unoptimized query, the driver status panel will appear and seems to get stuck at Open Table for an extended period of time or sometimes indefinitely. WHAT AM I DOING WRONG.
Solutions 1 - Narrow Date Range
When QODBC calls QuickBooks, QuickBooks is trying to return a HUGE XML document of 100,000 records here and you are clearly using up all the system resources on your computer.
I suggust you extract all the information year by year you require by running year by year subset queries instead like this:
select fromInvoiceLine UNOPTIMIZED WHERE Txndate d 2009-01-01 and Txndate
Optimizer Related Information
See: How to setup QODBC Optimizer and where are the Optimizer options for all the details about optimizer.
By default the optimizer will update new and changed entries in a table from QuickBooks first and then execute the query against the local optimized table. This is faster than reading everything out of QuickBooks every time, especially the more data you have.
If you extract all the information year by year you require by running year by year subset queries instead like above, you will also update your optimized table.
Solutions 1 - Reset Optimizer File
Sometimes, the queries which could work fine before crashes or become slow suddently, it always indicates that your optimizer file is corrupted. Once you are corrupting your. opt file, you don t have to reinstall QODBC, see: How to switch OFF or RESET the QODBC Optimizer for more.
Reset and Reload Information
Questions:
1. How long should a full re-build take.
2. The QB Data file is approximately 300MB in size
If an Invoicerecord for example is added or changed, will the optimizer kick in to update ONLY THE NEW new/changed records to the optimized data set or does it need to completely re-optimize all of the records in the tables affected by the addition/change of the record. The actual physical activity and time frames that I have experienced do not seem to match with the update changed records only description that I received in an earlier post.
Your advice in these issues has continued to be to run UNOPTIMIZED queries. Is there a reason for this.
Can I expect to have problems when running optimized queries while users are actively using the system.
Answers:
1. With large company files, we don t recommend a full load of all the data at once. In fact we recommend optimizing the tables you require on a table by table basis only. For example, for the InvoiceLine table you can fully resync your optimized InvoiceLinetable by running:
sp_optimizefullsync InvoiceLine
2. By default the QODBC optimizer will update new and changed entries in a table from QuickBooks first and then execute the query against the local optimized table. This is faster than reading everything out of QuickBooks every time, especially the more data you have. Please keep in mind that the QODBC Optimizer. opt file is local to your computer single user only and it can t be shared with other users.
I had used the UNOPTIMIZED tags to call QuickBooks directly because the basic rule is to always read historical information out of the. opt file NOSYNC and to read very new data directly out of QuickBooks UNOPTIMIZED.
Problem Description 2
I am resigned to the fact that the Invoiceline Table takes up to 5 hours to completely optimize on this system because it is so large. What I don t understand is why the optimizer insists on Optimizing the ReceivePaymentLine table when I try to access the optimized InvoiceLine Database. There are no references in my query to that table AT ALL. My query is simply SELECT FROM INVOICELINE NOSYNC. Why would it try to optimize that table when there is no reference to it at all. Does this mean that the OPT file is corrupt. If so then there must be an issue or bug with large datasets over 100K records . Again, I am stumped.
Solutions 2 - sp_optimizefullsync and sp_optimizeupdatesync
ReceivePaymentLines with Optimizer Related Information
We look at the ReceivePaymentLines internally to check if there s any payments that need to be updated against InvoiceLines stored in the optimized table.
Because you did SELECT FROM INVOICELINE NOSYNC, the SELECT forces a ReceivePaymentLine lookup to make sure open balances are correct and because ReceivePaymentLine hasn t been optimized it started it I guess even with a NOSYNC.
So I suggest you optimize your ReceivePaymentLine table too:
sp_optimizefullsync ReceivePaymentLine
Optimizer Update Information
Once your InvoiceLine and ReceivePaymentLine tables have been optimized you just need to execute a query on InvoiceLine to update it or you can update it manually at any point of time by doing:
sp_optimizeupdatesync ReceivePaymentLine
sp_optimizeupdatesync InvoiceLine
Note: Once your InvoiceLine, SalesOrderLine and ReceivePaymentLine tables have been optimized, QODBC by default will just update the. opt file when you do a query on the table. So you only have to use sp_optimizeupdatesync if it s part of a batch reporting or updating process or something. You don t need to run it every night to use QODBC.
What you now need to do is NOT run full Select queries for every column and every row ina table anymore. Imagine if QuickBooks did that., it would be very slow.
Solutions 2 - Interator or Chunk Size
Sometimes, because of the large record sets being used, the Iterator or Chunk Size in the Advanced Tab in the QODBC Setup Screenshould be setup up to accomodate the largest returned record set. The default is 500,and it can be set to any value up to 100,000.
David Ringstrom explains how to use the CSV format when exporting QuickBooks reports to Excel to create workbooks that automatically update themselves.
1 取消对话框最大 最小按钮. include Qt QPixmap splImg images//Splash.png ;.
QODBC Slow or Fails on Large InvoiceLine DataSet - Powered by Kayako Help Desk Software
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 Qptimizer 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 explaination 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 UserProfile 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 local 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 opitmized data while you are for example running queires or importing or exporting data. The optimized data is updated upon a new connection and leftunupdated untilthe nextnew connection occurs again.
The start of every new connection with Reload All Data first is used if you need to rebuild opitmized data from scratch before running important queires like Sales Commisions or importing or exporting data. The optimized data is rebuilt upon a new connection and left unupdated 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 option 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 succesfully 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 USA. Prior to SDK v3 the other multi-sync tables did not exists 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 wit
h 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
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
, CreditCardInfoCreditCardNumber
, CreditCardInfoExpirationMonth
, CreditCardInfoExpirationYear
, CreditCardInfoNameOnCard,
CreditCardInfoCreditCardAddress,
CreditCardInfoCreditCardPostalCode
ItemInventory
- QuantityOnHand,
TotalValue
, AverageCost
, QuantityOnOrder
, QuantityOnSalesOrder
ItemInventoryAssembly
- QuantityOnHand
, TotalValue
ItemInventoryAssemblyLine
Vendor
Transaction Tables
Bill
- AmountDue
, IsPaid
, OpenAmount
BillExpenseLine
, OpenAmount
BillItemLine
CreditMemo
- CreditRemaining
CreditMemoLine
Invoice
- AppliedAmount
, BalanceRemaining
, IsPaid
InvoiceLine
PurchaseOrder
- IsFullyReceived
, PurchaseOrderLineReceivedQuantity
, PurchaseOrderLineIsFullyReceived
PurchaseOrderLine
, 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 - Passthru query directly to QuickBooks, use no optimizations
OPTIMIZE OPTIMIZED NOSYNC - Passthru query to optimized table directly for maximum speed
Examples:
select fromInvoiceLine UNOPTIMIZED
select from InvoiceLine NOSYNC
select from InvoiceLine VERIFIED
For example, to read new recent InvoiceLines directly out of QuickBooks use:
select fromInvoiceLine 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 invoicelines before 2003:
SELECT from InvoiceLine NOSYNC WHERE Txndate
as invocies before 2004 don t ever change, you can read them directly from the QODBC Optimizer. You just need to use unoptimized for crtical operations like Sales Commisions 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 a Update Sync:
SELECT FROM OPENQUERY QBLS, SELECT TxnID FROMCustomer OPTIMIZE WHERE TxnID x
And for a Full Sync:
SELECT FROM OPENQUERY QBLS, SELECT TxnID FROMCustomer VERIFY WHERE TxnID x
Typical Questions About Optimizer
Q: Is there anyway 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 turning 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.
Intuit released QuickBooks 2012 R14 just a few weeks ago, and many but not all users found that once they updated the could not open their QuickBooks company file.
Installing QODBC Driver for QuickBooks. Step 1 - Download QODBC If you haven t already downloaded QODBC by modem, do it now by going to the download page.
Just finished creating what I consider a simple report for a customer who uses QuickBooks. He was spending at least 5 hours a month manually generating.
