[QODBC-Desktop] Speeding up Adding and Editing Invoices with ASP.NET
Posted by Juliet (QODBC Support) on 30 April 2010 06:40 AM
Instructions about AspCompat
Using QODBC on ASP.NET is slow when retrieving field values. "SELECT * FROM Vendor" took ASP.NET to retrieve a 3 to 5-second avg per record or row. But switching to AspCompat="true" will yield 45 to 60 records per second. Retrieval using classic ASP is fine too.
ASPCompat is a configuration setting at the start of your code.
Example of ASPCompat
<%@ Page AspCompat="true" Language="VB" %>
Special Notes for Insert and Update Query
INSERTs go directly into the table, while UPDATEs run the WHERE query first and then perform the updates. So it's important that the WHERE corresponds with a QODBC jump-in (as these act as indexes). Run:
to see the jump-ins of the table you are using.
You can also use batches to submit your daily invoices instead.
QODBC Batch Stored Procedures Instructions
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 or recording from external systems (like FileMaker Pro) to QuickBooks.
QODBC Batch Stored Procedures Detail
sp_batchclear tablename – clears the current batch started with sp_batchstart.
sp_batchstart tablename – starts a new batch. All inserts/updates 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 the sp_lastinsertid tablename.
Possible Solutions Provided by Customers
I was able to do a workaround for the speed issue by getting the assigned ListID's for the class and customer list and inserting those into a field in my SQL Server tables in the ASP.NET application to the corresponding customers and classes. This way, only one call to QB is run (the actual insert or update of an invoice) instead of 6 or 7 I had before trying to find the right ListID to insert into the invoice.