首页 > 代码库 > 参数化查询速度慢的原因及分析
参数化查询速度慢的原因及分析
测试环境：sql2005 + .NET2.0
同样的SQL语句，参数化查询和SQL语句直接执行的速度对比。数据库中存放的字段类型是varchar
结论：
1.对参数要设置正确的 DbType(varchar = AnsiString, nvarchar=String,char=AnsiStringFixedLength,nchar=StringFixedLength)
2.尽量使用数据库类型对应的DbType的。
3.尽量给参数的size设置大小
Code:
class Program { private delegate void Action(); static void Main(string[] args) { while (true) { ExecuteDataSetWithParam(false); ExecuteDataSetWithParam(true); ExecuteDataSetWithParamAnsiString(false); ExecuteDataSetWithParamAnsiString(true); ExecuteDataSetWithParamSqlDbType(false); ExecuteDataSetWithParamSqlDbType(true); ExecuteDataSetNoneParam(); Console.ReadLine(); } } private static void ExecuteDataSetWithParam(bool len) { #region Sql string sql = @""; #endregion List<IDataParameter> paras = new List<IDataParameter>(); paras.Add(new System.Data.SqlClient.SqlParameter("@Deleted", DbType.String) { Value = http://www.mamicode.com/"0" }); if (len) foreach (SqlParameter v in paras) { v.Size = v.Value.ToString().Length; } Invoke(delegate { ExecuteDataSet(sql, paras); }, "参数化查询" + (len ? " 指定长度" : string.Empty)); } private static void ExecuteDataSetWithParamAnsiString(bool len) { #region Sql string sql = @""; #endregion List<IDataParameter> paras = new List<IDataParameter>(); paras.Add(new System.Data.SqlClient.SqlParameter("@Deleted", DbType.AnsiString) { Value = http://www.mamicode.com/"0" }); if (len) foreach (SqlParameter v in paras) { v.Size = v.Value.ToString().Length; } Invoke(delegate { ExecuteDataSet(sql, paras); }, "参数化查询AnsiString" + (len ? " 指定长度" : string.Empty)); } private static void ExecuteDataSetWithParamSqlDbType(bool len) { #region Sql string sql = @""; #endregion List<IDataParameter> paras = new List<IDataParameter>(); paras.Add(new System.Data.SqlClient.SqlParameter("@Deleted", SqlDbType.VarChar) { Value = http://www.mamicode.com/"0" }); if (len) foreach (SqlParameter v in paras) { v.Size = v.Value.ToString().Length; } Invoke(delegate { ExecuteDataSet(sql, paras); }, "参数化查询SqlDbType" + (len ? " 指定长度" : string.Empty)); } private const string dbConnStr = ""; private static void ExecuteDataSetNoneParam() { #region Sql string sql = @"‘ Order By RegisterDate desc"; #endregion Invoke(delegate { ExecuteDataSet(sql, null); }, "SQL语句拼接"); } private static void Invoke(Action action, string name) { try { var star = DateTime.Now; action.Invoke(); var time = DateTime.Now - star; Console.WriteLine(name + " - TimeLost:" + time.ToString()); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } } private static void ExecuteDataSet(string sql, List<IDataParameter> AParas) { using (IDbConnection conn = new SqlConnection(dbConnStr)) { using (System.Data.IDbCommand cmd = new SqlCommand(sql)) { cmd.Connection = conn; AddParam(cmd, AParas); System.Data.IDbDataAdapter adapt = new SqlDataAdapter(cmd as SqlCommand); var ds = new DataSet(); adapt.Fill(ds); } } } private static void AddParam(IDbCommand cmd, List<IDataParameter> AParas) { if (AParas == null) return; foreach (var v in AParas) { cmd.Parameters.Add(v); } } }
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。