Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

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

Forum : When to optimize, when to use nosync?Search Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v9 Forum

 New Topic 
 
 Post Reply 
[1]  
 When to optimize, when to use nosync? 
 Author   Message 
  PH 
  
 Group: Members 
 Posts: 41 
 Joined: 2007-02-02 
 Profile
 Posted : 2008-11-06 03:27:51

I am using Qbook 2007.

In another post, you showed me how to use NOSYNC in a query to improve its speed.  Can you explain what this does and if there are any trade-offs?  Also, when is it appropriate to use NOSYNC and when not?

I have seen how to build the optimize tables with the SP.  Is that something you should always do when first setting up? 

Is the optimized table per inidividual user or shared?

Thank you.

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-11-06 08:13:46

The QODBC optimizer is single user only and the companyfilename.opt file can't be shared. There's no hard rule on how to use the QODBC optimizer, but to dump/export whole tables I use UNOPTIMIZED, for table joins I always use NOSYNC.

In programming one would general force a optimizer update on the tables you want to use and then just run all the queries with a NOSYNC tag. If I'm inserting a batch of transactions, I run my inserts and then force a optimzier update.

The recommended settings in: How do I setup the QODBC Optimizer? Where are the Optimizer options? will do this automatically, however users often want to optimize their operations instead.

 

  Top 
  PH 
  
 Group: Members 
 Posts: 41 
 Joined: 2007-02-02 
 Profile
 Posted : 2008-11-06 08:57:11

Thanks,   I read the link you sent pretty carefully.

Follow-up questions I didn't see answered in the reference you gave:  If you set QODBC to optimize as of yesterday, for example, and you do ordinary SELECT statements today, what will happen?  Will it actually return results based on a snapshot of Quickbooks from yesterday, ignoring all adds/changes/deletes since then?

Or, does only using the NOSYNC keyword have this behavior?

You say you use NOSYNC for joins.  Can you also use NOSYNC to speed up ordinary SELECT statements?

Last question.  You say you cannot put the .OPT file in a central location on the network and share it among users?  Or did you mean you could do that but only one user at a time?  So, each user must optimize separately and have their own .OPT file.  A nightly batch would have to run for each user on their workstation?

Thanks.

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-11-06 09:28:12

If you set QODBC to optimize as of yesterday, for example, and you do ordinary SELECT statements today it actually return results based on a snapshot of Quickbooks from yesterday, ignoring all adds/changes/deletes since then (but it will still test whether the optimizer needs to do a update sync). That's why if you know you want to use the optimized shapshot, you just use the NOSYNC tag.

NOSYNC can be used with single or multi-table joins.

You can copy the .opt file to other user locations, but an update sync by two users or even two sessions at the one time can corrupt the optimized table.

 

  Top 
  PH 
  
 Group: Members 
 Posts: 41 
 Joined: 2007-02-02 
 Profile
 Posted : 2008-11-06 11:03:41

OK, that makes perfect sense.  I'm really starting to understand this.

So, I installed at client.  I ran SP_OPTIMIZEFULLSYNC billitemline.

It ran about 30 min and completed successfully.

Then I ran a query:

select * from billitemline where refnumber = '10566-zwd'

But that still took a long time and it (1) optimized again or did more optimizing; (2) optimized other related tables.  (Of course the Nosync query was instant.)

So what happened here?  Why did it start optimizing again/more right after I completed optimizing that table?

With respect purely to SELECT statements, what is the standard practice when you want the fastest NOSYNC  SELECT statements.  Does one install for client, run SP_OPTIMIZEFULLSYNC ALL the first time (so all tables will be optimized for that user), then arrange for them to run SP_OPTIMIZEUPDATESYNC nightly? 

Thanks.

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2008-11-06 11:33:22

Why, because the QODBC optimizer has to update more than one table. When you used billitemline, it needs to check and resync Bill, BillItemLine, BillExpenseLine, BillLinkedTxn and BillToPay. A out-of-date timestamp on any of these tables triggers a resync on all of them.

Nightly updates are silly, your application should do a resync when it first runs against each table it uses and then only use NOSYNC.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to