PHP and Databases: Views, Functions and Stored Procedures performance

Question

I'm working on a PHP web application with PostgreSQL. All of the SQL queries are being called from the PHP code. I've seen no Views, Functions or Stored Procedures. From what I've learned, it's always better to use these database subroutines since they are stored in the database with the advantages:

• Encapsulation
• Abstraction
• Access rights (limited to DB Admin) and responsibility
• Avoid compilation

I think I read something about performance improvements too. I really don't see why the team hasn't used these yet. In this particular case, I would like to know, from experience, is there any good reason to NOT use them?

Mostly when there are so many "SELECT" queries along the code, why not use Views?

I am planning on refactoring the code and start coding the subroutines on the DB Server. I would like to know opinions in favor or against this. The project is rather big (many tables), and expects lots of data to be stored. The amount of data you would have in a social network with some more stuff in it, so yeah, pretty big.

Answer 1

I. Views offer encapsulation, but if not carefully designed they can slow down the application. Use with caution.
II. Use functions if needed, no reason to put them in if they are unneeded.
III. Stored Procedures are a godsend, use them everywhere there is a static query!!

In response to the views vs. queries, try to use views with Stored Procedure's, the Stored Procedure's will mitigate some of the performance hit taken with most views.

Answer 2

In my opinion, views and stored procedures are usually just extra trouble with little benefit.

I have written and worked with a bunch of different web apps, though none with bazillions of users. The ones with stored procedures are awkward. The ones with ad-hoc SQL queries are plenty fast (use placeholders and other best practices to avoid SQL injection). My favorite use database abstraction (ORM) so your code deals with PHP classes and objects rather than directly with the database. I have increasingly been turning to the symfony framework for that.

Also: in general you should not optimize for performance prematurely. Optimize for good fast development now (no stored procedures). After it's working, benchmark your app, find the bottlenecks, and optimize them. You just waste time and make complexity when you try to optimize from the start.

Answer 3

We try to use the features you mentioned only where there is a significant benefit

Being part of the "Database", they fall under "schema changes", rather than
"source code changes", and are naturally harder to version control.

Whatever you do, just make sure you retain full visibility of who-changed-what-when, so that you can diff, rollback, and recover in the case of problems.

Answer 4

The advantage of stored procedures is that, because all the processing is done on the database, you do not incur network overhead shunting intermediate result sets back and forth.

The disadvantage is that each RDBMS system out there has its own peculiar syntax for stored procedures. By implementing your business logic in stored procedures, you're pretty much restricting your app to a single database product, something you need to keep in mind if you intend your application to be database independent. Also, as gahooa pointed out, because stored procedures live in the database, your access to them as a developer may be restricted by local policy; some organisations will only let DBAs touch the database.

@WolfmanDragon: I don't know if views inherently make things slower; your mileage may vary, I guess, depending on the complexity of the view and the RDBMS you're using. Plus, some RDBMS allow you to materialise commonly-used views so access to them is as fast as a base table.