|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| Optimizer - Range of Optimization |
| Author |
Message |
|
|
| Posted : 2008-08-25 07:37:28 |
Hi Flexqtrs,
I have a general question on how the Optimizer works. We've had the optimizer fail every 2-3weeks for unknown reasons. When I say fail, I mean it does not complete the query then begins a Reload All Data which takes many many hours(typically an entire night to reload).
We have been trying to pin point the problem but we can't tell if its QB or the QODBC driver. Both QB and the driver is on the same machine and we keep QB open trying to keep performance up.
My question regarding the optimizer is how far back does it optimize? We really only need the most current and up to a weeks' worth of information to be optimized and for us to access. I was hoping you could tell me what date range or other variable the Optimizer uses to decide what optimize. Is it since the last end of fiscal year?
We would love a feature in the next QODBC driver to have some way to set how far the optimization goes, by date would be best. This would help us as if a Reload All Data is required we wouldnt have to wait for a full day to optimize all the data, just up to the date we need. If there is a way to set this with 8.0 please let me know. It is vital to our daily usage of QB.
Currently our Optimizer is set as recommended for performance (start of every conn. load updated, dirty reads, opt after every insert/update)
QB 08 Enterprise, Win XP.
|
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-08-25 14:44:11 |
The QODBC optimizer is single user only. The most likely reason for an optimization update to fail is:
a) More than one user is using the optimized file location
b) While optimizing a table QuickBooks said a customer, vendor, employee was in single user exclusive mode.
c) A Enterprise Multi-Sync operation detected deleted transactions or payments and then rebuilds the table instead.
b) is caused by the QuickBooks User Interface being used at the time while c) because Intuit says it must do that.
I never "Reload All Data". If one of my tables isn't right I simply use the VERIFY tag to rebuild it like this:
Select * from Customer VERIFY where Name='Tom'
As far as optimization is concerned, the whole point is to optimize all the old transactions from years ago so we don't have to ask QuickBooks for the information ever again.
Last but not least, for QODBC to work correctly you must first check that you are running QODBC v8.0.0.242 or higher (see: How can I get the latest version of QODBC? ) and then change your optimization settings to use the The start of every new connection (with "Load Updated Data" first) option and confirm that the Multi-Table Sync option has been disabled. If not, uncheck the Multi-Table Sync option as this is often defaulted on (checked) with QuickBooks Enterprise and the QODBC Enterprise Desktop Edition.
See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more information. |
|
|
|
|
|
| Posted : 2008-08-26 03:03:54 |
| Hi Tom,
Thanks for the quick reply. We have tried to remove variables and try to pin point how and when the QODBC driver goes into Reload All Data mode. We do have Verbose dialog settings on so we can see if it is hanging. We also have the settings you recommended for the Optimizer already set.
Please see questions in line:
The QODBC optimizer is single user only. - We understand this, we only have it installed on a single machine that has the Qb install. Access is only done on that machine.
The most likely reason for an optimization update to fail is:
a) More than one user is using the optimized file location - No Problem on this, only used by one application by one person on one machine.
b) While optimizing a table QuickBooks said a customer, vendor, employee was in single user exclusive mode. - OK on this, we are always in Multi-user mode when we make the ODBC calls.
c) A Enterprise Multi-Sync operation detected deleted transactions or payments and then rebuilds the table instead. - Definitely Possible, is this different from the QODBC Optimizer settings for Multi-table Sync (we already have Multi-table Sync Disabled in Options)? If this is a QB thing, how can we be on top of this so we can control when this happens and Reload data at a certain time?
b) is caused by the QuickBooks User Interface being used at the time while c) because Intuit says it must do that.
- not completely sure what that means but our QB application is left open but minimized and not used while we are making the odbc calls. We attended a flexqtrs class and they said this is a better method than to use non-attended mode.
I never "Reload All Data". If one of my tables isn't right I simply use the VERIFY tag to rebuild it like this:
Select * from Customer VERIFY where Name='Tom'
As far as optimization is concerned, the whole point is to optimize all the old transactions from years ago so we don't have to ask QuickBooks for the information ever again.
- We dont want to Reload All Data either but watching the verbose dialogs, if it doesnt get something back from QB for a while, the QODBC driver starts to Reload All Data on its own. Then we have to wait forever for it to do so.
As I mentioned, we use the QODBC driver a little differently and typically only need the last 10 days of data. With the Optimizer off it is still very slow. Your optimizer is amazing but is there any way to request a feature for future versions to add a date field for optimizer range?
Last question,How far back QODBC Optimizer optimizes or it goes for everything, even closed data from last year?
Thanks Tom, your input is much appreciated.
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-08-26 07:56:30 |
The QODBC optimizer creates a mirrored SQLite table of the whole table. The only options are to control when the auto-update routines run. If you have "verbose" on it will slow QODBC down and consume to many resources and stuff everything up. I've been running QODBC for over five years and never use verbose.
Applications like MS Access also store all the connection settings when QODBC is first used, so even when you change them later, the connection settings turn them back on. Get "Verbose" and "Trace" off and let the product run!!! Rebuild one table and then use NOSYNC for fast queries.
See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for how tags like NOSYNC work. |
|
|
|
|
|
| Posted : 2008-09-09 09:53:59 |
Hi Tom,
The Optimizer table died again. I tried it with your suggestions and still no luck. Could you give me more info on the occurance of:
c) A Enterprise Multi-Sync operation detected deleted transactions or payments and then rebuilds the table instead.
Does this mean if any QB User deletes a tranaction or payment, the Optimizer will rebuild the table? Just want to clarify so we can try to manage this.
If we cant get this figured out what options do we have to make this more reliable? Could this be caused by the application making the query?
Regards |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-09 20:39:38 |
| If the application that uses QODBC crashes during a optimizer update, then the opt file can also be corrupted. The best thing to do in your case would be to backup the opt file each day so you can restore yesterday's opt file when needed. |
|
|
|
|
|
| Posted : 2008-09-10 04:51:27 |
Hi Tom,
That's a good idea, just to clarify so if we just make a copy OPT file , replace the corrupt one when it hangs, and do a "Load Updated Data" the Optimizer will be able to pick up where it left off?
Also could you answer our previous question that if any QB User deletes a transaction or payment, the Optimizer will rebuild the table? If this is the cause of the Optimizer Reloading all Invoice Data then the backup method might not help since it'll detect the same changes and will still want to Reload.
The app accessing QODBC doesn't crash, it's just sitting there waiting for the driver which is "waiting for response from quickbooks".
Thanks again for the help. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-10 21:59:18 |
If a update operation fails to complete then QODBC will detect an invalid time stamp and the optimized table will be reloaded, and yes you can copy over a corrupted opt file and QODBC will update itself automatically. You don't need to use "Load Updated Data".
Please read: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more information how the optimizer works. |
|
|
|
|
|
| Posted : 2008-09-11 03:30:55 |
Hi Tom,
Not trying to be a bother but you said the following in your original reply regarding reasons why the Optimizer may have to reload:
The most likely reason for an optimization update to fail is:
a) More than one user is using the optimized file location
b) While optimizing a table QuickBooks said a customer, vendor, employee was in single user exclusive mode.
c) A Enterprise Multi-Sync operation detected deleted transactions or payments and then rebuilds the table instead.
b) is caused by the QuickBooks User Interface being used at the time while c) because Intuit says it must do that.
Can you please clarify on (c)?
Does this mean if a QB User deletes a transaction, say an invoice, then the Optimizer will Reload the entire Invoice Table?
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-11 22:51:00 |
| Currently the only way to remove deleted entries from a optimized table is to rebuild it. |
|
|
|
|