c# - Dapper 参数化查询导致问题的字符串值？

Question

我有下面的这个方法#1 查询，它使用 dapper 进行参数化，问题是即使在等待 30 秒后，使用这种方法查询也会超时，通常在 SSMS 上使用普通 sql 最多需要 1 秒。

然而，方法 # 2 查询实际上适用于在服务器端构建查询而不是参数化查询的地方。我注意到的一件事是，它可能与 FirstName 和 LastName 的过滤器有关，我在方法 #2 上对这些过滤器有单引号，但对方法 #1 没有。

方法#1 有什么问题？

Method # 1

string query = "SELECT *
                FROM dbo.Customer c                
                WHERE c.MainCustomerId = @CustomerId 
                AND (@IgnoreCustomerId = 1 OR c.CustomerID = @FilterCustomerId)
                AND (@IgnoreFirstName = 1 OR c.FirstName = @FilterFirstName)
                AND (@IgnoreLastName = 1 OR c.LastName = @FilterLastName)
                AND (@IgnoreMemberStatus = 1 OR c.CustomerStatusID = @FilterMemberStatus)
                AND (@IgnoreMemberType = 1 OR c.CustomerTypeID = @FilterMemberType)
                AND (@IgnoreRank = 1 OR c.RankID = @FilterRank)
                ORDER BY c.CustomerId
                OFFSET @OffSet ROWS
                FETCH NEXT 50 ROWS ONLY";       



                _procExecutor.ExecuteSqlAsync<Report>(query, new
            {
                CustomerId = customerId,
                IgnoreCustomerId = ignoreCustomerId,
                FilterCustomerId = filter.CustomerID,
                IgnoreFirstName = ignoreFirstName,
                FilterFirstName = filter.FirstName,
                IgnoreLastName = ignoreLastName,
                FilterLastName = filter.LastName,
                IgnoreMemberStatus = ignoreMemberStatus,
                FilterMemberStatus = Convert.ToInt32(filter.MemberStatus),
                IgnoreMemberType = ignoreMemberType,
                FilterMemberType = Convert.ToInt32(filter.MemberType),
                IgnoreRank = ignoreRank,
                FilterRank = Convert.ToInt32(filter.Rank),
                OffSet = (page - 1) * 50
            });


    Method # 2      

            string queryThatWorks =
                            "SELECT *
                 FROM dbo.Customer c                
                WHERE c.MainCustomerId = @CustomerId 
                AND ({1} = 1 OR c.CustomerID = {2})
                AND ({3} = 1 OR c.FirstName = '{4}')
                AND ({5}= 1 OR c.LastName = '{6}')
                AND ({7} = 1 OR c.CustomerStatusID = {8})
                AND ({9} = 1 OR c.CustomerTypeID = {10})
                AND ({11} = 1 OR c.RankID = {12})
                ORDER BY c.CustomerId
                OFFSET {13} ROWS
                FETCH NEXT 50 ROWS ONLY";

                _procExecutor.ExecuteSqlAsync<Report>(string.Format(queryThatWorks,
                customerId,
                ignoreCustomerId,
                filter.CustomerID,
                ignoreFirstName,
                filter.FirstName,
                ignoreLastName,
                filter.LastName,
                ignoreMemberStatus,
                 Convert.ToInt32(filter.MemberStatus),
                 ignoreMemberType,
                 Convert.ToInt32(filter.MemberType),
                 ignoreRank,
                  Convert.ToInt32(filter.Rank),
                 (page - 1) * 50
                ), null);

Answer 1

我以前见过无数次。

我敢打赌您的列是 varChar，但 Dapper 将您的参数作为 nVarChar 发送。当发生这种情况时，SQL Server 必须对存储在每一行中的值运行转换。除了速度非常慢之外，这还会阻止您使用索引。

参见 https://github.com/StackExchange/dapper-dot-net 中的“Ansi 字符串和 varchar”