For the second run of the tests, we have added records to
different tables used in the reports. In a normal scenario, data would be added
to a live database in a real time environment or your data warehouse would be
updated once a night with the delta data.
For this test, the assumption is that the reports are
running off the live database, not a data warehouse. This simple test added 142
records to the saleorderheader table and 341 records to the salesorderdetail
table. After adding those records, we reran the load test to see what effects
we might have.
The second run took around 10 times longer than the first.
See Figure 4 which shows the results of the test. These results show that the
new records affected the performance dramatically. And in the 10 minute test,
the off the shelf caching did not appear to have much affect.
After running the test a second time though, the performance
increased dramatically. See Figure 5 to see how the reporting times increased. This
time the tests showed a 10 times faster increase from the test run in Figure
The test in Figure 4 had 4 errors shown. These 4 errors
included one http request timeout, 1 error accessing the performance counters
and 2 errors accessing the performance category "Network Interface."
The test in Figure 5 had 1 error shown. The error was that
the performance category "Memory" could not be accessed.
The Figure 4 percent of processor time was at 56.3%. Figure
5's test had percent of Processor time at 50.5%. This seems to show that the
reports are not using a significantly larger percentage of processor time when
adding new data to the cache.
When digging into the details for the scenarios of the tests,
the information should help us to see any speed patterns with the different
reports in the tests. When drilling down for the details in the Figure 4 test,
we see that the most time is in the first 2 scenarios. When looking at the
details in the Figure 5 test, the results are similar. The Figure 5 results are
faster, but the first 2 scenarios are the slowest scenarios in the test group.
These results do not show what the exact cause is of slower
responses. It looks like the caching takes a little while to show up in the
tests. Keep in mind that these tests do not conclusively show that the quicker
results in the Figure 5 test are due to Crystal caching. It could be caching done
at the database level as well. In future articles we will explore this subject.
For speeding up the response times there are different
methods to take. For instance, we could try to improve the SQL and tune the
database. If we want to do this, we could programmatically try to do some
things with the Crystal caching model. We will try to see if some of these
techniques could improve results in a future article.