As mentioned earlier this year in the first installation of this article series, it can be useful to have information on the performance of our BI tools. The purpose of this load testing article is to give some understanding into where caching can help improve performance. 

The BI tools used here are running on the web. In that first article, the testing was performed on standard Crystal reports. Now we show how to use the High Shareabliity Caching and test the reports using that model.

The High Shareablility Caching model is for reports with underlying data with minimal changes (think data warehouse). Also, if the parameters will be similar or run often, this report is a good target for high shareability.

System Requirements

·         Visual Studio 2008 Team Suite (Developer and Test Edition used)

·         Crystal Reports 10

·         SQL Server 2005

Our tests are performed on a web application using three web pages to display three different Crystal Reports. Those reports are based on the Adventureworks database. Each page displays the report using the standard Crystal Viewer object with the report bound to that object on an asp.net page.

Our test strategy involves two sets of tests. The first load test consists of standard or non cached reports. The second load test consists of those tests using the Crystal reports caching with the same reports. 

As a reminder, the Visual Studio load testing application is used. The strategy is to create two web tests that view the three different reports we have created. These different tests should cycle through the reports pages in a semi random manner, at some point go to the last page, and first page in at least one instance. Also, the tests include using the Crystal Viewer goto page function to maneuver to specific pages.

First, we run first "non-cached" load test. Those results are used as the benchmark. Then the second load test uses two web tests with the same reports and the same maneuver sequence.  The only difference is the use of the cached reports rather than the standard report.

The reports use the SQL Server sample database Adventureworks to pull sales data from. The web tests use all three reports. The first test shows sales by customer order. The next test includes all the reports, including Sales Person report, the customers report, and the Sales Orders report. The Sales Person and customers' reports have a lot of pages, over 1,000 each, and the Sales Person report is a summary that does not display much more than one page. 

The load tests are set up with the idea that the users of these reports have a mix of internal LAN connections and a mix of DSL type connections. The browser mix will be kept to minimum, just one flavor of IE (7.0) and Firefox. More details can be found later.

Caching in reporting is useful for any highly shareable resource in an application. For our reporting, Crystal Reports default caching is easy to utilize in any application. To use the caching inherent in Crystal, we invoke in .NET code the cached report. Below is an example of how to do this.

This caching should be used with highly shareable data. In other words, for reports that will be run in a similar manner with infrequent data refreshes may be a good candidate for caching. If those running the reports use similar parameters, those reports are not good candidates for caching.  

If you're running reports against real time data stores that are updated frequently between report runs, I would not consider those good candidates. That may be a good test for a later time though!

As stated above, we are using the load test project that comes in Visual Studio Team Suite. If you have the Visual Studio Team System Test edition, this should include all testing projects as well.  This article is not a tutorial for using the Visual Studio test projects, we assume familiarity with this.

The first load test (NonCached.loadtest) consists of the non-cached reporting web pages that will be displayed multiple times. Figure 1 shows what the load test looks like, including scenarios.  Both load tests have the same scenario setup.

Figure 1: Non Cached load test

Scenario 1 is the only scenario used to keep this simple. We use two web tests, the CustomerNonCached web test and the AllNonCached web test. The CustomerNonCached web test displays the "CompanySales.rpt" report, which displays the orders by customer. The test displays the first page of the report then pages through multiple pages of this report. The web page is the default.aspx web page which has a Crystal Report Viewer on it bound to CompanySales.rpt.

The AllNonCached web test displays all three reports, including CompanySales.rpt, SalesOrders.rpt, and SalesPerson.rpt. AllNonCahced first displays default.aspx page (CompanySales.rpt is bound to that Crystal Viewer) and pages through the report.

Then the test displays SalesOrders.aspx which has a crystal report viewer bound to the SalesOrders.rpt report. This just displays once, because SalesOrders.rpt is a one page report.  Then SalesPerson.aspx is displayed, which, once again, is a Crystal report viewer bound to the report SalesPerson.rpt. Pretty simple stuff I will admit. 

The test pages through the report one page at a time and also navigates to the end of the report.  The test then displays default.aspx again and navigates across a couple of pages of the report.  The test then navigates back to Salesperson.aspx and displays a few more pages before ending.

That shows what is going on in the AllNonCached tests. The NonCached.loadtest runs the CustomerNonCached.webtest 35% of the testing time, and AllNonCached.webtest 65% of the time. This test also distributes the browsers running the test between IE 7.0 and Firefox 2.0. I set IE up at 84% and Firefox at 16%. Again this was to keep the tests relatively simple. 

Finally, the network mix is set up to use a variety of connection speeds. For this test I assume that all users will have a minimum of cable/DSL speeds. So then 16% of the mix are LAN connections, with 58% cable/DSL of 1.5 Mbps, and 26% Cable/DSL of 384 mbps. This test assumes the reports are for external customers for the most part rather than internal customers.

The test is set up with a constant user load count of 25. The warm up time for the test is three minutes and the test duration runs for 15 minutes. Again, this is a simple test to see if caching pays off in this scenario. We could increase the durations and load counts to really stress the system, but we are not after that here.

After the test ran, we wanted to see how many pages were served, and what the response times were. See Figure 2 for a representation of the results summary. 

Figure 2: Results Summary for NonCached load test

You will notice the Average Page time on the right hand side, which is for the top 5 SLOWEST pages. These times are actually faster than the Cached test, which is shown in a later section.  Looking through the summary report, we find that the average test time for AllNonCached.webtest is 559 seconds. And the average test time for CustomerNonCached.webtest is 87.9.

Digging into the AllNonCached results, we find that there are no test failures. That probably has to do with not putting in any validation rules in the test. The only errors would be with the web server not returning data, and that was not a problem in this case. The results also show that AllNonCached was run 26 times.

Digging into the CustomerNonCached, we find again no test failures. And for the same reasons, this web test had no validation rules. This test was run 13 times during the 15 minutes. 

Other interesting data comes from the Controller and Agents resource category. For this test we learn that the only instance used the processor 93.16% of the time. And the available memory at test completion was 113Kb.

As you can see by looking at Figure 3, the Cached load test is set up similarly to the non-cached load test. To keep this test as a fair comparison, the mix of browsers, connections and tests were kept the same. So IE 7.0 had 84% and Firefox 16%, Lan 16%, 1.5Mbps Cable DSL 58%, and 384K Cable DSL 26%. Finally, the similar web tests, CustomerCached.webtest, and AllCachedReports.webtest are run with the same percentage. CustomerCached is 35% and AllCachedReports at 65%.

Figure 3: Cached Load Test Setup

The CustomerCached.webtest navigates through the CompanySales.rpt report the same way as in the CustomerNonCached.webtest. This time it uses the defaultcached.aspx web page, which instantiates the cached report, not the regular report. In this case the code is as shown in code Listing 1.

Listing 1

protected void Page_Load(object sender, EventArgs e)
{
  CachedCompanySales csales = new CachedCompanySales();
  CrystalReportViewer1.ReportSource = csales;
}

In this case we use the object CachedCompanySales instead of CompanySales. This is the default Crystal Caching object, which is created with every report in your Visual Studio project. Just add this simple code in your page_load event to invoke caching.

In every other way the web test is the same, other than using defaultcached.aspx.

In the same way, AllCachedReports.webtest is used. For this, we use defaultcached.aspx to load the CompanySales.rpt, SalesOrdercached.aspx to load the cached salesorder.rpt report, and SalesPersonCached.aspx to load the cached salesperson.rpt report. Each of these aspx pages loads the reports cache object to the crystal viewer.

For the cached load test, see Figure 4. The first part of this report shows the slowest Avg. page time, which is more than what we saw in the non cached results. This makes sense when I think that the first time the page is loading, all the caching is happening. This would explain the differences. 

Figure 4: Cached Load Test Summary

When we look a little deeper, this is where we see the benefits of caching. For instance, if we look at the average test time for AllCachedReports, the number is 412 sec. For nonCached, the number was 559. This appears to be a 30+% better performance. When looking how many tests were run, for AllNonCached, 40 tests were run.

Looking at CustomerCached, the average test time was 96.5 sec. In the non-cached version, it was 87.2 sec. So it appears that for some reason, caching was not helpful for this report. But the gain in noncached was around 10% in this case. When looking at how many tests were run, 14 tests were run for CustomerCached. It appears that the gain from AllCachedReports allowed CustomerCached to be run more times.

Overall, it appears that using caching increased performance for these reports over 30%. So reporting falls into this type of situation and would be a great candidate to use caching. 

This was a simple testing of the crystal caching model. There is potential for other areas to test on, which I hope to cover in future articles. Take these tests in the spirit they are given as a starting point for you to consider caching. Your results may vary, but I would love to hear other test scenarios and how they fared.