29 Oct 2009
In this second part of the series, Eric examines the usage of caching in Crystal Reports. He makes use of Visual Studio Team System testing to compare performance between cached and non-cached pages. After providing a short introduction and outlining the system requirements, he examines the test strategy in detail and provides a comprehensive coverage of both the NonCached and Cached loads with a detailed analysis of the results with the help of screen shots and source codes.
This article has not yet been rated.|
Views (Total / Last 10 Days):
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
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.
|Crystal Reports Caching|
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
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!
|NonCached Load Test Setup|
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
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
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
|NonCached Test Results|
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
|Cached Load Test Setup|
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.
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
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.
|Cached load test results|
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.
No comments posted yet.