ADO.NET中应用大数据量参数化查询的效率分析

在网上经常看到的一条网友技术提问大意是“为什么我写的SQL查询语句在SQL Server查询分析器中可以瞬间Run出结果，在.NET程序中却要二十几秒(甚至要数分钟)？”。近日笔者在项目开发过程中也遇到相同问题，经过多方查找资料和自己不断调试分析，找到问题本质并得出解决办法。下面简要写出分析过程和问题根源以供遇到相同问题童鞋参考。

 

一、问题描述

 

　　1.数据量偏大

 

　　a) 首先在网上看到类似问题的一个共性都是数据量在万条以上，结果集一般也达到千条以上；

　　b) 并且对比自己项目中包含出现此问题SQL语句的自定义方法同样为多表关联查询、表数据量数万条、结果集大致在2000条左右；

　　c) 同样对比相同项目中包含未出现效率问题SQL语句的自定义方法，表数据量不过千条、结果集仅为百条左右；

 

　　故得出第一点结论为虽达不到海量数据级，但表数据量偏大。

 

　　2.与ADO.NET中托管方法无关

 

　　a) 有些网友反映使用了数据库查询通用类SqlHelper.cs,有些网友使用的是ADO.NET最常用的SqlCommand、SqlDataAdapter适配器对象载入DataSet中；

　　b) 在笔者个人项目中使用了包括SqlHelper.cs类和DataTable.Load两种方法进行测试，通过单步跟踪发现都是在数据装载到数据集合中时出现严重效率延迟，二者并没有对查询效率的改善有本质区别；

　　c) 笔者测试了读取数据到SqlDataReader中的方法，在大数据量情况下并不存在效率问题；

 

　　故得出第二点结论为效率问题与ADO.NET读取SQL Server数据方法没有直接联系(SqlDataReader的情况后面会给出解释)。

 

　　3.均使用了参数化查询方法

 

      参数化查询(SqlParameter)具有可过滤SQL-Injection攻击、通过预编译技术提高执行效率，诸多好处就不一一提及。那么为什么使用了参数化查询方法反而会降低了查询速度呢？

 

二、问题分析

 

      1.出现上述问题的一个重要原因是恰恰是没有正确使用ADO.NET的参数化查询方法。在参数化查询代码编写过程中很多开发者忽略了指定查询参数的类型，这将导致托管代码在执行过程中不能自动识别参数类型，进而对该字段内容进行全表扫描以确定参数类型并进行转换，消耗了不必要的查询性能所致。这也解释了为什么大数据量表查询才会出现极大的效率反差。而才提到的通过SqlDataReader读取数据时并无额外效率损失的原因，是因为SqlDataReader保持数据库长连接，并不物理缓存数据，通过Read()方法逐条读取，也就避免了全表扫描。错误的参数化查询方法带来的另一点伤害是导致表索引失效，一切本应是索引扫描的操作均由表扫描替代。错误代码示例如下

 

     SqlCommand sc = new SqlCommand(strSql, Connection);

     sc.CommandType = CommandType.Text;

     sc.CommandTimeout = 300;

     sc.Parameters.Add(new SqlParameter("@mcode", mCode));

     sc.Parameters.Add(new SqlParameter("@date", tbDate));

 

      2.在大数据量表查询中还有另一个需要注意的地方，就是做好查询语句的优化，正所谓一条完善复杂SQL查询语句要优于一条简单SQL查询语句+客户端多步处理;具有大量返回记录的查询语句要做好SQL端分页处理，避免一口气加载到客户端数据集合中再做分页或过滤返回的操作行为。这方面请大家自行学习，不是本次讨论的重点。

 

三、解决方法

 

      找到了引发问题的原因，问题自然迎刃而解。只需将上面参数化相关语句拆分为初始化并指定数据类型和赋值两步操作即可。正确代码示例如下

 

     SqlCommand sc = new SqlCommand(strSql, Connection);

     sc.CommandType = CommandType.Text;

     sc.CommandTimeout = 300;

     if (blCode != null)

         if ((blCode = blCode.Trim()) != "")

         {

             SqlParameter para_blcode = new SqlParameter("@blcode", SqlDbType.VarChar, 12);

             para_blcode.Value = blCode;

             sc.Parameters.Add(para_blcode);

         }

     if (ht != null)

         if ((ht = ht.Trim()) != "")

         {

             SqlParameter para_ht = new SqlParameter("@ht", SqlDbType.TinyInt);

             para_ht.Value = ht;

             sc.Parameters.Add(para_ht);

         }

 

      并且在链接打开后，查询执行时执行一次预编译方法

 

     Connection.Open();

     sc.Prepare();

 

      至此检索效率已经有了几何级数的提升，本文不仅将解法写出，同时列举了分析过程，也是希望看这篇文章的网友同时能够加强独立思考和调试排错的能力。限于笔者知识有限，文中若有纰漏请不吝赐教。文止于此，欢迎大家共同交流。