The ExecuteSQL via FileMaker is painfully slow at 8-9 seconds per insert into the Vendor table. This could be a show stopper for using the QODBC driver for us. Do you know of any process for speeding this up? Or do you know if your other FileMaker customers have been able to speed this up?

QODBC Batch Stored Procedures

These allow you to start a batch for a given table and all insert/updates will be queued until the sp_batchupdate command is issued. This allows for fewer round trips to QuickBooks which increases performance when doing large transfers of records from external systems (like FileMaker Pro) to QuickBooks.

The sp_lastinsertid stored procedure will return the ListID/TxnID plus an error message column for every row added to the batch.

Note: Each batch is limited to 500 transactions.

• sp_batchclear tablename – clears the current batch started with sp_batchstart.
• sp_batchstart tablename – starts a new batch. All inserts/update issued on this table will be batched until sp_batchupdate is issued.
• sp_batchupdate tablename – sends the batched transactions to QuickBooks. ListID/TxnIDs and error messages are available through sp_lastinsertid tablename.
  

Hi Tom,

Do I have to keep track of the 500 transactions with a counter and then issue the sp_batchupdate or does the driver do it automatically in the background?

Also, is there a limit on the following syntax ?

UPDATE tablename SET fieldname1 = value1, fieldname2 = value2, .....................
WHERE fieldname x = value x

I am trying to update the ItemInventory for: sales price, salesdesc, salestaxcode, costprice, costdesc, costtaxcode (not the actual fieldnames) and it fails unless I break it into 4 update queries (price&desc, Tax, Price&Desc, Tax).  Even with Optimizer on with 2.5 k Items to update it is taking an age.

A series of multiple INSERT INTO iteminventory (fieldnames)  VALUES (................)  is really quick, and does not seem to re-connect to QB on each query, as the QODC driver dialog appears at the start of the while loop and does not re-appear - with the multiple update queries running as above the dialog keeps popping up and scanning the whole 5.5 k record table. 

Any Ideas ?

INSERTs go directly into the table, while UPDATEs run the WHERE query first and then performs the updates. So it's important that the WHERE corresponds with a QODBC jump-in (as these act as indexes). Run:

sp_columns tablename

to see the jump-ins of the table you are using.

As far as sp_batch is concerned, I recommend you count the batch inserts/updates and run sp_batchupdate tablename every 200 inserts/updates (not five hundred). The number of UPDATE columns that can be used depends on the table used.

Hi Tom,

These 4 updates execute individually / in sequence fine:

UPDATE ItemInventory
SET SalesDesc = 'Dimple - Black - A4½',SalesPrice = 46.80851
WHERE FULLNAME = '1201355'

UPDATE ItemInventory
SET TaxCodeForSaleRefFullName = 'S'
WHERE FULLNAME = '1201355'

UPDATE ItemInventory
SET PurchaseDesc = '',PurchaseCost = 24.05
WHERE FULLNAME = '1201355'

UPDATE ItemInventory
SET TaxCodeForPurchaseRefFullName = 'S'
WHERE FULLNAME = '1201355'

But this composite fails (inc in VBDemo)

UPDATE ItemInventory
SET SalesDesc = 'Dimple - Black - A4½', SalesPrice = 46.80851 , TaxCodeForSaleRefFullName = 'S', PurchaseDesc = '', PurchaseCost = 24.05, TaxCodeForPurchaseRefFullName = 'S'
WHERE FULLNAME = '1201355'

Error is:  [QODBC] Error parsing complete XML return string

Any Ideas ?

I have logged these errors with the QODBC Software Engineer under Support Ticket ID: BR00000047.

In the meantime, you can switch back to QODBC v6, simply rename fqqb32.dll to fqqb32.204 and run the QODBC v6.00.00.176 installer. I do it all the time!

Hi Tom

Any update on this, as the deployment uses OEM driver and I don't think I can roll that back to v6 as the licencing system is different.

Cheers

Dave

Ok, the problem occurs because your SETs in the composite update are not in the exact order of the columns in the ItemInventory table. Please run:

sp_columns ItemInventory

and resort your combined update SETs in the order of the table.