Caching Parameters Performance Issue When Using SQLHELPER

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);
}
 
W

William Vaughn \(MVP\)

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Datalist not displaying records from stored procedure 2
No Results 1
How do you get output parameters? 4
BUG? SELECT statment w/ Return Value doesn't work together 5
Passing a DataReader between methods and getting RETURN_VALUE 6
Output Parameters null when using SqlCommand.ExecuteReader()...? 6
problem with simple stored procedure execution 8
Parameters wont work on SQL Server sproc 3

Top