@ErikDarling now that's interesting. I would have thought CT was lighter because it only tracked PK rather than full before & after. What makes CT so burdensome?
We had CDC on our main application DB but removed it because of extra load on the logs was not sustainable.
I'd leave the database in single thread redo, if you're still having issues then note the redo thread OS ID (sys.dm_os_threads with workers with sessions, etc) and then get a wpr for cpu
Especially when some of the issues that come through are things like "our security team wants security updates for our SQL Server 2005 instance for TLS 1.3"
and then hem and haw and bitch and moan for 3-4 months because they don't like the answer of they need to upgrade to supported versions which do support 1.3
It feels like it would be hard to quantify the tradeoffs. You're potentially making the query perform worse (by replacing the IN list with that OPENJSON table expression thing). But you're also easing the burden on the plan cache - possibly very significantly. I guess which one you want depends on what problems your specific system has.
@JoshDarnell Yes. There are trade-offs and eliminating the cache pollution problem will likely be seen as a big win by most. There will be other people who experience new problems, or find the new implementation is a lot slower when executed rapidly enough etc etc etc
@SeanGallardy Dumb people deserve to have problems
> Hundreds of years from now, it will not matter what my bank account was, the sort of house I lived in, or the kind of car I drove... But the world may be different because I did something so bafflingly crazy that my ruins become a tourist attraction.
@PaulWhite And given that reality, it would be nice to be able to pick which one you want to do per query, rather than turning the whole thing on or off at the system level with that "UseCompatibilityLevel" method when the DB Context gets configured.
There are codebases I've worked on where some IN lists were practically static, or varied in only small ways, so it was okay for them to be string literals. And then some IN lists were dynamic, changed every time, and probably would benefit from the OPENJSON thing.
I remember reading and sort of agreeing with the reasoning for avoiding TVPs somewhere, but now I can't remember all the details. Paul is right that part of the problem was needing a TYPE.
Deferred compilation and interleaved execution are useful, but you need to be on a very modern SQL Server. Same with PSP and the various feedback thingies
Having a table/variable/temp table with varying numbers of rows suiting a small number of different plan options is not that unusual, and recompiling isn't always an answer
One can work around these things quite well when all the code is in a stored procedure or other module, but people will use SQL-generating frameworks
It's funny though how often problems due to plans not being reused turn into problems because plans are being reused
@JoshDarnell Do you happen to know if EF can make use of the WITH RESULT SETS option of EXECUTE?
From the point of view of establishing a contract between the code and database, I mean
But this is just my normal preference for assigning tasks where they're best performed I guess
@J.D. Raw SQL ('ad hoc' SQL?) isn't the same as a stored procedure or function though. I was thinking generally about development. If you can execute a module with a guaranteed return shape, a framework could allow you to write code against contracts before the SQL-side coding had been done. Something like that.
Maybe all that goes against the intent of using EF, I don't really know, having never used it
It just bugs me there's so much angst about writing a 'query' in some programming language, then worrying about how the database will interpret the generated result. It might be nice to be able to pass 'entities' to SQL Server via an interface and receive something similar back (if needed).
@PaulWhite So forgive me if I'm misunderstanding you. But I just took a quick look at what the devs who use EF here do, and they do execute the procedure in raw SQL, but it's automatically mapped to the type of class the FromSqlRaw() method is called off of. E.g. someDataContext.SomeDataObject.FromSqlRaw("EXEC StoredProcedure @Parameter1", parametersList);. SomeDataObject is a class defined to match the result set shape coming back from StoredProcedure. No WITH RESULT SETS clause used.
WITH RESULT SETS would help enforce that from the database side, I suppose, and I think EF wouldn't have a problem if that clause was added in this raw SQL. All the same to it.
@ErikDarling True. If I recall correctly, they were having performance issues by using the same type for everything, NVARCHAR(4000) or NVARCHAR(MAX) even and refused to accept that that was part of the problem / unwilling to listen to reason to address it.
@J.D. But more to my point, writing EXEC dbo.Proc with parameters is just hard work and error-prone. If you could associate a proc with an entity, you could write much more readable and concise code.
There's other ways to call procedures in EF Core too. That's just what seems commonplace at the shoppe here. I honestly haven't touched EF Core in over a year now, so I'm fuzzy on how to utilize it.
Back in the day, I recall LINQ2SQL would map procedures to actual methods in your data context. So you can call them just the same as a C# method, syntactically.
Tough job in either direction I suppose. But it does irk me it seems like some of the solutions the EF team implement aren't even put in front of someone with actual SQL experience to give their 2 cents on before going live.
For example, the discussion on that IN list problem (github.com/dotnet/efcore/issues/13617) has plenty of input from developers with some SQL experience
@PaulWhite Word, perhaps that's a global problem in the industry too. I find it pretty scary the direction software is going. Things should be getting easier, and I feel like they're just getting more complex - at the sake of being new and cool.
And, in fairness, passing JSON to a stored procedure (albeit temporary and unnamed) is the kind of solution we've been using for a decade
Because SQL Server doesn't allow you to pass tables into and out of modules nicely
And someone with practical experience would know the potential issues with that solution, and how best to work around them, given the features we do have
The other point about modules being you can change the implementation - and use more than one statement - without breaking the contract with the consumer
I feel like I've had this chat with Darneli before and he told me I was reinventing LINQ
@PaulWhite That's an interesting thought! EF already has the config data about what types the different properties on an entity map to. So it could totally generate that clause. Extension methods could even be added to provide a projection (if the proc doesn't return all the properties of a given entity.
The funny thing is that benchmark is probably sufficient for MANY shops using EF. But, it's totally unfair to the folks working with bigger datasets.
At my last job, we had a customer with an app that was 24/7, mission critical for their business, dealt with time-sensitive medical outcomes, and thus needed HA and DR. The biggest table in the database had about 60K rows after being in production for several years.
An ORM worked great for this app, because even bad ORM-generated queries didn't cause all that much trouble for SQL Server when there's that much data, and most all of it sat in RAM all the time.
Which I think speaks against all the people saying EF can only be used on "toy apps and databases." This was a very important app for the customer, and had a lot of complex functionality. Just the nature of the business doesn't generate all that much data.
@PaulWhite Haha yeah, I guess it depends on what sense you mean toy 😁 But I think most folks mean it to say "you can't us EF for important projects." And I just don't think that's true.
To play Mr D. Advocate for a moment, if you're using an example where all the data is in memory and the queries don't matter to defend (apologise) for a framework...
Which is to say, any method would be fine, and EF is one of all possible methods
I don't know that that is a particularly strong statement
@PaulWhite I think it's kind of the standard method (not just one of all possible mehtods), for modern devs using .NET Core / C#, generally speaking. Probably based on ease of use and number of features available compared to alternative implementations for those devs.
Sure there's other ORMs, like Dapper, which arguably trade minor performance improvements at the expense of less features and arguably more dev work to implement. Or going back to frameworks like ADO.NET which again, isn't as user friendly for non-SQL devs.
@J.D. For the particular people you are talking about, sure. Is that a majority in the world, or just a majority you have encountered? How would we know
@Zikato tbh I think it's just one guy who multitasks extremely well
My point is, I'm not necessarily defending EF Core as the best solution or my favorite even (personally I liked LINQ2SQL ORM more back when I was a noob). But I'm pretty confident it's leading in popularity among modern devs working in the Microsoft stack because it's more robust and easier to pickup vs alternative options within the same stack.
All other things essentially being equal as far as generating crappy queries occassionally.
Last time I ordered a new dryer, it was through Lowes, and it took them literally 3 months to deliver it. They kept re-scheduling and losing track of it's location lol.
yah, they self-immolated the original after I closed it.
not to worry though, I undeleted the original and closed this as a dupe of it.
so now we have two terrible questions instead of none. But luckily they'll get removed by roomba in the next few days unless the OP fixes the original.
@PaulWhite If I were going to respond to Mr. D's advocate's point (which is fair), I would say that the advantage of EF in a scenario like that (everything is in memory, etc.) is that it's going to be much faster to develop (compared to using ADO.NET and stored procedures, for example).
I imagine you're joking, but just to laboriously clarify: my point is that it's not a pile of rubble (despite all of our usual state of default ORM hate haha). It works great in my experience for scenarios like the one I described, and I don't think the scenario is all that uncommon.
