Why there is a difference in output/performance while executing SQL query in SSMS and in code?

Question

I started to use dapper.net a while ago for performance reasons and that I really like the named parameters feature compared to just run ExecuteQuery in LINQ To SQL.

It works great for most queries but I get some really weird timeouts from time to time. The strangest thing is that this timeout only happens when the SQL is executed via dapper. If I take the executed query copied from the profiler and just run it in Management Studio its fast and works perfect. And it's not just a temporary issues. The query consistently timeout via dapper and consistently works fine in Management Studio.

exec sp_executesql N'SELECT Item.Name,dbo.PlatformTextAndUrlName(Item.ItemId) As PlatformString,dbo.MetaString(Item.ItemId) As MetaTagString, Item.StartPageRank,Item.ItemRecentViewCount
                        NAME_SRCH.RANK as NameRank,
                        DESC_SRCH.RANK As DescRank, 
                        ALIAS_SRCH.RANK as AliasRank, 
                        Item.itemrecentviewcount,
                        (COALESCE(ALIAS_SRCH.RANK, 0)) + (COALESCE(NAME_SRCH.RANK, 0)) + (COALESCE(DESC_SRCH.RANK, 0) / 20) + Item.itemrecentviewcount / 4 + ((CASE WHEN altrank > 60 THEN 60 ELSE altrank END) * 4) As SuperRank
                        FROM dbo.Item
                        INNER JOIN dbo.License on Item.LicenseId = License.LicenseId
                        
                        LEFT JOIN dbo.Icon on Item.ItemId = Icon.ItemId
                        LEFT OUTER JOIN FREETEXTTABLE(dbo.Item, name, @SearchString) NAME_SRCH ON
                        Item.ItemId = NAME_SRCH.[KEY] 
                        LEFT OUTER JOIN FREETEXTTABLE(dbo.Item, namealiases, @SearchString) ALIAS_SRCH ON
                        Item.ItemId = ALIAS_SRCH.[KEY] 
                        INNER JOIN FREETEXTTABLE(dbo.Item, *, @SearchString) DESC_SRCH ON
                        Item.ItemId = DESC_SRCH.[KEY]
                        ORDER BY SuperRank DESC OFFSET @Skip ROWS FETCH NEXT @Count ROWS ONLY',N'@Count int,@SearchString nvarchar(4000),@Skip int',@Count=12,@SearchString=N'box,com',@Skip=0

That is the query that i copy pasted from SQL Profiler. I execute it like this in my code.

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Conn"].ToString()))
{
    connection.Open();
    var items = connection.Query<MainItemForList>(query, new { SearchString = searchString, PlatformId = platformId, _LicenseFilter = licenseFilter, Skip = skip, Count = count }, buffered: false);
    return items.ToList();
}

I have no idea where to start from. I suppose there must be something that is going on with dapper since it works fine when I just execute the code.

As you can see in this screenshot. This is the same query executed via code first and then via Management Studio.

Screen shot

I can also add that this only (I think) happens when I have two or more word or when I have a stop char in the search string. So it may have something to do with the full text search but I cant figure out how to debug it since it works perfectly from Management Studio.

To make matters even worse, it works fine on my localhost with a almost identical database both from code and from Management Studio.

score 14 · Accepted Answer · answered Mar 29 '13 at 09:42

14

Dapper is nothing more than a utility wrapper over ado.net; it does not change how ado.net operates. It sounds to me that the problem here is "works in ssms, fails in ado.net". This is not unique: it is pretty common to find this occasionally. Likely candidates:

"set" option: these have different defaults in ado.net - and can impact performance especially if you have things like calculated+persisted+indexed columns - if the "set" options aren't compatible it can decide it can't use the stored value, hence not the index - and instead table-scan and recompute. There are other similar scenarios.
system load / transaction isolation-level / blocking; running something in ssms does not reproduce the entire system load at that moment in time
cached query plans: sometimes a duff plan gets cached and used; running from ssms will usually force a new plan - which will naturally be tuned for the parameters you are using in your test. Update all your index stats etc, and consider adding the "optimise for" query hint

answered Mar 29 '13 at 09:42

Marc Gravell

1,026,079
266
2,566
2,900

Sorry for my late reply but it was kind of hard to test. Anyway i think it had to do with the cached query plans. I got the time out locally now and restarted the server and then it worked. I tried adding a "OPTIMIZE FOR" thingy now. Hopefully that will help. Thanks for the insights! – Olaj Apr 18 '13 at 23:11
How do you correctly manage the "set" option for adhoc queries passed into Dapper/SqlCommand.ExecuteReader? – Terry Oct 05 '20 at 14:55
1

@Terry "set" options are at the connection level - so you'd need to open the connection, then (for example) `connection.Execute("SET CONCAT_NULL_YIELDS_NULL ON");`, then (the rest of your code); you can also prefix individual commands with `SET` instructions. Note, however, that not all `SET` options are reset by `sp_reset_connection` (which is executed automatically when connections are resued) - for example (and in particular) `SET TRANSACTION ISOLATION LEVEL {blah};` persists even between connection resets, which can be... fun – Marc Gravell Oct 05 '20 at 15:35
Thanks. And the process to debug why ADO.NET times out vs SSMS, simply start toggling SET options and see which happens to affect it? – Terry Oct 05 '20 at 16:24
I added OPTION (RECOMPILE) to the end of the SQL statemet that I sent to the DB and that solved it for me [based on point 3 above] – akraines Nov 18 '21 at 21:15
2

@akraines `recompile` is a terrible hint, that should almost never be used; `for unknown` is probably the one you want – Marc Gravell Nov 19 '21 at 07:23

score 8 · Answer 2 · answered Sep 11 '15 at 13:15

In ADO is the default value for CommandTimeout 30 Seconds, in Management Studio infinity. Adjust the command timeout for calling Query<>, see below.

var param = new { SearchString = searchString, PlatformId = platformId, _LicenseFilter = licenseFilter, Skip = skip, Count = count };
var queryTimeoutInSeconds = 120;
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Conn"].ToString()))
{
    connection.Open();
    var items = connection.Query<MainItemForList>(query, param, commandTimeout: queryTimeoutInSeconds, buffered: false);
    return items.ToList();
}

See also SqlCommand.CommandTimeout Property on MSDN

Link in English: https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?redirectedfrom=MSDN&view=netframework-4.7.2#System_Data_SqlClient_SqlCommand_CommandTimeout — Ryan, Nov 29 '18 at 23:07

score 3 · Answer 3 · answered Jan 22 '19 at 07:32

For Dapper , default timeout is 30 seconds But we can increase the timeout in this way. Here we are incresing the timeout 240 seconds (4 minutes).

    public DataTable GetReport(bool isDepot, string fetchById)
    {
        int? queryTimeoutInSeconds = 240;
        using (IDbConnection _connection = DapperConnection)
        {
            var parameters = new DynamicParameters();
            parameters.Add("@IsDepot", isDepot);
            parameters.Add("@FetchById", fetchById);
            var res = this.ExecuteSP<dynamic>(SPNames.SSP_GetSEPReport, parameters, queryTimeoutInSeconds);
            return ToDataTable(res);
        }
    }

In the repository layer , we can call our custom ExecuteSP method for the Stored Procedures with additional parameters "queryTimeoutInSeconds".

And below is the "ExecuteSP" method for dapper:-

    public virtual IEnumerable<TEntity> ExecuteSP<TEntity>(string spName, object parameters = null, int? parameterForTimeout = null)
    {
        using (IDbConnection _connection = DapperConnection)
        {
            _connection.Open();
            return _connection.Query<TEntity>(spName, parameters, commandTimeout: parameterForTimeout, commandType: CommandType.StoredProcedure);
        }
    }

score 2 · Answer 4 · edited May 23 '17 at 12:25

2

Could be a matter of setting the command timeout in Dapper. Here's an example of how to adjust the command timeout in Dapper: Setting Command Timeout in Dapper

edited May 23 '17 at 12:25

Community

1
1

answered Mar 28 '13 at 13:41

Jamie

132
1
7

score 0 · Answer 5 · answered Mar 18 '23 at 11:59

Dapper defaults to make the parameter VARCHAR(4000). On a large table this causes a very slow read, as if it's not using the indexes.

Change this:

var result = await _connection.QueryFirstOrDefaultAsync<Record>(sql, new { recordId });

to this:

var result = await _connection.QueryFirstOrDefaultAsync<Record>(sql, new { recordId = new DbString { Value = recordId, Length = 15 }});

(Or if you're confident that SQL injection isn't possible then put your parameter directly in the SQL string).

References:

SQL Server Hidden “Load Evil” (Performance Issue)With Dapper

Dapper.NET: The varchar(4000) default

Why there is a difference in output/performance while executing SQL query in SSMS and in code?

5 Answers5