Repeat It We Must
If you have a small to medium sized application, and your developers are highly skilled not only in their preferred method of development, and you have someone who can manage SQL Server performance well (think settings, indexes, etc.) then you might be able to get away with having zero stored procedures.
This post is for everyone else, and by “everyone else” I mean “99% of software shops” out there.
You are seen. You are seen by me.
One Eye Up
The problems you’ll run into eventually will come from a few places, in general:
- SQL Server has some wonky setup issues (this is still surprisingly common)
- No one is managing the indexing (and I don’t mean maintenance, here)
- Developer naïveté about what the generated queries look like (text and plan complexity)
Certain PaaS offerings, and software shops who offload certain tasks to customer DBA teams will partially be in the clear, here. But more often than not, DBAs are accidental and not much more confident than the average sysadmin when it comes down to digging into performance issues.
DBAs skilled in the art of performance tuning will be happy to tweak indexes where possible, but a lot of their feedback to you as a vendor might be a wishlist of things that they could do if the code were in a stored procedure where more traditional tuning techniques are available.
Matter Of Years
I’ve helped more than a few vendors dig their way out of ORM hell now, and the process isn’t so bad once you understand the logic involved to get the right results back.
Once you’re writing stored procedures, you have a whole world of possibilities when it comes to adjusting the code to better manage performance. For critical applications, this often becomes a necessity once a database gets big or busy enough.
I totally understand the appeal of ORMs for many development tasks and priorities, but once you run out of tricks to get it working faster, you need to learn how to let go.
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.
Yep! Just helped out a client last month with the same issue. I could only tune indexes for how the ORM mangled the generated query so much. I took the ORM generated query and advised on how it should be written to be performant. They took my modified query to replicate it in the ORM code instead of creating a stored procedure. The pushback I got on stored procedures was they are not testable. I directed them to tSQLt to no avail. 🙁
I get it, tSQLt isn’t very easy to use. But you know… You can’t save people from every bad choice.