Me : writing EF query. (EntityFramework)

EF : Transforms it into a good SQL, uses sp_executesql to run it with parameters. (You know, the correct way)

SQL server : Oh I see ! I’ll now compile an execution plan based on your values and apply it to all future values, even if you asked for 1 day of data and production query asks for 4 years of data. (timeout of cause)

Me after 1 week of research : “Option recompile”. FU. Same query in production : 0.1 seconds.

Me : Soooo you are telling me that disabling optimizations optimize execution time ?

Bad solution, but I honestly don’t see a good one. I could inline arguments into query, but it’s worse. We are not PHP !

  • 4
    I hate that these things exist...
  • 1

    Well, I try to keep it extremely rare in code. Right now, only 3 or 4 queries use "recompile", about 15 use Optimize for Unknown (Which has advantages to use query store)
  • 2
    I meant the whole situation! The fixes are warranted and they work, no problemo.

    It's just that it's a pretty complicated chain of events and I can get stuck when debugging that deep, so I might just rewrite the query to get it to work and move on to the next task... But I always get this bad feeling that something magical just happened behind the scenes but I couldn't catch it. I hate not knowing why acts in a strange (and therefore interesting) fashion, but there's so much pressure to just work around it and churn out a feature. There are some weird behaviors that I never could get to the bottom of and it haunts me.

    I know that computers aren't magical, there has to be an explanation. I NEED to know. But I eventually have to let it go, which sucks. Oh, a side project is a good outlet for these things. I guess there are more fun things than hard-to-debug problems but you always learn sooo much from it.
  • 1

    Here. That helped my understand what happens in SQL server

  • 2

    TL;DR : The SQL server will compile your stored procedure (In my case the EF queries) for the first parameters it receives. And it doesn’t care about next batch of parameters.
    I still struggling to understand: WHY ?
    We have multi-tenant DB for clients. Some clients represent 100 rows in database. Some more than 100.000. And SQL server manages them… with parameters received on the first execution of the query. So if a “big” client uses a future first, he’ll never have a problem. If for some reason Querry store is reset and a small uses the same feature: Well, yep it’s going to optimize the execution plan. And use it for the big client too. And big client will.. timeout
  • 0
    @jurion Fuck, I'm going to learn more about SQL server than I know today. Thanks. Funny it's a swedish domain, got to be faith hah 🙏
  • 0
    @jurion Cool, this is all new for me. I haven't been excited to learn about databases since before I had a terrible DB class in University.
  • 2

    For me too.
    Go back 3 weeks :
    One client: This page is not loading.
    I’m checking. Yes, the query is timing out (30 sec).
    First try : Write manual query. Execution: Instant. (0.01 seconds)
    Me : Hmmmm, EF must’ve messed up query generation.
    Intercepting EF generated query. Run it again (In production of cause). Same : 0.01 sec.
    Me : WTF ?
    Modifying query just a little. Added an empty check “true == true” in “where” clause.
    EF execution: 0.01 sec.
    Me ; ??????????
    Finally tracked the problem. If you want to search more : google SQL Parameters sniffing.
  • 0
    >SQL Server

    Well theres your problem right there! Just migrate to Postgres and everything will be solved! /s

    Seriously though postgres is really good
Your Job Suck?
Get a Better Job
Add Comment