| Speeding up Adding and Editing Invoices with ASP.NET |
| Author |
Message |
|
|
| Posted : 2007-01-04 04:06:25 |
I have a customer who needs to add and edit invoice on the fly using an ASP.NET application I developed. I got the application to successfully add and edit invoices but it takes over a minute to complete the transaction. This would be unaccetable to my client. They do hundreds of invoices daily and need them to go in as quick as possible. I tried the optimizer but that will not work because they need real time data from QB to work with.
Currently I have them using the SDK from Intuit to handle this process but that method is always crashing and is not reliable. I figured the QODBC method using SQL would be more reliable and faster. Is there something I am doing wrong to cause the slowness? |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-04 10:07:02 |
Using QODBC on ASP.NET is slow when retrieving field values. "SELECT * FROM Vendor" took ASP.NET to retrieve 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. For example:
<%@ Page AspCompat="true" Language="VB" %>
<script runat="server">
' The components is created at construction time.
Dim comObj As MyComObject = New MyComObject()
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
' The object is first used here.
comObj.DoSomething()
End Sub
</script>
<html >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
</form>
</body>
</html>
|
|
|
|
|
|
| Posted : 2007-01-05 05:20:47 |
| Thanks for your quick response. I already have the AspCompat="true" in the Page declaration. It still takes upward of 2 to 3 minutes to update or insert an invoice. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-05 09:30:28 |
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. You can also use batches to submit your daily invoices instead.
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 or recored 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.
|
|
|
|
|
|
| Posted : 2007-01-16 02:22:28 |
Unfortunately I cannot run batches. My client needs the invoice to immediately go into QuickBooks because when it does they send out the invoice to the customer and need that QB invoice number right away. 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.
Maybe my explanation can help others who are having the same issue as myself.
Thanks for all your help Tom. |
|
|
|
