Signs It’s Time To Switch From ORMs To Stored Procedures In SQL Server Development

Posted on by Erik Darling

Meet Wall

There are, unfortunately, some times when developers refuse to put the ORM down.

I mean, it’s mostly unfortunate for them, because they’ll continue to have performance problems.

Me? I’ll be okay.

The items in this post are issues I’ve run into constantly when working with people who use ORMs, but don’t spend any time looking at the queries they generate.

I expect this list to grow. Heck, maybe it’ll even get some good comments that I can add to the list.

I know, I know. Good comments.

Unreasonable

Here’s the stuff I see quite frequently causing issues with ORM code, in no particular order, and they’re quite often signs that you’d be better off with a stored procedure.

1. Your select list doesn’t fit in the cached plan

Developers using ORMs often have to learn the hard way that when they poke an object, all the columns come out. Not only does the lesson seem hard to learn, the behavior seems hard to change. I’ve worked with people months and years apart and found the same bad habits over and over again, and this isn’t an exception. Not only can this hurt query performance for a number of reasons, but it also makes reproducing any issues really difficult because you can’t get the full query text easily.

2. Your queries generates long IN lists

It’s bad enough that most ORMs don’t deal gracefully with  this by parameterizing the IN clause values. Even if you do parameterize them all, SQL Server might have different ideas about how best to apply that IN clause. Scroll down to the “Complexity” section in this blog post. You can get wildly different plans depending on how many search arguments you pass in. This is one of those times where a table valued parameter, temp table, or other materialization of the list is a way better idea.

3. You don’t understand the query it generates

Because you-know-who, did you-know-what, with you-know-who, but let’s keep that between me and you isn’t a good way to send queries to SQL Server. The number of far overly-complicated queries that generate unrecognizable logic that I’ve seen have, at this point, probably generated physical weight in the world beyond mere electrons. The 20 lines of code you wrote to explain what you want have turned into a 200 line query full of derived left joins to every single imaginable relation in the database. Why? I don’t know. You don’t know either.

4. You can’t get a good query plan

You see that paragraph up there? That’s why you can’t get a good query plan. The optimizer spent a reasonable amount of time assessing all of the crazy semantic needs of your query and came up with a reasonable plan as quickly as possible. But somewhere along the line, it misjudged something, or didn’t have time to explore that one last join reordering that would have made everything okay. Or maybe, like a query that generates a long IN clause, this monster would benefit from breaking the million-layer-dip of logic up by dumping some initial results into a #temp table.

5. You can’t get developers to fully parameterize queries

When you write queries that take parameters, whether it’s a stored procedure or dynamic SQL, you get better plan re-use. When you throw literal values into the mix, the optimizer is far less charitable, and will treat each query like it has never seen it before and go about compiling a brand new execution plan for it. Even if you turn on Forced Parameterization, your semi-parameterized queries won’t be parameterized. And of course, Optimize For Ad Hoc Workloads won’t help once the plan moves beyond stub-status.

Move On

There are many ways to tune a query, but unfortunately a good portion of them are unavailable while using ORMs in their natural code-only state. Sure, you can write custom queries in the code, but that has a lot of potential downsides, too. Depending on how the code is constructed, and if parameters are strongly typed, you may not get consistent plan re-use.

I’m all for application developers using tooling that enables them to work on new features quickly and in a way that they’re comfortable with. But at some point, SQL developers or DBAs need to step in and enforce coding standards. At some point, mom and dad have to the keys away and implement something that performs beyond just what “works”.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts

20 thoughts on “Signs It’s Time To Switch From ORMs To Stored Procedures In SQL Server Development”

  1. Is there any difference if we executed plain sql in code (e.g. with Dapper) vs putting them in SP (performance wise) as I really like to keep my sql in my code and in my source control ?.

    2. This seems to present a false dichotomy between “queries being in a stored procedure” and “queries being in source control”. To quote a commercial for taco shells from the 80’s – why can’t you have both? Source control the procs.

  2. I ran into an interesting variation on #2, where the ORM (nHibernate in this case) was parameterizing the IN clause, but there were so many items it was adding to that IN clause, that it ran into SQL Server’s 2100 parameter limit. This was one of those cases where it was re-written as a stored proc using a table valued parameter.

  3. Ooh, they go full-implicit sometimes and just don’t bother closing transactions. I’ve summoned Mr. Gosh Darnit a lot because of this, so he’s not pleased now as well as me with nHibernate.
    Great crosslinking btw. Someday it may become encyclopedia of ORM pains or even 2xBritannica size work (like Erland Sommarskog do bloggin’).

    1. Heh heh, I was just dealing with that yesterday with a client. Everyone thought SQL Server was the Worst Thing On Earth™ and it turned out to be uncommitted transactions.

      I’m hoping so! There’s good SEO around ORM problems in databases ?

  4. I hadn’t seen your previous post on IN clauses until now, thanks for that.

    I think whether you’re using an ORM or any other application code-based client access, the basic problem (the need for a parameter that’s basically a list, length n, of instances of a given thing) is one that needs more love and attention from Microsoft. We have TVPs, but they’re not marketed well and not exactly straightforward to use either.

    The end result is that you end up seeing procedures that take a stringly-typed parameter that’s expected to be populated with a delimited list, with T-SQL that then shreds that out and (hopefully) dedupes and does some quality checks on it, ultimately providing some sort of table-shaped structure that can actually be used for the purposes of the query. Using a XML or a string containing a JSON array is just polishing that turd.

    It should be possible to declare a parameter (or variable) of type “list of ints”. Or “list of datetime2(0)s”. Or “list of “, and have that parameter immediately available as either some sort of tabular structure or an IN list (subject to the plan caching problem that you mention in your other article needing to be fixed). Programming languages are capable of doing this kind of stuff, there’s no excuse for T-SQL to not join the 21st century too. Instead we get stuff like STRING_SPLIT which just makes it slightly easier to implement the turd design pattern.

    1. For context, my third “list of” was something along the lines of “list of any existing data type but if you go using sql_variant the angels will cry”, but it had angle brackets in it that apparently said “eat me!” to the blogging software.

      Gobble gobble.

    2. With the caveat of “this is probably a terrible idea”, you can reference temp tables created outside the body of a stored procedure.

      USE tempdb;
      GO
      CREATE OR ALTER PROCEDURE dbo.readTempTable
      AS
      BEGIN
      SET NOCOUNT ON;
      SELECT *
      FROM #tmp;
      END
      GO
      DROP TABLE IF EXISTS #tmp;
      CREATE TABLE #tmp (a INT);
      INSERT INTO #tmp VALUES (1), (2), (3);
      EXEC dbo.readTempTable;

      1. Yep, it’s certainly something that can be done and works outside the context of sprocs too (create temp table, populate, use in query) but is another programmatic nightmare. Just being able to provide a basic list of ints as a single parameter would be something I’d expect if I was paying SQL Server licensing dollars. You can do it with TVPs, but the first step there is “create user-defined type as a database object, which is just…wat

        1. Eh… needing to create the TVP yourself doesn’t bother me at all. You do it exactly once per db (per DT that you need a list of, I suppose) and use it forever.

  5. Yeah, it’s not the worst thing in the world but I’ve never met a developer who’s already heard of them and it’s such a basic requirement that I feel it should be supported natively. SQL doesn’t have to be the clunky archaic beast that it is, and developing features that people need and are already working around in various ways is just sensible, it doesn’t open the floodgates to all the business logic ending up in the database (those gates will have already been forced open). Devs would love it, DBAs would love it and it’d bridge that gap between them too. The approach you’ve mentioned (once per DB per data type) is the least worst workaround I’ve ever thought of (and have pseudo-implemented before); the problem is if you explain that to a developer they’ll rightly laugh at how ridiculous SQL is. It’d be nice to be able to just declare a parameter of the right type and move on with the day like app developers can.

  6. I was just having a classic “no you *finger point*” conversation with a lead app dev regarding this very topic. IDK if he’ll read it but im def passing it along 😀

  7. Pingback: The Limitations of ORMs – Curated SQL

  8. A common reason I see for IN parameterization issues is simply because the devs are trying to do a JOIN in the code. Get IDs from one query, then pass then on to another query. In many cases they don’t even realize they can do the join in one EF query.

    Anyway, as much as I’m a fan of a well-tuned SP, you’ve got to admit that for OLTP systems 99% of the queries EF generates are better than what the average dev would write (well, given that the EF model is in sync with the DB schema’s data types, at least).

  9. Pingback: Shop Talk: 2021-10-11 – Shop Talk with TriPASS

Comments are closed.