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

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 andrew@heffree.dev 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 "bounded" your relations are. Presumably the number of legs are bounded, I couldn't find a Guinness world record for the table with the most legs, but surely it's like 100. On the other hand we could stack coasters to the moon, so that value is "unbounded", we should probably break it out into a separate SELECT so our main query can continue to pull in table values.77Yes, I realize using tables as my example is a teensy bit confusing when discussing databases...

Somewhat related, generally duplicating data onto rows like this isn't the end of the world, you sacrifice a little memory for speed. However, if you have a really heavy column, consider grabbing it separately.

Grab Bag (Dynamic Queries/TVPs/Conversions/Filtered Indexes)

Alright, lightning round.

Dynamic Queries

As mentioned above, a query plan is cached against the body of a query, the literal text of it. If you're conditionally adding different fields to SELECT on, JOINing additional tables, or modifying your predicate, you're generating new query plans. Your DBMS can likely support a small range of these, but if you start generating a lot of them, you will completely monopolize the database CPU.

TVPs

Leading in from above, if your query has a varying number of parameters in the predicate, e.g.:

SELECT *
FROM tables
WHERE id IN (@1, @2, @3)

-- and then 

SELECT *
FROM tables
WHERE id IN (@1, @2, @3, @4, @5)

Each query with N parameters will be a different query plan, TVPs can help you reuse the same query plan regardless of the number of parameters.

You'll need a type stored as part of your schema to allow passing in a TVP, e.g.:

CREATE TYPE dbo.IdList AS TABLE (id INT PRIMARY KEY)

Then consult your ORM or DB driver SDK documentation to see how to supply the TVP to a given (parameterized) query:

SELECT t.*
FROM tables t
INNER JOIN @TVP tvp ON tvp.id = t.id

-- or 

SELECT *
FROM tables t
WHERE t.id IN (SELECT tvp.id FROM @TVP tvp)

Multiple query plans aren't the end of the world, so this can be used as a last resort if you're seeing issues.

Type Conversions

One especially dangerous hiccup to call out when owning your queries is to make sure your inputs are correctly declaring themselves. Sometimes your ORM will incorrectly default types being passed to your parameterized queries, e.g.:

EXEC sp_executesql
N'SELECT 1 FROM my_table WHERE code = @code',
N'@code NVARCHAR(50)',
@code = N'SUPER_MEH_GUY'

If our code column is defined as a VARCHAR, NVARCHAR has type precedence, so you're going to get an implicit conversion for every row you check, converting each code to an NVARCHAR for comparison.

Filtered Indexes

Last little fun thing, filtered indexes. Deploying indexes can be an extremely heavy and time-consuming process, especially while the DB is under load. You can greatly reduce the amount of time it takes to build an index by narrowing the index to a subset of values. This is called a filtered index. Index creation is so time consuming largely due to actually writing the index, it's easy to check the values. If you don't need to query on every potential value of a column, consider a filtered index, e.g.:

CREATE NONCLUSTERED INDEX ix_tables_in_stock
ON tables (id)
WHERE in_stock = 1;

Great time to point out, to actually use this index you DO NOT want to parameterize the input. It is not variable, it does not need to be sanitized, you would hardcode it into the query:

SELECT *
FROM tables
WHERE in_stock = 1;

-- NOT

SELECT *
FROM tables
WHERE in_stock = @0;

The End

Okay, that's it. I figured if I was gonna rant a bit I should hand out some tools to get started. Hopefully, this is useful to you if you've felt the pain of managing sprocs. There are certainly uses for stored procedures, it should just be more of an exception than the ultimate form of supporting applications. Sometimes you'll have no choice for security reasons, that'll happen and it is what it is. But if you have the option, at the very least, please, don't fall for the "sprocs are more performant" line.

There's a lot more to learn; read query plans, profile, have fun, be safe!88I'm re-aware of Jeff Atwood's post from 2004(!), I'm sure there are more, but it's still happening!