SQL 语句在查询分析器执行很快，程序 Dapper 参数化查询就很慢（parameter-sniffing）（转）...

SQL 语句在查询分析器执行很快，程序 Dapper 参数化查询就很慢（parameter-sniffing）

 

这个问题困扰我好长时间了，使用SQLSERVER 事务探查器找到执行超时的SQL语句，参数查询都是通过执行exe sp_executesql 的存储过程调用，因为它能够分析并缓存查询计划，从而优化查询效率，但是现在反而很慢。本地调试没有问题，开始上线也没有问题，但是运行一个月左右有时候会出现超时现象： 
Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

 

                var param = new DynamicParameters();
                param.Add("@StDateNum", Convert.ToInt32(dStartDate.ToString("yyyyMMdd")), dbType: DbType.Int32);
                param.Add("@EndDateNum", Convert.ToInt32(dEndDate.ToString("yyyyMMdd")), dbType: DbType.Int32);
                param.Add("@StDate", dStartDate, dbType: DbType.DateTime);
                param.Add("@EdDate", dEndDate, dbType: DbType.DateTime);
                param.Add("@HotelCd", sHotelCd, dbType: DbType.AnsiString, size: sHotelCd.Length);
                return new SqlConnection(DBSetting.PriceAndRmFlow).Query<PriceAndRmFlow>(cmdText, param).ToList();

 

临时解决办法

1.就是把代码DbType重新指定不同类型，重新部署就好了，但根本原因还是没有找到。网上也看了好多文章，说是是参数类型不正确，必须设定为数据库一致的参数类型。另外Size也有影响，使用参数化后,因为字段类型错误导致了表扫描。已做修改，还在观察中。

2.看了几篇文章，初步分析是因为执行计划的问题，后面在SQL后面强制禁用了重用执行计划( OPTION (RECOMPILE))，本来想分析了SQL的执行计划，但是之前同样慢的SQL，查询起来又很快了。

3.可能是缓存不好的缓存计划，可以执行 DBCC FREEPROCCACHE 清除。

4.在这篇文章 http://stackoverflow.com/questions/10933366/sp-executesql-is-slow-with-parameters 中并且也用的是Dapper，解决方式是加了 WITH RECOMPILE 语句

parameter-sniffing

• Query runs fast, but runs slow in stored procedure
• SQL Server: Query fast, but slow from procedure
• Query times out when executed from web, but super-fast when executed from SSMS

SQL Server Management Studio 的默认 ARITHABORT 设置为 ON。 客户端应用程序将 ARITHABORT 设置为 OFF 可以接收不同的查询计划，使得对性能较差的查询进行故障排除变得困难。 即，同一个查询可以在 Management Studio 中快速执行，但在应用程序中却比较慢。 使用 Management Studio 排除查询故障时始终与客户端 ARITHABORT 设置匹配。

查询执行计划

select * from sys.dm_exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle) t ORDER BY sys.dm_exec_cached_plans.usecounts DESC

System.Data.DbType映射关系

AnsiString:VarChar
Binary:VarBinary
Byte:TinyInt
Boolean:Bit
Currency:Money
Date:DateTime