October 2005

New Features in SDK 5.0—Part I

Back fresh from a Hawaiian vacation and working on the new and improved developer forums, the AlphaGeek now delves into the features of the SDK runtime in QuickBooks 2006.

As you know, QuickBooks 2006 makes some pretty radical changes over previous versions of QuickBooks. These changes include the new SuperNav (see Figure 1) and, of course, the new SQL database (built on the Sybase SQL Anywhere platform, in case you thought the AlphaGeek was lying to you when he said, over and over again on the forums, that QuickBooks would not be running on SQL Server).

Figure 1. QuickBooks 2006 Super Navigator

We’ve spent a lot of time this year making sure that we did everything we could to optimize the performance of the SDK with the new database. (No, there is no way to directly access the database outside of QuickBooks. That’s still the job of the SDK, which ensures that all data exchange happens according to QuickBooks business rules.) As a result, SDK 5.0 doesn’t have a whole lot of new features, but what’s there is pretty powerful and will help you maximize the performance of your application. In this article, we’ll cover two important new features: query iterators and a new way of populating custom fields inherited by transactions.

Query Iterators
If you spend a lot of time on the forums, you know that one of the key bits of advice for optimizing the performance of applications with QuickBooks is to avoid exchanging large quantities of data with QuickBooks in a single request. These large quantities of data occur most frequently in queries against potentially long lists (like the customer list, which may have as many as 29,000 entries in QuickBooks Enterprise) or transaction queries (which may have thousands or even millions of transactions returned).

In each case, a large response requires QuickBooks to allocate (and reallocate) large sums of memory to hold the XML response string as it is assembled. The memory block must then be marshaled across process boundaries to your application. Then you have to parse the XML (or QBFC does it for you), and parse performance becomes nonlinear to the size of the XML string being parsed.

Our recommendation has always been to use query parameters like MaxReturned and the NameRangeFilter or TxnDateRange filter to chunk the data and get just a small piece (50 to 100 records) of the data at a time. The QuickBooks UI does much the same thing when you view a list like the customer list. It retrieves only one page of data at a time, and it gets the next page only when you move beyond the bottom of the page it has displayed.

Chunking data is great, but it is cumbersome and difficult to manage in code. The customer list is probably one of the easiest to deal with, and Listing 1 shows how painful it is to manage just the customer list. (Dealing with items, for example, is much more difficult if you want the whole item list.)

Listing 1. Old Way of Chunking the Customer List

SDK 5.0 introduces a new way to get a manageable chunk of data at one time: query iterators. The type of list or transaction list you need to deal with doesn’t matter. Note: Some lists don’t support iterators because the lists are typically quite small, like the CustomerMsg list and the Terms list.

As an example, Listing 2 shows how to use iterators to accomplish the exact same walk through the customer list shown in Listing 1.

                      

Listing 2. New Way of Chunking the Customer List

Notice how the code in Listing 2 is much simpler. I don’t have to skip a duplicated customer, and I don’t have to manage complex logic for the special cases and detect the end case for the loop within the code of the loop itself.

As you start to think about implementing chunking you will come to the obvious question: What’s the optimal size for each chunk? That will vary depending on the extent of the detail in each customer record. Your friend the AlphaGeek did a little experimenting to prepare this article: I set up an empty company file and pumped it full of 13,000 customer records that had basic information in each—the customer name, the company, and the billing address. Then I wrote a little program to time QBXMLRP2’s ProcessRequest call during a complete iteration through the customer list with MaxReturned set to 25, 50, 100, 200, 500, and 1000 for each timing run.

The results are shown in Figure 2.The experiment was certainly not exhaustive, but the results do lend support to our advice of limiting ourselves to approximately 100 records at a time. Although the minimum time shown in Figure 2 is near the 250 mark, I’m not actually parsing the response in my test and the customer records I created were certainly not extensive (a more extensive customer record would result in a larger XML response per customer).

Figure 2. CustomerQuery Performance Results

A few final points are worth mentioning with respect to iterators. First, although the example and the performance test I tossed together use a fixed chunk size with every request as I iterate, a fixed chunk size is certainly not a requirement. I can freely change the MaxReturned value with each request as I continue the iterator. This flexibility is great if, for example, I have a resizable UI grid being populated with customer data. I simply set MaxReturned to some reasonable multiple of the number of lines in the grid and fetch another page of data as needed while the user scrolls around in my UI.

Second, my earlier example shows how to walk through an entire list, and the iterator is automatically destroyed by QuickBooks when the walk is complete. However, sometimes an application may decide it is done walking a list before it has completed the iteration. Then the application must tell QuickBooks it is done with the iterator by sending a query request with the iteratorID and the iterator attribute set to Stop (as opposed to Start or Continue). Finally, each iteratorID assigned by QuickBooks is unique, so an application can, if necessary, maintain more than one iterator even for the same list or transaction query type.

Populating Custom Fields in Transactions
A while back, the AlphaGeek covered the details of custom fields inherited by transactions. The transaction itself inherits custom fields from the entity (Customer or Vendor), and the individual lines of the transaction inherit custom fields from the items referenced by each item line. Populating the custom fields in the transaction or transaction line is a matter of calling DataExtAdd or DataExtMod for each transaction ID or transaction line ID that needs to be updated. The performance problem occurs when an individual transaction (an invoice, for example) has many item lines and each item line has one or more custom fields to populate (for example, if a Web site needs to push sales orders to QuickBooks with a customer choice of size and color for each item line in a customer order).

In an extreme case of a customer order with 50 items, each needing color and size, we wind up making 101 requests to QuickBooks: 1 to add the transaction and 100 to populate the color and size custom fields in each of 50 item lines. Worse, for each request QuickBooks must retrieve the transaction from the database, update a line, and then write the transaction back to the database—only to do it again with the next request, and so on. A simple experiment of this scenario took nearly 5 minutes to push a single transaction with all the custom field details to QuickBooks.

Well folks, the AlphaGeek has been watching your back! After hearing this tale of woe from several developers, we took your case to the SDK runtime engineers and they came up with a great solution. It’s now possible to populate custom fields (or even app-private data extensions) for each line of an invoice, estimate, sales order, sales receipt, credit memo, or purchase order. You’ll still have to use the DataExtAdd or DataExtMod request to set the custom fields that were inherited from the entity (Customer or Vendor in the transaction itself), but at most that’s fewer than 10 calls per transaction. The individual line item custom fields could easily result in tens or even hundreds of calls per transaction.

The secret is a new DataExt sub-aggregate in any LineAdd or GroupLineAdd aggregate for common transactions such as invoice, estimate, sales order, sales receipt, credit memo, or purchase order. The DataExt aggregate has three fields: the OwnerID (0 for custom fields), the data extension name, and the desired value for the data extension. For example, an InvoiceAdd for a single order of shoes would look like the XML shown in Listing 3.

Listing 3. Setting Custom Fields Directly in an InvoiceAdd Request

In the same scenario for a customer order with 50 items, we went from taking 5 minutes to add the transaction using the old method to taking just more than 6 seconds (vs. 4 seconds to add the transaction without setting any custom fields). Yes, you are reading right. That’s a 50x performance improvement!

Ready to Start Coding
SDK applications will automatically benefit from most of the performance improvements in QuickBooks 2006. By using some of the new performance-directed features of SDK 5.0 and QuickBooks 2006, and by doing a little extra work in your application, you can achieve some truly dramatic improvements. I hope this brief tour has started you thinking about how best to take advantage of these features in your own applications.

Iterators are especially interesting if you are concerned about the apparent responsiveness of your application’s UI as it interacts with QuickBooks data. Now you can very conveniently duplicate the behavior of the QuickBooks UI, loading only the data you need to fill the screen, reloading as the user scrolls down in a list, and so forth.

In upcoming columns we’ll talk about other exciting features of SDK 5.0, such as support for processing credit card transactions from an application using a QuickBooks Merchant Service account so that the transactions reconcile in QuickBooks as though the card had been processed directly in QuickBooks. SDK 5.0 features also include support for a standard way that Web-based applications can communicate with QuickBooks, even when a firewall is in the way. Until then, happy coding!

Next: Pack your brain with more SDK wisdom with a visit to the AlphaGeek Archive, a roundup of all the AlphaGeek's columns.