Tom,

Our QBE data file is 125MB and growing at a pretty good clip. We add about 25 or 30 customers each month and we produce around 200 invoices each month (we are a service company that bills T&M) with an average of maybe 8 lines per invoice so our InvoiceLine table is growing rapidly (not to mention the TimeTracking table). When we view invoices 'in-house' I include any unbilled time and I access the Invoice table to show any unpaid invoices. One of the biggest problems is when any of the tables has to go through an optimization (particularly the InvoiceLine of TimeTracking tables) ... it requires a lunch break! I realize that Jump-in's will help my performance but I'd like your advice on the following scenario for achieving maximum performance:

Run QODBCUPD in an automated script every night and set the optimizer to 'The end of yesterday'

When accessing data I will use the NOSYNC tag which will return data through the last QODBCUPD (i.e. the previous evening) but I will also include an option (that people can select) to get today's data which will just use the same query but add the tag 'UNOPTIMIZED Where TxnDate={d '2007-02-08'}' (obviously it would be today's date). That way I'd be assured of getting the latest and greatest data if need be.

A couple of questions: 1) Does this scenario make sense?; 2) If I run the QODBCUPD each night should I change the optimizer to 'The end of yesterday'; 3) What is the difference between QODBCUPD and QODBCFUL

And finally, as a side question, can you comment on the revision to the Optimizer that you have in the works?

I would greatly appreciate your insight before I start down this 'optimized' road. Thanks as always Tom!

I have recommended a new optimizer option called,  "Keep my optimizer data up to date as of: The start of every new connection with a reload of specified table data first". This will mean that when you connect, QODBC will automatically run a sp_optimizeupdatesync internal operation for each table when it's first used within the connection and run all queries in a forced NOSYNC (optimized) mode that actually does a sync update first!

This will mean that the data will always be correct when you first start your queries, but you will not need to wait when you re-run queries or do drill downs on the information. We are also looking at doing optimizer data updates after an Insert or Update (will not be available if optimizer currency is set to "The start of every query).

This will be the best option for large company files. If while your connection is open you need to see new invoices you would simply close the connection and reopen it to see new data or force a update by doing:

sp_optimizeupdatesync InvoiceLine

When using "The end of yesterday" optimizer option the assumption is you are only interested on seeing data that has been entered at the close of business yesterday. But that's not what you are doing here, each time you run a UNOPTIMED query, the time it took to read the info out of QuickBooks is wasted and would have been better used updating the optimized table instead. So instead of running the same query but adding the tag 'UNOPTIMIZED Where TxnDate={d '2007-02-08'}' (obviously it would be today's date), perform a sp_optimizeupdatesynce operation instead, and run the query with a NOSYNC tag.

Tom,

I'm now running a QODBCFUL every night. I'm still having a problem though. I include AppliedAmount as a field on my invoice. I just applied a payment to an invoice and it did not show up when I queried with the NOSYNC tag. I then ran a 'sp_optimizeupdatesync Invoice' and re-ran the query but the payment still doesn't show up with the NOSYNC tag. If I take out the NOSYNC tag then it gets the payment. I thought the updatesync would allow me to use the NOSYNC tag and still give me the latest data. I even tried the VERIFIED tag and it caused a fullsync of the Invoice table (all 2900+ lines ... very slow). What exactly does the updatesync do? What does the VERIFIED tag do (I thought it was similar to the updatesync)?

Should I just run a QODBCFUL each night and then omit the NOSYNC tag (i.e. use the optimizer without tags) for best performance with latest data?

Thanks.

You should only be running QODBCUPD every night. As far as running 'sp_optimizeupdatesync Invoice', that only updates new invoices. The tables that need to updated (in reverse order) when a payment is made are:

• Invoice - Main Invoice Header Table
• InvoiceLine - Provides line item detail for a customer invoice; contains all the information included in the Main Invoice Table
• InvoiceLinkedTxn - Payments, Credit Memos, Deposit Line Items; does NOT include reimbursements, transfers from SO or Estimates
• ReceivePayment - Main Receive Payments Header Table
• ReceivePaymentLine - Provides line item detail for a payment; contains all the information included in the Main Receive Payments table

Both AppliedAmount and BalanceRemaining are Forced Unoptimized columns in the Invoice table, meaning that they need to be updated by reading other tables. Because your optimizer setting is 'The end of yesterday', those other tables will not show any payments made today until tomorrow?

Tom, as always you have shed new light on the matter. Just to clarify, I was only thinking about changing the optimizer setting to 'end of yesterday'. After your previous response I left it at 'start of every query'. Is there a way I would/should know that AppliedAmount and BalanceRemaining were forced optimizations (so I don't have to bug you for an answer)? I will try QODBCUPD every night and include the InvoiceLinkedTxn, ReceivePayment and ReceivePaymentLine tables as part of my sp_optimizeupdatesync routine to get data refreshed to the latest and greatest during my invoice program.

One more clarification. I ran a QODBCUPD and then immediately ran another QODBCUPD. I would have thought that all the updates would have been pretty quick since nothing had happened between the updates. When the update got to EsimateLinkedTxn it took quite a long time (8+ minutes to retrieve ~2900 records) and I can see that it is actually accessing the InvoiceLinkedTxn table (by using the Driver Status message box). It did the exact same thing when it got to SalesOrderLinkedTxn (full update accessing the InvoiceLinkedTxn table). Why did it do a FULL update on the InvoiceLinkedTxn table? Do I also need to be cognizant of the order in which I update the tables? Below you touched on an order to my sp_optimizeupdatesync of the appropriate tables. Evertime you answer a question a new one pops up. Thanks as always for your speedy responses!

Sorry about the small font (I copy my posts to Word first in case the site drops my post ... which it did again).

UPDATE ... I ran a QODBCUPD and then ran my queries without the NOSYNC tag as follows"

It ran without a problem and showed a recent payment. I then ran it with the NOSYNC tag and the received payment didn't show up. I then ran a sp_optimizeupdatesync on ReceivedPaymentLine, ReceivePayment, InvoiceLinkedTxn, InvoiceLine, Invoice (in that order) and the query still did not show the payment with the NOSYNC tag. Obviously I can get the proper results if I just leave out the NOSYNC tag but I'm trying to get optimal performance.

What am I missing?

When QODBC optimizer operates, all the LinkedTxn tables have to be read in real time out of QuickBooks and can't be optimized. The QODBC optimizer does a not more operations than just refreshing tables. In the case of Linked Transactions, we read the modified transactions and then modify the optimized data in the corresponding matching optimized tables.

sp_optimizeupdatesync only resyncs new data in the tables, and while it will check for deleted transactions, it doesn't do the resync on all LinkedTxn operations yet.

But, now that you back to using "start of every query", you can go back to your original method on using NOSYNC, but instead of using UNOPTIMIZED, run those queries normally without any optimizer tags and the optimizer will do the rest!