CODEWITHSUNDEEP
phpmysqlstored-proceduresstored-functions
neomech
neomech

Reputation: 303

Stored Functions in MySQL - Worth Doing?

When I first got into databases I was using SQL Server. I got into that originally with classic ASP. One of the big things we were told was that you saved a LOT of time in your SQL transactions if you used stored procedures, rather than working with the ASP SQL commands (doing it "in line", I suppose). So almost everything I ever wanted to do with the database I wrote a stored procedure for, and then called the stored procedure from my code.

Anyway, fast forward several years and I'm now doing all of my work with PHP and MySQL (and a little Python). One thing that I don't seem to see much of at all is people using stored procedures/functions, so I haven't really been worrying about it.

However, it occurs to me that I'm just doing it wrong and don't realize it. Is there a major advantage to using stored functions in MySQL? I'm building a fairly good-sized website that handles a lot of calls to the database. The calls are all done in-line with my PHP code. Would I be better off using stored functions for the calls that I'm making consistently, and then just passing variables to the function from the PHP?

Upvotes: 2

Views: 1543

Answers (2)

rerun
rerun

Reputation: 25505

Well that depends. Stored procedures are a way to handle functional decomposition and can be absolutely essential if you have multiple applications interacting with the same database. The idea of using stored procures for everything was on the ascendancy a couple of years ago and as the world has moved to a service/RAD world they are losing some ground.

Some of the benefits of stored procedures are

  1. Reuse / This can certainly be accomplished within your code base but it beats the hell out of writing the same query with 10 sub joins 15 times

  2. Security - when sp were the rage sql injection attacks were coming to the for front and one way to reduce your exposure is to offer parameterized sp that sanitize you input automatically for the most case

  3. Documentation by definition on really large databases table layout is not always sufficient to explain what you are storing and why and have SP sometimes gives you and those that come after you what was intended

  4. A defined interface.

I think that all of these pros can be provided assuming good application design and only make scene to a degree on projects of some scale.

Some cons

  1. Redundant functionality - I have seen a lot of shops where business and crud logic is spread in the application and business logic is in the database.

  2. Lack of configuration management on SPs -- While there are established procedures and tools for code SP management lags far behind.

Upvotes: 4

Scott Bruns
Scott Bruns

Reputation: 1981

This is the same question as asking, "Should I break up my code into methods/functions/procedures and call them or should I code everything into my current function?"

Some of the advantages that stored procedures give you:

  1. Easier testing. You can test the stored procedures without running the app.
  2. Easier development. You can have a DB developer write the stored procedures and a GUI developer write the UI, etc.
  3. Easier porting to a different database. The changes are all contained in the database and the contract with the app (The parameters passed to the stored procedures) should not change.
  4. The ability to use the logic in the stored procedure from multiple front ends. You do not have to code the same customer create logic in every app that needs to create a new customer.

The big downside is that you have to learn multiple diciplines and probably use multiple tools. This is one of the big reasons for using Linq for SQL in .Net. You do not have to learn SQL and everything is contained in the .Net code.

We use stored procedures for everything. It works exceptionally well. Abstraction is your friend.

Upvotes: 0

Related Questions