• 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
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
  • Piet Souris
  • Himai Minh

Database: Why Stored Procedures are Bad

Saloon Keeper
Posts: 25475
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!
Those are the largest trousers in the world! Especially when next to this ad:
free, earth-friendly heat - a kickstarter for putting coin in your pocket while saving the earth
    Bookmark Topic Watch Topic
  • New Topic