0

So yeah, I'm currently stuck on a simple code that my app should do pretty quickly, considering the same query runs in SSMS in about 34 seconds and uses around 90mbps:

enter image description here

But then, look at what EF does while running the same query:

enter image description here

Not even using ADO.NET makes it go as fast as SSMS. What gives? I've yet to try this with .NET Framework. Currently using .NET Core 3.1. Is this a bug?

Any pointers are greatly welcome, thanks in advance!

DARKGuy
  • 843
  • 1
  • 12
  • 34
  • Possible duplicate of [sql runs fast in ssms slow in asp.net](https://stackoverflow.com/q/10175422/2029983). – Thom A Jan 26 '20 at 09:53
  • @Larnu yes and no. The problem is unrelated, but the suggestion from Reza worked. The post in that question talks about a stored procedure, something with the query plan, etc. This was a simple query, so all the other solutions didn't apply, so no, I don't think it's a duplicate, but it's related. – DARKGuy Jan 26 '20 at 21:40
  • *"something with the query plan"* Are you saying that an SP doesn't have a Query plan? You would be wrong. SQL Server caches query plans for all queries, not just SPs. – Thom A Jan 26 '20 at 21:46
  • I know that SPs do have a query plan, and queries too, but a SP is saved in the database, a query maybe cached. I might be wrong, yes, but to be honest that solution took me around an hour or so to find it, which is why I asked the question then kept googling, and just to try something that I thought it was unrelated (considering MARS is usually used to allow more queries to go through at the same time, but since I'm running a single one, logic tells me it'd use all the bandwidth, no?) it worked, so that's why I also shared my solution, I would still not consider it a duplicate :/ – DARKGuy Jan 26 '20 at 21:49
  • One of the reasons you used a parameterised query is so that the query plan can be reused, SP or not, they'll both go through the same process. You don't have to be using an SP for a query to suffer parameter sniffing. – Thom A Jan 26 '20 at 21:59
  • In this case I guess you are right. However the first time I read about "parameter sniffing" was in the answer chosen in that question, but neither Oded's answer or even the link he shared shed me a light (let's be honest, who's gonna read that wall of text?) so what Reza mentioned was the fix that worked after trying a lot of other suggestions around for 1h or so. Most users of SO come here when they're desperate and time is of the essence (like my case) so an answer you can find in a couple minutes is really valuable, especially if you can identify your problem in the question asked. – DARKGuy Jan 27 '20 at 01:07

1 Answers1

0

For those who encounter the same issue as me: The issue was because I was (rightfully) using MultipleActiveResultSets=true. Turns out I'd have to disable it for this specific bit of code, then I can resume using it. Thanks to @Reza on https://stackoverflow.com/a/51824444/1598811 for that suggestion :)

DARKGuy
  • 843
  • 1
  • 12
  • 34