-1

I have followed answers to similar questions already, including setting ARITHABORTH ON/OFF, clearing out buffers using DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE. Also tried converting parameters to local variables. But still having performance issues.

enter image description here

As seen in the screenshot, the same procedure from SSMS takes about 625 ms, but from the .NET client, takes about 3.5 seconds.

What is interesting is that data reads are also different. The "Binary" column has some data for the .NET client, but not for SSMS, but I am not an expert and do not know why.

Any help would be much appreciated.

Technology: SQL SERVER 2016 Standard

The underlying table contains about 3 million rows The parameters taken in by the procedure are usually very similar.

Once again, thank you in advance for your help.

Arvind S
  • 87
  • 1
  • 7
  • are u sure the dotnet client connects to the same DB that you use in SSMS – GuidoG Aug 16 '17 at 15:35
  • 1
    capture the sql command using the profiler. Then compare that with the command you tested in SSMS – GuidoG Aug 16 '17 at 15:36
  • @GuidoG Yes, of course I am using the same DB. The captured profile trace is attached already. I have also confirmed that both the SP calls use the same query plan – Arvind S Aug 16 '17 at 15:48
  • My next guess would be arithabort but I see you already tried that. Sorry I am out of ideas now. – GuidoG Aug 16 '17 at 15:52
  • http://www.sommarskog.se/query-plan-mysteries.html – Dan Guzman Aug 16 '17 at 16:08
  • @DanGuzman Yes, I did go through that earlier and tried almost everything in there including having a query store. – Arvind S Aug 16 '17 at 16:47
  • @ArvindS, are you running SSMS on the same machine as the app? If so and the plans are the same, that suggests slowness in the app code. If on different boxes, maybe network is the culprit. – Dan Guzman Aug 16 '17 at 16:54
  • @DanGuzman I am not. But if network is the culprit, how come the SQL profile shows 5 times slowness for the SPROC generated by the app? I suspect there is something else here. – Arvind S Aug 17 '17 at 09:34
  • @ArvindS, the duration reported by a SQL trace includes time taken by the client application to consume results, assuming more that one buffer is needed to return results. To rule out network latency, execute on the same machine using a Powershell script with `CommandType.StoredProcedure` for an rpc call like the app code. – Dan Guzman Aug 17 '17 at 10:40
  • @DanGuzman I did that both from the web server and localhost -> both are approx 500ms. I also created a blank .NET project with a simple call to the SPROC returning data to a grid view. Takes 3.5 secs. I have tried ARITHABORT, etc. Am running out of ideas now :( .. Also, I tried to execute the query from Excel on the webserver, still around 500 ms.. Only from the .NET MVC app, it take 3.5 secs – Arvind S Aug 17 '17 at 14:36
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/152166/discussion-between-dan-guzman-and-arvind-s). – Dan Guzman Aug 17 '17 at 14:41
  • Figured it out actually - Because I'm using Entity Framework, the connection string in my application has MultipleActiveResultSets=True. When I remove this from the connection string, the queries have the same performance in ADO.NET and SSMS. – Arvind S Aug 17 '17 at 15:06

1 Answers1

0

Because I'm using Entity Framework, the connection string in my application has MultipleActiveResultSets=True. When I remove this from the connection string, the queries have the same performance in .NET and SSMS.

Arvind S
  • 87
  • 1
  • 7