Caching Parameters Performance Issue When Using SQLHELPER

  • Thread starter Thread starter lijinhao
  • Start date Start date
L

lijinhao

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 thought 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?


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);
}
 
You're looking in the wrong place for performance benefits. 99% of the time,
it's not how fast you "ask" the question (setup the Command object) but how
fast the server can process the answer. Smarter questions yield faster
results. I expect that both approaches you're using are avoiding late
binding and would have very similar performance.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________



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 thought 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?


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);
}
Click to expand...
 
Back
Top