XslGenerator Cache Before and After

Overview

The SQL commands shown below illustrate the dramatic improvements that can be achieved by using the XslGenerator Cache mechanism.

These are actual SQL commands captured from a SQL Profile Trace run on a page in the corporate Avectra website.

The fist table shows the SQL commands that fire each time this page loads without caching. The second table shows the SQL commands that fire with caching.

Based on unscientific sampling, the page loads in 2-3 seconds without caching and 0.5 seconds with caching.

The improvement gains are won because we reduce, by 40 percent, the number of SQL commands that need to fire, from 23 to 10. Put another way, the page needs almost two and a half times as many SQL commands to load without caching. This saves both processing time and round-trips from the web server to the SQL server, but we also save time by not waiting for the SQL commands to execute. When executed in a SQL Query Analyzer, these SQL commands fire in a split second, but the round-trips start to add up. Finally, we need to return much less data from the SQL Server to the Web server: we send over only 63691 bytes of data instead of 133540.

SQL code

Without Cache
23 commands

With Cache
10 commands