Performance issue when using caching Parameters

Topics: Caching Application Block , Data Access Application Block
Nov 28, 2008 at 7:23 AM
I use SqlHelper to handle my application DAL. I saw there were 2 methods, one is CacheParameters(string key, SqlParameters[] params), another is GetCachedParameters(string Key). I hought they are used for improving performance. But I make a example to test the performance against with caching and without caching. In the end, I found using caching is not more fast than without caching. actually, No caching has good performance. Could you tell me the reason to help me out?  In addtion, Could you tell me the difference between using caching parameter and not using caching parameter?

here is my example code snippet 

private

SqlCommand GetCmd()

 

{

 

SqlCommand cmd = new SqlCommand();

 

cmd.CommandType =

CommandType.StoredProcedure;

 

 

SqlParameterCollection sqlParams = cmd.Parameters;

 

sqlParams.Add(

new SqlParameter(USERGROUPIDS_PARM, SqlDbType.VarChar));

 

sqlParams[USERGROUPIDS_PARM].Direction =

ParameterDirection.Input;

 

 

sqlParams.Add(

new SqlParameter(USERGROUPNAMES_PARM, SqlDbType.VarChar));

 

sqlParams[USERGROUPNAMES_PARM].Direction =

ParameterDirection.Input;

 

sqlParams.Add(

new SqlParameter(COMPANYIDS_PARM, SqlDbType.VarChar));

 

sqlParams[COMPANYIDS_PARM].Direction =

ParameterDirection.Input;

 

sqlParams.Add(

new SqlParameter(RIGHTIDS_PARM, SqlDbType.VarChar));

 

sqlParams[RIGHTIDS_PARM].Direction =

ParameterDirection.Input;

 

sqlParams.Add(

new SqlParameter(OUTPUTFILTERRIGHTNAME_PARM, SqlDbType.Bit));

 

sqlParams[OUTPUTFILTERRIGHTNAME_PARM].Direction =

ParameterDirection.Input;

 

sqlParams.Add(

new SqlParameter(OUTPUTFILTERRIGHTDESC_PARM, SqlDbType.Bit));

 

sqlParams[OUTPUTFILTERRIGHTDESC_PARM].Direction =

ParameterDirection.Input;

 

sqlParams.Add(

new SqlParameter(CHANNELID_PARM, SqlDbType.Int));

 

sqlParams[CHANNELID_PARM].Direction =

ParameterDirection.Input;

 

 

return cmd;

 

}

 

 

private static SqlParameter[] GetParametersForUserGroupRights(string key)

 

{

 

SqlParameter[] parms = SqlHelper.GetCachedParameters(key);

 

 

if (parms == null)

 

{

parms =

new SqlParameter[] {

 

 

new SqlParameter(USERGROUPIDS_PARM, SqlDbType.Int),

 

 

new SqlParameter(USERGROUPNAMES_PARM,SqlDbType.VarChar),

 

 

new SqlParameter(COMPANYIDS_PARM, SqlDbType.Int),

 

 

new SqlParameter(RIGHTIDS_PARM, SqlDbType.VarChar),

 

 

new SqlParameter(OUTPUTFILTERRIGHTNAME_PARM, SqlDbType.Bit),

 

 

new SqlParameter(OUTPUTFILTERRIGHTDESC_PARM, SqlDbType.Bit),

 

 

new SqlParameter(CHANNELID_PARM, SqlDbType.Int)};

 

 

SqlHelper.CacheParameters(key, parms);

 

}

 

return parms;

 

}

 

protected void Button1_Click(object sender, EventArgs e)

 

{

 

Stopwatch sw = new Stopwatch();

 

 

int max = 100000;

 

sw.Start();

 

for (int i = 0; i < max; i++)

 

{

 

SqlParameter[] parms = GetParametersForUserGroupRights("test");

 

parms[0].Value =

"";

 

parms[1].Value =

"";

 

parms[2].Value =

"";

 

parms[3].Value =

"2;13";

 

parms[4].Value =

true;

 

parms[5].Value =

true;

 

parms[6].Value = 0;

}

sw.Stop();

 

TimeSpan ts = sw.Elapsed;

 

 

string elapsedTime = ts.TotalMilliseconds.ToString();

 

sw.Reset();

sw.Start();

 

 

for (int i = 0; i < max; i++)

 

{

 

SqlCommand cmd = GetCmd();

 

cmd.Parameters[USERGROUPIDS_PARM].Value =

"";

 

cmd.Parameters[USERGROUPNAMES_PARM].Value =

"";

 

cmd.Parameters[COMPANYIDS_PARM].Value =

"";

 

cmd.Parameters[RIGHTIDS_PARM].Value =

"";

 

cmd.Parameters[OUTPUTFILTERRIGHTNAME_PARM].Value =

true;

 

cmd.Parameters[OUTPUTFILTERRIGHTDESC_PARM].Value =

true;

 

cmd.Parameters[CHANNELID_PARM].Value = 0;

}

sw.Stop();

 

TimeSpan ts1 = sw.Elapsed;

 

 

string elapsedTime1 = ts1.TotalMilliseconds.ToString();

 

Response.Write(elapsedTime +

"<BR>");

 

Response.Write(elapsedTime1);

}

 

public static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)

 

{

parmCache[cacheKey] = commandParameters;

}

 

 

 

public static SqlParameter[] GetCachedParameters(string cacheKey)

 

{

 

SqlParameter[] cachedParms = parmCache[cacheKey] as SqlParameter[];

 

 

if (cachedParms == null)

 

 

return null;

 

 

SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

 

 

for (int i = 0, j = cachedParms.Length; i < j; i++)

 

clonedParms[i] = (

SqlParameter)((ICloneable)cachedParms[i]).Clone();

 

 

return clonedParms;

 

}

 

OutPut: Using Cache is       1070.1701
            Not Using Cache is 0593.0955 

Nov 28, 2008 at 3:04 PM
The parameter cache is used to cache parameters that were discovered from a command. Parameter discovery requires a round-trip to the database to perform the discovery. It is not designed for you to cache your parameters that you new-upped.