I've got a batch process setup to run some VB scripts that essentially execute the following each night (morning?) at around 1:15 am:

     sp_optimizeupdatesync InvoiceLine

     sp_optimizeupdatesync SalesReceiptLine

Log files show the process took 1 min 38 sec last night (1:15 am this morning 5/4/2006).  Since my manually running this process tends to always take considerably longer (anywhere from 20 minutes to an hour or more, even if I just ran it), I am suspisious that the process did not truly run, for whatever reason.

Further, in looking in the ..\QODBC\Optimizer folder, I see the *.OPT file has a date/time stamp of 5/2/2006 @ 5:14 pm.  This further leads me to believe the sp_optimizeupdatesync commands did not run.  Shouldn't the *.OPT file's date/time stamp be updated as of 1:15am this morning (5/4/2006), since there definitely WERE Sales Receipts entered in QB yesterday (5/3/2006)?

Note however, that running a select with the following FROM and WHERE components DOES list out the 5/3/2006 SalesReceiptLine records. 

   FROM SalesReceiptLine NOSYNC WHERE TxnDateMacro = 'Yesterday'

In other words, all looks like it worked just fine, from the record selection perspective. Doesn't the NOSYNC mean to get the records from the *.OPT file?  How did all this work, if the *.OPT file hasn't been updated since 5/2/2006?

Just trying to fully understand how QODBC and the Optimizer and the NOSYNC options all work.

TIA!

I think you may be confused with sp_optimizefullsync which will take a lot longer as it reloads the optimized table from scratch.

sp_optimizeupdatesync InvoiceLine

will run very fast as it just needs to add the new invoicelines to the optimized table since the last sync. By default the QODBC optimizer will automatically update new and changed entries in a table from QuickBooks first and then execute any query against the local optimized table, so if you queried the InvoiceLine table yesterday, the optimized table must probably was already up to date.

There's no real need to run the sp_optimizeupdatesync operation each night.

Confused is an understatement.  I've been dealing with computers and IT-related issues for 23+ years, much of that time spent in DBA type roles, and I don't know that I've EVER had such a difficult time understanding the concepts behind a product.  I've read everything about QODBC and the optimize that I can get my hands on, and it still doesn't seem to behave like I would understand it to behave.

In reality, I'm actually NOT as confused between sp_optimizefullsync and sp_optimizeupdatesync as it may appear.  Everything I said in my original posting was accurate. I previously explained to you (in different postings) how BOTH of these took a long time, even if I had JUST RAN either or both of them.  This client has a 147 MB QB data file.  You then explained to me how the optimize was really designed for something different, etc.

At any rate, since I'm doing my selects with the NOSYNC option, and considering your statement that "There's no real need to run the sp_optimizeupdatesync operation each night.", just WHEN would I run either of these sp_optimize options?  How/when does the OPT file truly get updated?  You say " and then execute any query against the local optimized table".  Shouldn't the date/time stamp on the OPT file show as having been updated?  Even right now it's still showing a date/time stamp of 5/2/2006 at 5:14 PM.  Your reply doesn't seem to clarify how/when the OPT file is updated?

How is it that running a select with NOSYNC today (5/5/2006), will return records dated 5/4/2006 from an OPT file that was last updated on 5/2/2006?

Again, if I don't run sp_optimizeupdatesync nightly, then WHEN would I want to run it?

Clarification would be much appreciated.  Thanks!

You say "other than one with the NOSYNC tag"...

As I stated, I AM using the NOSYNC tag (and this is the ONLY QODBC query of this table).  In such a situation, where ALL selects use the NOSYNC tag, is running sp_optimizeupdatesync still not necessary?

Also, you say "...when the QODBC Optimizer runs in default mode", what if it's NOT in default mode?  What determine or stipulates when running sp_optimizeupdatesync is necessary?

Also, if QODBC Optimizer IS in default mode, and the ONLY QODBC select statements being run all use the NOSYNC tag, THEN is the "update of the optimizied table" STILL AUTOMATIC?