Hello everyone,

I was wondering how the caching in the .NET Pet Shop will increase the speed of the app. I was truly amazed with the results. The code below explains my test approach. The class offering the Cache storage is based on the Pet Shop example.

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args) {

int[] lengths = PrepareParamsArray();

DateTime start = DateTime.Now;
for (var t1 = 0; t1 < lengths.Length; t1++) {
GetWithCache(lengths[t1]);
}
DateTime stop = DateTime.Now;
TimeSpan test1 = stop - start;
Console.WriteLine("Test with cache time: {0} ms", test1.TotalMilliseconds);

start = DateTime.Now;
for (var t2 = 0; t2 < lengths.Length; t2++) {
GetWithoutCache(lengths[t2]);
}
stop = DateTime.Now;
TimeSpan test2 = stop - start;
Console.WriteLine("Test without cache time: {0} ms", test2.TotalMilliseconds);

start = DateTime.Now;
for (var t3 = 0; t3 < lengths.Length; t3++) {
GetWithCacheLonger(lengths[t3]);
}
stop = DateTime.Now;
TimeSpan test3 = stop - start;
Console.WriteLine("Test with cache long time: {0} ms", test3.TotalMilliseconds);

start = DateTime.Now;
for (var t2 = 0; t2 < lengths.Length; t2++) {
GetWithoutCacheLonger(lengths[t2]);
}
stop = DateTime.Now;
TimeSpan test4 = stop - start;
Console.WriteLine("Test without cache long time: {0} ms", test4.TotalMilliseconds);

Console.ReadKey();
}

private static SqlParameter[] GetWithCache(int i) {
SqlParameter[] parms = TestSqlParams.GetParameters("TEST2" + i);

if (parms == null) {
parms = Get2Params(i);
TestSqlParams.StoreParameters("TEST2" + i, parms);
}

return parms;
}

private static SqlParameter[] GetWithoutCache(int i) {
return Get2Params(i);
}

private static SqlParameter[] GetWithCacheLonger(int i) {
SqlParameter[] parms = TestSqlParams.GetParameters("TEST10" + i);

if (parms == null) {
parms = Get10Params(i);
TestSqlParams.StoreParameters("TEST10" + i, parms);
}

return parms;
}

private static SqlParameter[] GetWithoutCacheLonger(int i) {
return Get10Params(i);
}

private static SqlParameter[] Get2Params(int i) {
var parms = new[] {
new SqlParameter("@Quantity" + i, SqlDbType.Int),
new SqlParameter("@ItemId" + i, SqlDbType.VarChar, 10)};

return parms;
}

private static SqlParameter[] Get10Params(int i) {
var parms = new[] {
new SqlParameter("@Quantity" + i, SqlDbType.Int),
new SqlParameter("@ItemId" + i, SqlDbType.VarChar, 10),
new SqlParameter("@Desc" + i, SqlDbType.Char, 120),
new SqlParameter("@Amount" + i, SqlDbType.Int),
new SqlParameter("@Price" + i, SqlDbType.Money),
new SqlParameter("@ShipNo" + i, SqlDbType.VarChar, 40),
new SqlParameter("@ShipPlace" + i, SqlDbType.NChar, 30),
new SqlParameter("@Available" + i, SqlDbType.Bit),
new SqlParameter("@CanSellPlace" + i, SqlDbType.NText, 1024),
new SqlParameter("@Guid" + i, SqlDbType.UniqueIdentifier)
};

return parms;
}

public static int[] PrepareParamsArray() {
const int arrLength = 1000000;
var parmsLen = new int[arrLength];

for (var i = 0; i < arrLength; i++) {
parmsLen[i] = i % 45;
}
return parmsLen;
}

}
}

And the class that based on the Hashtable implementation.

using System;
using System.Collections;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
class TestSqlParams
{
private static Hashtable cache = Hashtable.Synchronized(new Hashtable());

public static void StoreParameters(string key, params SqlParameter[] parameters)
{
cache[key] = parameters;
}

public static SqlParameter[] GetParameters(string key)
{
var cached = (SqlParameter[]) cache[key];
if (cached == null) return null;

var cloned = new SqlParameter[cached.Length];

for(int index = 0, cacheLen = cached.Length; index < cacheLen; index++)
{
cloned[index] = ((SqlParameter) ((ICloneable) cached[index]).Clone());
}

return cloned;
}
}
}

Result:

Test with cache time: 3432 ms
Test without cache time: 1070 ms
Test with cache long time: 16982 ms
Test without cache long time: 6349 ms

So is there any error in my way of thinking or just in the test implementation?

Caching will definitely improve performance, but the actual tests would depend on how much load you have on the server, how complicated or time consuming your queries are etc etc. Simply testing caching on a standaloen server with one request will not help you much. The concept is simple: instead of fetching from the DB the code will fetch the data from the server memory, and the time difference would be the time your app would have taken to get the data from the DB.

Also, try to use SqlCacheDependency to invalidate the cache based on DB changes, so that you dont need to set any time limits on the cached data expiry. It will automatically happen once a record in the "monitored" DB table changes.

HTH,

Vivek