@izzion said in A critical look at Marvel vs. Capcom....:
@Groaner said in A critical look at Marvel vs. Capcom....:
Can this magical ORM do table/index/query hints?
In general, no, and if you're working with things that are such a mess that they need those hints either (1) your database people need to get off their asses and fix the indexing so the query optimizer can build decent plans to begin with
The DB people may have optimized the schema to be highly efficient with certain kinds of operations. However, there are almost always tradeoffs and you can't optimize for every type of access. Such is life.
I'll give you a real-life example: once upon a time, I helped build a survey app from the ground up. Each Survey would have one or more Sections, each of which would have one or more Questions. Sounds simple, right? Well, over the next few months, after this schema was built, you got the following additional requirements:
- Our users are stupid and don't appreciate that deletes are forever, so instead of deleting surveys, sections, or questions, can we add a soft delete flag to all of them?
- Also, only certain users should be able to see certain questions based on their assigned user type(s).
- But admins and super-admins should see all the questions in Admin Mode™.
- Oh yeah, can we make it so users only see certain questions if they answered a previous question a certain way?
- Can we make it so that users only see certain questions if they answered several previous questions in certain ways?
- Can we change what questions a user sees based on their answers in other surveys?
- Can we hide certain questions for users at Institution X and only show others at Institutions Y and Z?
Imagine what a query would look like to retrieve all the questions visible to a user, or even question n+1 if the user is currently viewing question n. If you're imagining correctly, it would be about a page tall and very complicated, no matter whether you do it in SQL, LINQ or whatever. The complexity is not indicative of poor design, it's indicative of reality being complicated.
And while you might be able to optimize such complex logic for that particular case, when you're asked to do a different complex operation (like come up with pie charts of response distribution to each question), there's no guarantee it'll automatically be performant with the right haggisindexing.
or (2) you're doing reporting that is sufficiently complex that you should use a purpose built tool (e.g. SSIS or whatever the $$$ORAORAORA equivalent is).
The elders told me when I was a padawan never to rely on SSIS for anything more than simple bulk loads as:
- such integration tools are notoriously buggy, and
- ask someone who's built out their entire workflow in SQL Server 2000 DTS packages (and are thus stuck on SQL Server 2000) how well that's worked out for them
Does it know how to aggregate rows into a temp table when needed?
Generally the same as above. Though if you have a really grungy query that needs to do a lot of aggregation and temp table work, with the ORM solution, you have the option of explicitly pulling all of the data across to the application tier (read as: web server) and do the aggregation/calculation work there. Where, to paraphrase Brent Ozar, "you aren't paying $7,000 a core" to do that work, so it's probably more cost efficient to let the ORM pull everything down and crunch numbers in CLR code anyway!
That sounds like a good idea until you're retrieving millions of rows and pushing gigabytes over the wire for every page load.
Does it retain compiled execution plans and allow users to address parameter sniffing?
The SQL server will retain compiled execution plans for standard queries just as easily as it does for stored procedures (don't believe me? Install a SQL 2016+ dev box to play with somewhere, turn Query Store on, and throw a bunch of the same plain old query at it over and over again).
Yes, I know how sp_executesql
works.
I'll concede that if you do have a parameterized query that is susceptible to parameter sniffing, the ORM isn't going to ever really know about it or be able to do the stupid human stored procedure tricks that exist to break parameter sniffing. But (again, assuming modern SQL Server here, since that's my domain of expertise) you still have access to Query Store's tooling to strongly encourage the good plan, or you could look at tuning the query to actually fix the small plan / big plan problem or just opt into pulling everything down and doing the data processing that causes the parameter sniffing spills in the application tier's cheaper CPU/RAM.
Yes, you can do things in memory in the application side when you can fit things into memory. But to get to that point, you're often going to have to do some heavy lifting on the SQL side, and the conditions may be nontrivial.