Stored Queries?

Question

Is it considered crazy to store common SQL queries for my web app in a database for use in execution? Or is that common practice? Or is it impossible?

My thinking is, this way, I avoid hard-coding SQL into my application files, and add another level of abstraction.

Is this crazy? Is this what a stored procedure is? Or is that something else?

---

EDIT: The below answers are useful as a background for 'stored procedures', but didn't answer my core question: Is a 'stored procedure' just when I have a database table that contains queries that can be called? ie, something like this

INDEX | NAME          | QUERY
1     | show_names    | "SELECT names.first, names.last FROM names;"
2     | show_5_cities | "SELECT cities.city FROM cities LIMIT 0,5;"
etc.

Or is there a more complicated mechanism that encompasses the concept of stored procedures? Is my example an actual example of something people do?

Answer 1

Well in my opinion you should definitly use stored procedures. And this is common practice!

Here are just two advantages of using stored procedures:

They will run in all environments, and there is no need to recreate the logic. Since they are on the database server, it makes no difference what application environment is used - the stored procedure remains consistent. If your setup involves different clients, different programming languages - the logic remains in one place. Web developers typically make less use of this feature, since the web server and database server are usually closely linked. However, in complex client-server setups, this is a big advantage. The clients are automatically always in sync with the procedure logic as soon as its been updated.

They can reduce network traffic. Complex, repetitive tasks may require getting results, applying some logic to them, and using this to get more results. If this only has to be done on the database server, there is no need to send result sets and new queries back and forth from application server to database server. Network traffic is a common bottleneck causing performance issues, and stored procedures can help reduce this. More often though, it is the database server itself that is the bottleneck, so this may not be much of an advantage.

Answer 2

The idea certainly has its appeal -- but the problems is, they are nearly impossible to scale.. I have never seen a scalable solution to maintaining stored procs (especially in MySQL) that has not made me shutter.

Since it seems you're heading the PHP/MySQL route, I'll give a few examples of my experience with stored procs in MySQL:

• They are generally far less readable and far more difficult to write than PHP.
• They make debugging a nightmare
  • Trying to figure out why changing a value in table_1 triggers a change in table_2 (if you're even lucky enough to recognize that this happens) is much more difficult to determine by looking through dozens of stored procedures than it is to, say, look in the Model that handles changes to table_1.
• To my knowledge there is no standardized & automated way to integrate stored procs / triggers / etc into any revision control system

Answer 3

A stored procedure is just one or more SQL statements that are "pre-compiled" and live inside the database. You call them to return one or more rows of data, or to update, insert, or delete data.

If you tell us what web framework and database you are using, we can give you actual examples of how to call a stored procedure, or at least point you to an article or two to get you going.

You could also consider using an ORM framework, such as Hibernate. This will allow you to get away from dealing with SQL code altogether. I am a .Net developer, so I'm not sure what is available to you on the PHP/MySQL platform, but I am sure there is a lot out there to choose from.

Answer 4

Along with MUG4N's great reasons on why to use stored procedures, here are three more:

Security

You can grant access to your application to execute stored procedures while denying direct table access.

Think defense in depth. If your app is cracked, then they will be limited to executing ONLY the procedures you have defined. This means things like 'drop table' would be explicitly disallowed, unless, of course, you have a procedure to do that.

Conversely, if your app is cracked and you allow the app to have full access to your sql server, then one of two things will happen. Either your data disappears and/or the cracker easily get's a copy.

---

Unit Testing. It's much easier to unit test your queries if you can hit them directly without having to go through the application itself.

---

In Flight Changes: If you need to modify a query AFTER you have published your site, it's much easier to just make a proc change than redeploy code that may have undergone other changes since the last deployment. For example, let's say you have a page that isn't performing all that well. After evaluation, you determine that just changing the joins on a query will fix this. Modify the proc and go.