SQL 执行很快，Ado.net 的参数化查询很慢的原因。

网上有这个问题：

http://www.cnblogs.com/bluedoctor/archive/2011/03/04/1970866.html

http://www.cnblogs.com/fxwdl/archive/2008/07/31/1257409.html

但我使用的查询参数很简单：是 Int32 和数据库是一样的。 都设置为   可空。

ADO.Net 有问题的SQL：

exec sp_executesql N'select count(1) As [Cou] From [dbo].[TF_FeesReceipts] As [TfFeesReceipts] left join [dbo].[TM_Customer] As [TmCustomer] on ([TfFeesReceipts].[CommID] = [TmCustomer].[CommID] And [TfFeesReceipts].[CustID] = [TmCustomer].[CustID]) left join [dbo].[T_USER] As [TUser] on ([TfFeesReceipts].[UserCode] = [TUser].[USER_CODE]) where [TfFeesReceipts].[CommID] = @CommID_1',N'@CommID_1 int',@CommID_1=100005

ADO.Net 没有问题的脚本，而使用：

exec sp_executesql N'select count(1) As [Cou] From (select [TfFeesReceipts].[ReceID] As [ReceID],[TfFeesReceipts].[PaidAmount] As [BillsAmount],[TfFeesReceipts].[BillsSign] As [BillsSign],[TfFeesReceipts].[BillsDate] As [BillsDate],[TfFeesReceipts].[ChargeMode] As [ChargeMode],[TmCustomer].[CustName] As [CustName],[TUser].[USER_NAME] As [UserName] From [dbo].[TF_FeesReceipts] As [TfFeesReceipts] left join [dbo].[TM_Customer] As [TmCustomer] on ([TfFeesReceipts].[CommID] = [TmCustomer].[CommID] And [TfFeesReceipts].[CustID] = [TmCustomer].[CustID]) left join [dbo].[T_USER] As [TUser] on ([TfFeesReceipts].[UserCode] = [TUser].[USER_CODE]) where [TfFeesReceipts].[CommID] = @CommID_) As [__SubQuery__]',N'@CommID_ int',@CommID_=100005

参数的生成方式相同， 应该不是网上所说参数的问题。

上述SQL 是从 Sql Profiler 中截获的，且在查询分析器中执行很快，所以不会是SQL 的问题。

把第一个ＳＱＬ：　Count(1) 改为　sum(1)  , 则执行很快。

继续测试一下，count(*) 也很快。

count(主键) 也很快

count(0) 也很快

count(子查询) 也很快：

exec sp_executesql N'select count(1) As [Cou] From (select count(1) As [Cou] From [dbo].[TF_FeesReceipts] As [TfFeesReceipts] left join [dbo].[TM_Customer] As [TmCustomer] on ([TfFeesReceipts].[CommID] = [TmCustomer].[CommID] And [TfFeesReceipts].[CustID] = [TmCustomer].[CustID]) left join [dbo].[T_USER] As [TUser] on ([TfFeesReceipts].[UserCode] = [TUser].[USER_CODE]) where [TfFeesReceipts].[CommID] = @CommID_) As [__SubQuery__]',N'@CommID_ int',@CommID_=100005

突然有一种想用 sum(1) 替换 count(1) 的冲动。

回头再看执行一次第一种SQL： count(1) 的性能，发现居然也很快了。

数据库: Sql 2008 R2 x64, 操作系统： windows 2008R2 x64 , 数据库在本机。 内存 4G

-----------------------------------------------------------------------------------------------------------

后来又发现了这个问题，且表现很不稳定。

测试代码：

        public ActionResult QuerySlowlyTest()
        {
            var sl = new StringLinker();
            using (var scope = new MyOqlConfigScope(ReConfigEnum.SkipCache | ReConfigEnum.SkipLog | ReConfigEnum.SkipPower))
            {
                var st = Stopwatch.StartNew();
                dbr.View.VmFeesChangeFilter
                    .Select(o => new Columns(o.UserCode, o.UserName))
                    .Distinct()
                    .Where(o => o.CommID == 100005 & o.UserName != null)
                    .ToMyOqlSet();

                sl += ("普通参数化查询：" + st.ElapsedMilliseconds) + Environment.NewLine;

                st.Restart();

                var cmd = dbr.View.VmFeesChangeFilter
                    .Select(o => new Columns(o.UserCode, o.UserName))
                    .Distinct()
                    .Where(o => o.CommID == 100005 & o.UserName != null)
                    .ToCommand();
                cmd.Command.Parameters[0].DbType = DbType.Int64;

                var da = new System.Data.SqlClient.SqlDataAdapter(cmd.Command as System.Data.SqlClient.SqlCommand);
                using (var conn = new System.Data.SqlClient.SqlConnection(
                    System.Configuration.ConfigurationManager.ConnectionStrings["pm"].ConnectionString
                    ))
                {
                    conn.Open();
                    var ds = new DataSet() ;
                    da.Fill(ds);
                }

                sl += ("自定义参数查询：" + st.ElapsedMilliseconds) + Environment.NewLine;


                st.Restart();

                dbo.ToDataTable("pm", @"select distinct usercode, user_name
from vm_feeschange_filter
where commid = 100005 and user_name is not null
order by user_name");

                sl += ("SQL查询：" + st.ElapsedMilliseconds) + Environment.NewLine;
            }

            return Content("<pre>" + sl.ToString() + "</pre>");
        }

三个时间值：

普通参数化查询：201213
自定义参数查询：1087
SQL查询：1826

这个问题单独测试时，可以复现，　但放到页面中，　时有时无。

但可以得出这个结论：

放大参数的　DbType 数据类型（数字类型），可以避免查询过慢的情况。

另外，根据以往的贴子，还要：

对参数要设置正确的　　DbType(varchar = AnsiString, nvarchar=String,char=AnsiStringFixedLength,nchar=StringFixedLength)