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)
____________________________________________________________________________________________
<(E-Mail Removed)> wrote in message
news:8b09d9f0-7891-4c1a-a01c-(E-Mail Removed)...
> 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);
> }
|
Similar Threads
