When is it a good idea to use stored procedures?

I didn't see it with my own eyes, but our ex-CTO allegedly built a massive property search site for his previous company, and it only made one stored procedure call per pageview, returning every single bit of info current page needed in one go.

Haven't tried it in practice, but sounds like an interesting idea. Assuming that the "win" was because it takes time to connect from server to DB server, then return results, so saving a tiny bit of time for every single DB query, it added up.

As a developer the only time I have used stored procedures was when I spent 2 years working with Oracle tools doing Business Intelligence / Data warehousing. Since it was very DB focused using stored procedures made sense and was helpful.

The remainder of my career has being doing web development and I have never used stored procedures in this context since we want the business logic to be in the web application. Also, I would think having stored procedures would be a pain from a code repository and deployment point of view.

There's a lot of gray area beyond the obvious ones like bulk data loads and transformations. But basically you want the database server to do the things that it's been optimized to do, such as extracting subsets of data, sorting data and joining tables. The database engine has been written and tested to accomplish these tasks very well and the algorithms were written by a team of top notch programmers. In most cases, you shouldn't need to "reinvent the wheel" in your code.

On the opposite end, more complex business logic usually belongs in non-database code, such as in an API or service layer. One indicator of this is when you see a stored proc with a lot of cursors, loops, branches, string concats, calls out to multiple stored procs or functions and the like. This can and does work but it tends to create maintenance issues, especially when the original database savvy programmer leaves the company.

Absolutely use procs:

• for ETL (extract-transform-load data into the db) jobs
• for other database ops tasks
• with triggers

Consider using procs:

• any time you'd be shuttling a ton of data over the wire only to do something to it then put it back (eg cloning a relationship graph)
• to perform simple tasks that lend themselves well to SQL

Don't use procs:

• when you're worried about compatibility between different DBMSs
• when what you're doing is subject to change extremely quickly

I use them all the time to be honest. If I don't want the code visible, I'll call them from a package. We do everything we can to keep the logic out of the application code, if it isn't tiny or very specific to that application.

Use the utility when needed. There's no point limiting yourself for the 1 in 100 cases using something else might be better.

I like them, and I use them for things like working out latitudes and longitudes and great circle distances quickly. If you have more than one service which accesses the database and would be performing the same simple logic on those data then why not keep it in one place? Your database is initially generated from code, be it through fixtures, migrations or just a big old constructor. It's not like it's not going to be in your VCS.

On the other hand, I know they're not necessarily ideal in production depending on your situation. You have to be careful with stored procedures and triggers because they require separate permissions and your database host might not allow them.

For example, Acquia disallow them completely. They're the big Drupal host and contributor, but if you want to use packages which rely on triggers like CiviCRM with them you'll need to host the database elsewhere. That adds another layer of complexity to your infrastructure.

When I used the IIS/ASP/MS SQL Server stack on a daily basis I found it very convenient over time to massage anything DB related that should stick around in the code base into smaller fractions as stored procedures.

This improved overall performance and helped keep data and input validation reliable.

It can however cause some trouble when one needs to restructure the project or part of it, since it is common for these procedures to be less than general unless one actually took the time to refactor and break them down into small pieces at once when saving them.

So as a rule of thumb in big web applications I would recommend to do whatever works for fast development in testing but take the time needed to get from dynamic, interpreted and flexible to really tight and precise stored procedures once it is moved towards production.

In other types of applications it gets more muddled and depends more on the particular work the program needs to do and in what environment. Not all programs run in such hostile and weird environments as the open web, and some applications have no good use for stored procedures but still make heavy use of databases and information stores, perhaps backend stuff like REDIS as a link between automated and greedy data collection and something like a logic program KB, or more obscure systems like picolisp.com where DB, code and data are basically the same thing and integrates tightly with web GUI and an inference engine.

For me, it is right when it doesn't contain business logic. Or if speed is absolutely critical.

For example:

• Auditing an action or a data change
• Archiving data
• For cron-like tasks