• Post Reply Bookmark Topic Watch Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Devaka Cooray
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
  • paul wheaton
  • Jeanne Boyarsky
  • Tim Cooke
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Tim Moores
  • Mikalai Zaikin
  • Carey Brown

Database: Why Stored Procedures are Bad

Saloon Keeper
Posts: 27491
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why avoid stored procedures? Here are some reasons:

1. Stored procedures are generally written in some proprietary language such as PL/SQL and even when not (for example, Java stored procedures) are likely to use proprietary API's. That locks your apps into one vendor's database. If Amazon can move their business to a different database vendor, so might yours.

2. Stored procedures risk fracturing business logic. If part of the business logic is in the app and part in the database (stored procedure), it can be a "treasure hunt" to find what's being done where when maintaining systems.

3. Stored procedures run on the database server machine. Typically a database server will have many application client machines, which allows distributing the workload. But if expensive logic can only run on the database server, then it can over-burden the database server and potentially slow down all of the client machines as well.

4. Not all databases have version control. With application code you can do version control with a system such as git or Subversion.

So why use stored procedures at all?

1. If several different applications need complex logic then it's better to maintain that code in one place and not have to update it in multiple places. It's still better to share a code library, but code libraries are limited in that typically they are bound to a particular programming language, which may be a problem when apps aren't all in the same language.

2. Sometimes you can get much better performance if you do processing on the database server instead of sending tons of data to the application and having the application send tons of data back to the server. However, always keep item 3 from the "why not" section in mind!
We can fix it! We just need some baling wire, some WD-40, a bit of duct tape and this tiny ad:
a bit of art, as a gift, the permaculture playing cards
    Bookmark Topic Watch Topic
  • New Topic