Ask HN: Are database stored procedures overkill?

by UpdatedFolders on 9/30/2020, 5:50:01 PM with 1 comments
My manager is telling me to use stored procedures (in context of SQLServer) for the reasons of security (using active directory security groups), faster performance, and easier maintenance. His experience from what I understand is working in a windows dev shop from a while ago.

Arguing against his points, I see it as 1) You can control access to pages or functionality application side 2) Increased performance gains is negligible for our apps as we don't have much traffic. 3) Maintenance may be debatable. Have to now source control database code.

Typically, I have a wrapper around an odbc library that connects to our database instance. I define my SQL queries in the codebase and use prepared statements.

I frankly was a little mad when he questioned my lack of stored procedure usage, but not my call if he wants me to do it this way.

Anyways, I'm looking to start a discussion on this as I like insight from fellow developers on how they've used stored procedures in their career.

  • by PaulHoule on 9/30/2020, 6:05:20 PM

    Today many devs build a "service layer" that exposes an API that covers up the database. Somebody might write that in Java, Node.js, .NET, etc. or they could implement it in stored procedures.

    If a transaction BEGINs and ENDs in the stored proc, you eliminate a huge amount of waiting time in which a tx could conflict with another tx. A tx inside the database can't possibly mutate some global state which makes it no longer be a tx. Presumably some SQL expert writes the stored proc and they've got a better chance of getting the details right, etc.

    There are toolkits for managing stored procedures in version control and stored procedures can be highly productive for coding certain things. I've worked on Microsoft SQL Server projects that used lots of stored procs and thought we were doing OK.