I have this Method# 1 query below that is parameterized using dapper, problem is the query times out with this approach even after waiting 30sec and normally it takes max of 1 sec on SSMS with plain sql.
However Method # 2 query actually works where the query is built on the server side instead of parameterized one. One thing i have noticed is, it might have something to do with filter for FirstName and LastName, I have single Quote on Method #2 for those filter but not for Method #1.
What is wrong with Method # 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);
I've seen this countless times before.
I'm willing to bet that your columns are varChar
, but Dapper is sending in your parameters as nVarChar
. When that happens, SQL Server has to run a conversion on the value stored in each and every row. Besides being really slow, this prevents you from using indexes.
See "Ansi Strings and varchar" in https://github.com/StackExchange/dapper-dot-net