Why there is a difference in output/performance while executing SQL query in SSMS and in code?
ado.net, database-performance, sql, sql-server
Solution
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
Problem
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. 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.