• 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

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 * from InvoiceLine UNOPTIMIZED WHERE Txndate >= {d '2003-01-01'} and Txndate < {d '2004-01-01'}

select * from InvoiceLine UNOPTIMIZED WHERE Txndate >= {d '2004-01-01'} and Txndate < {d '2005-01-01'}

select * from InvoiceLine UNOPTIMIZED WHERE Txndate >= {d '2005-01-01'} and Txndate < {d '2006-01-01'}

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

See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for all the details. 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 this:

select * from InvoiceLine WHERE Txndate >= {d '2003-01-01'} and Txndate < {d '2004-01-01'}

select * from InvoiceLine WHERE Txndate >= {d '2004-01-01'} and Txndate < {d '2005-01-01'}

select * from InvoiceLine WHERE Txndate >= {d '2005-01-01'} and Txndate < {d '2006-01-01'}

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

you will also update your optimized table. 

Pictures can be inserted/uploaded by using the Insert/Edit Image button while editing your forum post:

But logs and pictures won't help here. Please turn off the QODBC status panel, and make sure that Detail Tracing is off in the QODBC Setup screen and close the date range of the queries to even month ranges!

select * from InvoiceLine UNOPTIMIZED WHERE Txndate >= {d '2006-06-01'} and Txndate < {d '2006-07-01'}

I would like to run the optimized set because of the time savings.  Are you suggesting to turn off the optimizer just for testing purposes or for production?  Also,  extracting data for a whole year worked fine until this "hiccup".  It seems that the following statement has been true in the last two weeks of testing:  If  an application accessing QODBC has a problem during execution, QODBC does not handle that well.  Upon re-accessing the same query (after shut-down and re-boot) QODBC hangs as decribed in my earlier post.  I have found that the the only way to "clear" everything is to uninstall QODBC, delete all of the DSNs and Query Optimizer files, then re-install QODBC.  None of the other things I tried would fix the QODBC data access problems once it hung that first time.  Any thoughts on this?

I had already tried RESETTING the Optimizer per those instructions in previous instances without success.  I went ahead and tried it again last night at your suggestion and here are the results.

 System is a 3.4GHz P4 with 2GB RAM running as the QB File Host: (All DSNs, queries and optimizations referenced in these posts are being run directly on this box).  Physical Memory Usage has never exceeded 1.3GB.

• Went into the Optimizer options in the ODBC Driver for Quickbooks Setup screen.
• De-Selected "Use Optimizer"
• Appplied Changes and Exited
• Deleted the Optimizer Files in the folder as instructed.
• Opened the ODBC Driver setup screen again
• Selected "Use Optimizer"
• Selected a new, never-used directory for  the Optimizer Database Folder.
• Ran "qodbcful.exe DSN Name" from the C:\Program Files\Intuit\QuickBooks Enterprise Solutions 6.0\Components\QODBC directory at the command prompt.
• Started the re-build at 11:00PM last night.  No other users on system.
• As of 6:30am this morning the process is still running.
• The .OPT file is 78,214 KB so far
• The .OPT-journal file is 5KB
• There is one message in the QODBCLOG.TXT and it reads:
• 2006-06-08 02:22:02 QODBC Ver:  6.00.00.155 *********************************************************************************************************************
  IsAService: True
  SQLOptimize_OpenOptimizeDBHandle prepare:
  SELECT "type", "tbl_name" FROM "SQLite_MASTER"
  database is locked

When the users come in this morning I will most likely have to abort the re-build because of system performance degradation.

QUESTIONS:

• How long should a full re-build take? (The QB Data file is approximately 300MB in size)
• If an Invoice record (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? I know that this question was asked before but I need clarification.  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?

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 InvoiceLine table by running:-

sp_optimizefullsync InvoiceLine

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).

Sorry, 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

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 InvoiceLine

I think you're missing the point, 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 in a table anymore. Imagine if QuickBooks did that?, it would be very slow.

We came to this realization some time ago. That's why we put a SQLite engine behind QODBC as it's optimizer in v6. In the meantime, Intuit did change to a Sybase back end for QuickBooks 2006 for USA users only, but they didn't change their data layout to take advantage of using the Sybase backend.

You will find that using our backend like this with NOSYNC will give you fast results:-

SELECT InvoiceLine.TxnID, InvoiceLine.TimeModified, InvoiceLine.TxnNumber, InvoiceLine.CustomerRefListID, InvoiceLine.CustomerRefFullName, InvoiceLine.ClassRefListID, InvoiceLine.ClassRefFullName, InvoiceLine.ARAccountRefListID, InvoiceLine.ARAccountRefFullName, InvoiceLine.TemplateRefListID, InvoiceLine.TemplateRefFullName, InvoiceLine.TxnDate, InvoiceLine.RefNumber, InvoiceLine.BillAddressAddr1, InvoiceLine.BillAddressAddr2, InvoiceLine.BillAddressAddr3, InvoiceLine.BillAddressAddr4, InvoiceLine.BillAddressCity, InvoiceLine.BillAddressState, InvoiceLine.BillAddressPostalCode, InvoiceLine.BillAddressCountry, InvoiceLine.ShipAddressAddr1, InvoiceLine.ShipAddressAddr2, InvoiceLine.ShipAddressAddr3, InvoiceLine.ShipAddressAddr4, InvoiceLine.ShipAddressCity, InvoiceLine.ShipAddressState, InvoiceLine.ShipAddressPostalCode, InvoiceLine.ShipAddressCountry, InvoiceLine.IsPending, InvoiceLine.IsFinanceCharge, InvoiceLine.PONumber, InvoiceLine.TermsRefListID, InvoiceLine.TermsRefFullName, InvoiceLine.DueDate, InvoiceLine.SalesRepRefListID, InvoiceLine.SalesRepRefFullName, InvoiceLine.FOB, InvoiceLine.ShipDate, InvoiceLine.ShipMethodRefListID, InvoiceLine.ShipMethodRefFullName, InvoiceLine.Subtotal, InvoiceLine.ItemSalesTaxRefListID, InvoiceLine.ItemSalesTaxRefFullName, InvoiceLine.SalesTaxPercentage, InvoiceLine.SalesTaxTotal, InvoiceLine.AppliedAmount, InvoiceLine.Memo, InvoiceLine.IsPaid, InvoiceLine.CustomerMsgRefListID, InvoiceLine.CustomerMsgRefFullName, InvoiceLine.IsToBePrinted, InvoiceLine.CustomerSalesTaxCodeRefListID, InvoiceLine.CustomerSalesTaxCodeRefFullName, InvoiceLine.SuggestedDiscountAmount, InvoiceLine.SuggestedDiscountDate, InvoiceLine.InvoiceLineType, InvoiceLine.InvoiceLineSeqNo, InvoiceLine.InvoiceLineGroupTxnLineID, InvoiceLine.InvoiceLineGroupItemGroupRefListID, InvoiceLine.InvoiceLineGroupItemGroupRefFullName, InvoiceLine.InvoiceLineGroupDesc, InvoiceLine.InvoiceLineGroupQuantity, InvoiceLine.InvoiceLineGroupIsPrintItemsInGroup, InvoiceLine.InvoiceLineGroupTotalAmount, InvoiceLine.InvoiceLineGroupSeqNo, InvoiceLine.InvoiceLineTxnLineID, InvoiceLine.InvoiceLineItemRefListID, InvoiceLine.InvoiceLineItemRefFullName, InvoiceLine.InvoiceLineDesc, InvoiceLine.InvoiceLineQuantity, InvoiceLine.InvoiceLineRate, InvoiceLine.InvoiceLineRatePercent, InvoiceLine.InvoiceLinePriceLevelRefListID, InvoiceLine.InvoiceLinePriceLevelRefFullName, InvoiceLine.InvoiceLineClassRefListID, InvoiceLine.InvoiceLineClassRefFullName, InvoiceLine.InvoiceLineAmount, InvoiceLine.InvoiceLineServiceDate, InvoiceLine.InvoiceLineSalesTaxCodeRefListID, InvoiceLine.InvoiceLineSalesTaxCodeRefFullName, InvoiceLine.InvoiceLineOverrideItemAccountRefListID, InvoiceLine.InvoiceLineOverrideItemAccountRefFullName from InvoiceLine NOSYNC WHERE Txndate >= {d '2005-01-01'} and Txndate < {d '2006-01-01'}

Well I was very relieved to read your post and at least know that there was someone else experiencing the same issues with QODBC driver that I am seeing as I attempt to pull only 1,081 records from the Invoice table.

I tried DTS from SQL Server 2003, I tried to do this under the SQL Server 2005 flavor of DTS, and I tried using the Import wizard to my local database and to the production servers. I set and un-set every possible check box and radio button for the QODBC data source possible. I know the issue isn't the ODBC connection as I can see my data using the VB Demo App that came with the Driver Install. Even that takes eight minutes to return a result set from

select * from Invoice where BalanceRemaining <> 0

There were 1081 rows in this result set.

I have set this query going through DTS overnight both on SQL Server 2003 and SQL Server 2005 and it's still not done when I return the following morning.

ODBC drivers shouldn't be this complicated or slow. I've used DTS before to get to legacy data in the realm of 12 million Invoice records and I have never seen anything as inefficient as this. I am just gald that I decided to evaluate it properly before making the mistake of buying it.