0

I have a MS SQL query that takes about 40 seconds to run in ASP.NET (EF Linq). I have captured it in Profiler and it shows a duration of about 40 seconds:

enter image description here

However, when I copy/paste this in SQL Management Studio it runs in 00:00:00 seconds. It's a simple select on a single table (no Joins, Views, Stored Procedure) and it returns about 10.000 rows.

Execution plan:

enter image description here

I read this question on StackOverflow witch points to this blog and placed

SET ARITHABORT OFF

above the query in Management Studio but that doesn't make it slow so I don't think that is the issue.

One strange thing is that we migrated the application to a faster server with Windows 2012 / SQL 2014 Web Edition and since that time queries seems to run slower. The same query did run a lot faster on our old Windows 2008R2 Server with SQL Express 2008R2.

Community
  • 1
  • 1
roberth
  • 924
  • 9
  • 17
  • There are a number of `SET` options that can have *significant* impact, and which don't default to the same. The first thing I would try would be to run something like https://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/ both in SSMS and via raw ADO.NET (you might need to hack the query to make it friendly, since `PRINT` is a pain to use from ADO.NET, but: maybe just `select @@OPTIONS` in both is enough). See which flags (if any) are different. I will try locally as a check. – Marc Gravell Mar 31 '16 at 19:52
  • Hmm, checking locally, the only delta is `ARITHABORT` which you are already handling. I wonder, then, if this is bad parameter optimization / query plan generation. If you are writing the SQL yourself, you can use the `OPTIMIZE FOR` / `UNKNOWN` hints. If you aren't writing the SQL yourself: then... yeah, that's a toughie. We have to do that quite often - oddly enough, internally we call this "the Jon Skeet / new user problem" - the layout of our data tends to be massively skewed for new users and massively engaged users like Jon. – Marc Gravell Mar 31 '16 at 19:58
  • @Marc, thanks for your help. SQL Studio gives `Options = 5496`, but from my application (created an `IDbConnection` on the entity framework context) it gives `Options = 5432`. Difference is `ARITHABORT` indeed. – roberth Mar 31 '16 at 20:33
  • maybe you're lazy loading related fields in EF.. if lazy loading is enabled, disable it and try again – JamieD77 Mar 31 '16 at 21:03
  • I don't know about the number of records of your table but as I see you have a parameter Date which selects from 1900-01-01, which means a full table scan, except you have a proper index on AppCustomerId. It would help if you could post the table and index definitions – Gabor Mar 31 '16 at 21:31

2 Answers2

0

It looks like the amount of data in the columns returned was the problem. I narrowed it to the minimal set of columns needed and it became a lot faster. I expect the text-columns are especially slowing things down in the application, however it doesn't seem to effect the same query in Management Studio.

PS. After noticing the performance gains, I rewrote the whole thing in raw SQL and the query duration went down to nearly 0 seconds.

roberth
  • 924
  • 9
  • 17
0

I think some of the rows have been locked by another action affecting the data you're trying to get but it's not committed yet. Try this for your select statement

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT * FROM tblA WHERE something
Ngoc Lam Man
  • 1
  • 2