Please, I beg you, we need to stop using Stored Procedures (from applications)

2026-08-30 | programming databases Please, application developer, I beg you, stop allowing reliance on Stored Procedures to enter your codebase. Please, application developer, I beg you, learn some SQL! Please, database administrator, I beg you, stop using Stored Procedures as a cudgel to compensate for your application devs. Please, database administrator, I beg you, guide your devs when it comes to SQL, indexes, query plans, transactions, isolation levels, parameterized queries, parameter sniffing, sargability, profiling, bookmark lookups, schema design, normal forms, denormalization, cardinality, B(+)-trees, WAL, the holy trinity of CPU/Log IO/Data IO, and everything else my DBA has yet to teach me.11Or that I failed to mention... Also, I'm not incapable of learning on my own and I swear I do it. I just always learn better when a DBA tells me something 😅 - Me, for the last 5 years Alright, great, now that that's out of the way I can go back to calling them sprocs. Listen, we need to talk,22Or I need to talk at you I guess. You can respond at [email protected] I don't know if it's just a thing at my work — and in that case I guess I'm outing us a bit — but I'm seeing far too many (i.e. any at all) applications calling out to a sproc. Thankfully, this (suspiciously new) Reddit user mentioned it as well, so I must not be alone! I've felt this way for a while, but seeing this (plus recent events at work) spurred me to make a quick post going over it. My hope is to cement such an all-encompassing reasoning here in this post that there's no room for argument, only disagreement on the final judgement. Luckily, there really isn't much to it, honest. Forgive me for this being so SQL Server centric, but it doesn't change the thrust that if you own an application, you should do what you can to colocate your DB access logic with your app logic. What do you want out of your DBMS? When we make a request to a database there two major sproc benefits we care about replicating. The first is plan caching. When you make a database query, your DBMS will use the statistics of your data to calculate the optimal way to gather that data. This is an expensive process, so ideally this is a rare operation. The second is parameterized queries. Parameterized queries allow us to inform the DBMS of the types of our inputs, they let us separate the data from the logic. Conveniently, parameterized queries also help us reuse query plans from the plan cache. If I send SELECT 1 FROM my_table WHERE code = 'SUPER_COOL_GUY' and then I send SELECT 1 FROM my_table WHERE code = 'SUPER_UNCOOL_GUY', that's two separate query plans, that's the evil we call a dynamic query.33Typically, dynamic queries are seen when the structure of the query changes a bunch, but this counts! However, if I send: -- Hey, look, a stored procedure, well dammit... -- we can rely on this one, that's fine EXEC sp_executesql N'SELECT 1 FROM my_table WHERE code = @code', N'@code NVARCHAR(50)', @code = N'SUPER_MEH_GUY' Now we have a parameterized query where we can send anything in for code and it'll use the same query plan over and over, without the DBMS having to recalculate.44That is — as long as the parameter hasn't been Parameter Sensitive Plan sniffed and deemed in need of a separate query plan, but that'd happen with sprocs too. And is also super SQL Server specific. So what does a stored procedure get us? Absolutely nothing! Well, I mean, headache for one. But whether you execute your SQL as above or call into a sproc, your DBMS is going to treat it just the same. It will handle binding your inputs to your parameters, it will generate a query plan on first execution (yup, not precomputed for sprocs), and it will reuse the query plan on seeing the same query body. We get all the same benefits. What other fun side effects do we get from using a sproc? Well, our application code and DB access are separately versioned, our sprocs can change right under our feet from aberrant (i.e. extremely rare, insane) DBAs, we have to deploy migrations to update our queries, and we have to run diff migration_for_my_sproc migration_for_my_sproc_n to see how things changed. And don't even get me started on rollbacks, oh my goodness... when's the last time you initiated a database rollback after a deploy? For my team, never, we actually can't because it's too convoluted and dangerous so we haven't even introduced the means. On the other hand, if we own our queries — well, we get to own our queries, that's awesome! We get our database access lockstep versioned with our application logic. We get to take advantage of actual-version-control-without-having-a-file-that-just-holds-the-contents-of-a-sproc-that-we-try-to-remember-to-update-every-time-we-write-a-migration-to-deploy the-new-version-of-a-sproc-that-invariably-gets-out-of-sync-because-no-one-made-a-CI-check-to-see-if-the-migration-you-just-deployed-also-made-sure-to-update-the-redundant-file-you-track just-for-version-control... *heavy breathing* And also, we can rollback our application and our queries! After all that, I can only assume if you still want a stored procedure you just really want your DBAs to own your SQL for some reason (those reasons exist). Thank them for so kindly being on-call with you. How should app devs interact with their DBs? Yeah, that's right, your DB, have some pride, gall darn it.55Assuming you've opted into "banning" sprocs from your application at this point, otherwise put your pride where you will. Now that we've accepted responsibility for our database access, it's important we be upstanding coworkers and help lighten the load as much as possible for our DBAs. You won't avoid every mistake, DBs are hard, DBAs are smart. But some basics can get you far, I'll walk you through some of the big ones.66Some of this might be pretty obvious, but I've been seeing a lot of people saying obvious things are great things to write about :D Network Requests First and foremost, as an application dev you should know that network requests are our bane, you'll almost never do anything slower. Provided you're not on-prem, that DB... well, it ain't close, not even to your apps also running in the cloud. It's another network hop away, persistent connection be damned — at least it's probably in the same datacenter, probably. So we want to interact with our DB in as few requests as possible. Most DBs will have an option to return the values from the row you just updated or inserted. If not, do an upsert pattern bundled with a SELECT. I can't explain the ins-and-outs/hows-and-whys of every DB, but every DB will support you not making a bunch of separate requests to achieve your goals. Indexes Indexes, use them. If you're using a column in your query's predicate (the WHERE), you're gonna want an index on that column — most likely a composite index on the collection of columns. It'll be a cold day in hell the day you don't want an index on a predicate — obviously hyperbole, but very very often it's true. More specifically, indexes are more effective the greater the cardinality of a column. A boolean or bit has a cardinality of 2 (unless it's also nullable, then 3), so it can only really be separated into two "buckets". The reason you wouldn't want an index is because it impacts the speed of writing to your DB, but this is almost always so minuscule that it's not worth consideration until you're seeing issues. You shouldn't opt for the opposite, i.e. not indexing until you see issues. As always, profile, benchmark, analyze. Your DBMS should also support something called covering indexes (usually specified by INCLUDEs). These are a bit more situational in my experience, but if you need to speed up reads and don't have a ton of writes going on, you can include the data your queries request in the index. ORMs ORMs are... okay. They're good for quick little queries on sprouting applications. You should make sure you're not pulling full "Entities" if you don't need all the data, every ORM should have an option to select specific columns. This subset of columns is called a "projection". You should also always do your due diligence and view the exact queries your ORM is sending to the DB, they'll typically have an option to turn on query logging. ORMs can do surprisingly many queries in the background, for example: a findOne in TypeORM for SQL Server will do two separate SELECTs, a save will do a SELECT and then an INSERT or UPDATE. The infamous N + 1 (or more aptly, 1 + N antipattern) is something much less achievable with your average SQL query and only really a mistake you'll make relying on an ORM, with something like fetchTables().forEach((table) => fetchTableLegs(table)). Cartesian Explosions Last big one is 💥💥CARTESIAN EXPLOSIONS💥💥. Generally speaking, joining tables in SQL or through your ORM is going to be faster than separate SELECT statements for related rows. So: SELECT * FROM tables WHERE id = 3 SELECT * FROM table_legs WHERE table_id = 3 -- vs SELECT * FROM tables t INNER JOIN table_legs tl ON t.id = tl.table_id WHERE t.id = 3 Assuming the table has four table legs, we'd end up returning 4 rows. Now that INNER JOIN actually doesn't have the same functionality as the above two queries would. If that table has no legs it unfortunately wouldn't be returned from the query. So instead, we'd likely want to use a LEFT JOIN, provided we still want to report the table exists and is tragically legless. Let's say we also want to pull all coasters that sit on the table. This is where the dangers of a potential 💥💥CARTESIAN EXPLOSION💥💥 come in, e.g.: SELECT * FROM tables t LEFT JOIN table_legs tl ON t.id = tl.table_id LEFT JOIN table_coasters tc ON t.id = tc.table_id WHERE t.id = 3 Now assuming four legs and four coasters, we'd end up with 1 (T) X 4 (TL) X 4 (TC), 16 rows. And that's a 💥💥CARTESIAN EXPLOSION💥💥! If you're entering this territory, think about how