DBXL v2.3 Performance of SharePoint Submit

One of the main features of DBXL v2.3 is the ability to map your XML documents to SharePoint. This means that you potentially have three storage locations for your documents’ data:

• DBXL stores the XML version of the document
• DBXL maps your XML document to your SQL database
• DBXL can map the same XML documents to SharePoint

One of the main benefits is that you can continue using SharePoint as you have done – no change there – but also now use SQL to do more advanced reporting on your XML data. What changes however, and this is a good thing, is that you will see a significant performance benefit.

The graph below summarizes the results of Qdabra’s performance testing using DBXL v2.3 and WSS 3.0 installed on a server running Windows Server 2003 with 1GB of RAM.  We have plotted the time required (in seconds) to submit an increasing number of documents, starting with 10 and going up to 1000. We used the Expense Report sample that ships with InfoPath 2007 to test two methods:

1. In the first (in purple), we create a SharePoint form library. We then submit documents to this library.
2. In the second test (in red), we create a Document Type in DBXL. Then we configure a SharePoint mapping in DAT, and proceed to submit documents to DBXL. DBXL then maps those documents to SharePoint. End result is same as #1, we just get there through DBXL.

We can see that it’s fairly constant with some slower cases when dealing with a small number of documents. This is likely caused by constant processing time required to initialize (or page in) submit processing code. For the direct submit to SharePoint, the submit operation tends to asymptote around 7 seconds. For the DBXL submit, the submit operation levels out around 2 seconds per document – that’s a 5 seconds per document savings! Here are the raw numbers.

Now, let’s look at client-level submit, i.e. the time required to submit one document. The graph below shows twenty attempts, each time submitting one document, using the same methods described above. The SharePoint submit (blue) varies between five and almost eight seconds, with an average of 6.2 seconds.

For the DBXL submit (red) the submit operation varies between one and four seconds, with an average of 2.0 seconds.

 
DBXL v2.3 Performance of SharePoint Query

DBXL is also able to query SharePoint lists. To illustrate a comparison between InfoPath’s built-in SharePoint query and DBXL’s QuerySharePoint web method, we will use a SharePoint list containing 3144 items, for all counties in the 50 states in the United States plus the District of Columbia. You can download it here.

We create InfoPath forms that load this list on-load, and measure the average time required to load this list. The first form will use InfoPath’s built-in SharePoint query data connection; the second form will use DBXL’s QuerySharePoint web method. The graph below shows the result of ten iterations of this test. The built-in InfoPath query takes an average of 78.30 seconds, while QuerySharePoint takes 17.42 seconds.

The benefit in using QuerySharePoint is the ability to perform server-side filtering, allowing us to avoid loading the entire list of 3144 items. In order to illustrate this, we modify the Travel Request sample form that comes with InfoPath 2007. In the Destination section, we will make use of two dropdowns. In the first, we will list all of the states in the United States, i.e. the unique values in the State column in our SharePoint list. We estimate the on-load time, required to obtain the list of state abbreviations, to be 11.78 seconds (average of 10 attempts).

When the user selects a state from that dropdown, the InfoPath form will query the SharePoint list to obtain the possible counties for the selected state. The average wait time when a state is selected is 2.52 seconds. If we add the time required to load the state abbreviations plus the average time required to load the counties for a particular state, we come to a total “wait” time of 14.30 seconds. Even if the user selects another state, the wait time (for each state change) will be an additional 2.52 seconds, on average.