Stored Procedures

System Design Masterclass · Database Fundamentals

Stored Procedures

Name: System Design Masterclass
Availability: InStock

Free preview: the first 2 parts of this 8-part lesson.

Already bought the course? Sign in to read the whole lesson.

this lesson is part of

System Design Masterclass

Unlock this lesson and the 751 other paid lessons

770 lessons, beginner to principal
106 real interview systems taken apart step by step
An AI tutor beside every lesson that answers and draws
Lifetime access, every new lesson included

$25 once, by PayPal

See the System Design course

The rest of this lesson

The One Capability That Is Not A Preference
The Plan A Reused Statement Remembers
What Error Handling Costs When Nothing Errors
What To Do
Two Reasons, One Cost, And A Third Option
Quick Check

read the rest of this lesson in

System Design Masterclass

Unlock this lesson and the 751 other paid lessons

770 lessons, beginner to principal
106 real interview systems taken apart step by step
An AI tutor beside every lesson that answers and draws
Lifetime access, every new lesson included

$25 once, by PayPal

See the System Design course

Continue this lesson

in Database Fundamentals

This lesson is part of the full System Design Masterclass. Unlock it along with 769 other lessons for a one-time payment.

One-time payment. Lifetime access. No subscription.

Global

$25/lifetime

One-time payment by card or PayPal

Get Lifetime Access

Foundation lessons free, try before you buy

India Special

India

₹1,111/lifetime

One-time payment: UPI, card or netbanking

Get Lifetime Access

Foundation lessons free, try before you buy

All 770 interactive lessons

Step-by-step animated diagrams

Runnable code in the lessons where code helps

Quizzes with instant feedback

Lifetime access, all future content included

18 Foundation lessons are free, no payment needed. Try them now

It is easy and good

“It is really nice and good Please join the course”

Gyan Kumar, Analyst, Bangalore · read 3 lessons before writing this · all reviews

Most Of This Argument Has No Numbers In It

Where business logic should live is a real question and it is decided by how a team works. Four narrower questions have answers that can be produced in an afternoon, and three of them are not the ones usually cited.

Two panels. What the argument is usually about: where logic should live, deployment and version control, and what a team is used to, all real and unmeasurable, settled by preference. What an afternoon can measure: the round trips it removes, the transaction it can end, and the plan it remembers, measured at 117 ms becoming 17 and 9 ms becoming 134. — ComparisonFigure 1. Separating the measurable half from the rest
Nothing in this lesson argues for or against putting logic in the database. It settles the parts that measurement can settle.

The Case That Is Actually About Procedures

A terminal capture of the stored procedures lab. Three thousand statements down one open connection take 117 ms, the same three thousand inside one CALL take 17 ms, and two hundred statements with a new connection each take 1,139 ms which scales to 17,085 per three thousand. A function attempting COMMIT raises invalid transaction termination, and the same job as a procedure committing per chunk takes 479 ms with zero rows left. A plan built per value costs 7 as an index only scan and one plan built for all values costs 17,101 as a parallel index only scan, and twelve calls in one session run at 0.073 ms falling to 0.014 without ever switching. A loop of 200,000 iterations takes 9 ms plain and 134 ms with an EXCEPTION block inside it. — terminal-figureFigure 2. Four questions, measured against a real PostgreSQL
The round trips are real round trips over a real socket, and the plans are read out of EXPLAIN instead of assumed.

A bar chart of milliseconds for 3,000 updates down one open connection. Sent one statement at a time it takes 117 ms. The same work inside one CALL takes 17 ms. — ChartFigure 3. Identical work in the database, seven times the elapsed time
The difference is three thousand messages that did not have to travel anywhere, on a local socket where a round trip is as cheap as it ever gets.

A hand-drawn figure with two panels joined by a line. A new connection each time: 17,085 ms per 3,000, 1,005 times the procedure, and nobody deploys this. A pooled connection: 117 ms per 3,000, 7 times the procedure, and this is what you have. A note reads that quoting the left number for the procedure's benefit compares it with an application nobody wrote. — SketchFigure 4. Two measured numbers, one of them a fair comparison
Every real application has a connection pool and has had one for twenty years. The honest ratio is seven times.

Benchmarks comparing a database feature against an unpooled client are common and they are measuring connection setup. Check what the baseline is before accepting any figure of that size.