We have a test ASPX page that will be used for a
demonstration. We will use WAST
to load test this and show some SQL Profiler trace output for both the original
pattern with no locks and the improved pattern. A WAST test for one minute
should suffice.
Listing 5
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat=server>
void Page_Load(object src, EventArgs e)
{
string key = "version";
string version = HttpRuntime.Cache[ key ] as string;
if ( version == null )
{
version = GetCustomObj();
HttpRuntime.Cache.Insert(
key
, version
, null
, DateTime.Now.AddSeconds( 15 )
, Cache.NoSlidingExpiration );
}
lit.Text = version;
}
string GetCustomObj()
{
using(SqlConnection conn = new SqlConnection(
"server=(local);uid=;pwd=;database=master;trusted_connection=true;"))
using(SqlCommand cmd = new SqlCommand("select @@version", conn))
{
conn.Open();
return cmd.ExecuteScalar() as string;
}
}
</script>
<html>
<body>
<asp:Literal runat="server" id="lit" />
</body>
</html>
Figure 1
Notice that there are multiple requests to the database when
there is nothing in the cache. This is worsened when the SQL being executed is
more intensive. Now, for the improved pattern and the SQL Profiler results:
Listing 6
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="Common.Caching" %>
<script runat=server>
void Page_Load(object src, EventArgs e)
{
lit.Text = TCache<string>.Get(
"version"
, 15
, delegate()
{
using(SqlConnection conn = new SqlConnection(
"server=(local);uid=;pwd=;database=master;trusted_connection=true;"
))
using(SqlCommand cmd = new SqlCommand("select @@version", conn))
{
conn.Open();
return cmd.ExecuteScalar() as string;
}
});
}
</script>
<html>
<body>
<asp:Literal runat="server" id="lit" />
</body>
</html>
Figure 2
So the WAST test started at roughly 11:14:52.733, basically
every 15 seconds a background call was made. Also note that when the test
stopped (at around 11:15:52) there was one more call that was on the background
thread as well. This was because it was previously accessed more than once.