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 : Another optimizer questionSearch Forum

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

 New Topic 
 
 Post Reply 
[1]  
 Another optimizer question 
 Author   Message 
  FlyingDutchman 
  
 Group: Members 
 Posts: 1 
 Joined: 2009-01-21 
 Profile
 Posted : 2009-01-21 09:36:48

Hi, I am a new user to QODBC (Or Quickbooks, for that matter).

I need to post internet activity (invoices and bills) to Quickbooks, run locally.  I have a small vb.net program that runs a webservice that creates 2 csv files which then get downloaded using ftp  So far easy.

Then the program loops through the csv and checks for each record whether it already exists.  Code looks like:

strSQL = "SELECT COUNT(*) FROM BillItemLine WHERE RefNumber='" & myDR("RefNumber") & "' AND itemLineItemRefListID='" & myDR("itemLineItemRefListID") & "'"

myCmd = New OdbcCommand(strSQL, myQB)

iCount = myCmd.ExecuteScalar()

It runs Ok for the BillItemLine, but when I run it against InvoiceItemLine, the Optimizer feels the need to "optimize" the invoiceLine table, as well as the Invoice and InvoiceLinkedTxn tables.  It does this about 250 records at a clip, and in between it is in a "Waiting On Quickbooks" state.  It has now been running for 2 hours and 12 minutes and we are on record 8,500.  There are at least 20,000 invoices in the table.

I am supposed to run this program every night.  What would be the best advixce to avoid this?  Is it an ODBC DSN setting ( I am using "with every connection - Load updated data first"), or should I use the "UNOPTIMIZED" hint in the query?

Or am I going about it the wrong way entirely?

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2009-01-21 16:16:10

If you're checking for the existance of a row (or record... especially new records) you should always use the UNOPTIMIZED tag:

strSQL = "SELECT COUNT(*) FROM BillItemLine UNOPTIMIZED WHERE RefNumber='" & myDR("RefNumber") & "' AND itemLineItemRefListID='" & myDR("itemLineItemRefListID") & "'"

This also bypasses the optimizer update process.

See: How do I setup the QODBC Optimizer? Where are the Optimizer options? for more info.

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to