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?
|