Using Stored Procedures for Simple Select Queries

[eluser]Xeoncross[/eluser]
I am just looking into using Stored procedures/functions on the database side rather than sending queries from PHP in CI. One of the things that I was told is that each time you send a query it is checked and validated on the DB side before it is run. On the other hand, Stored procedures do not have this problem as they are already compiled on the DB side - kind of like prepared statements.

So here is my thought, every site has at least one query like this:

Code:

SELECT * FROM `posts` WHERE id = 23

While I am not a big fan of building the complex business logic into the DB - would making a stored procedure for this simple statement (and others like it) and then calling "CALL get_posts(id)" really be a good idea? I know it would save bandwidth between the DB server and webserver, but is the speed also worth it?

Code:

DELIMITER //
CREATE PROCEDURE get_posts(id)
   BEGIN
   SELECT * FROM `posts` WHERE id = 23;
   END //
DELIMITER ;

Then each of the 100,000 page requests would:

Code:

CALL get_posts(id);
//Instead of
SELECT * FROM `posts` WHERE id = 23;

If you want more information you can look here, here, or here.

[eluser]bretticus[/eluser]
Well, I have used stored procedure a lot with other platforms in the past but never with MySQL. Thus, I am not the expert here. However, if I were you, I'd do some loop tests. That is, perhaps run your query once and compare it with one stored procedure call with the same data. Then I'd do perhaps 1000 of each and compare the time. Then I'd do 1000 again but this time I'd pass a different parameter (perhaps 1 through 1000?) I'd be watching my resources on the web server and the database server also to see which version is more efficient.

Lastly, if you really wanna get optimized, I've had good results caching (relatively small) query results that are fairly static using APC (http://us3.php.net/apc)

[eluser]Aea[/eluser]
Query caching doesn't work for stored procedures, something to keep in mind.

[eluser]zutis[/eluser]
From a programmers view point (mine really) I would say that the improvement would have to be quite significant to make it worth while. I only say this because if you had a sizeable application, for me, stored procedures would increase my dev time and probably would make harder for others to follow the code - having to jump in and out of PHP code and MySQL.

Just an opinion though ...

[eluser]jedd[/eluser]
I used to think that stored procs were only really useful to people who were bad at designing schemas and even worse at writing SQL queries.

These days I'm a bit more relaxed. I now include the group 'anal retentives' - which applies nicely to banks (for example) where they used stored procedures as the sole interface to their database in the name of security. The library of stored procs becomes the API, as it were. I'm not sold on this idea entirely, as it suggests they employ programmers they don't entirely trust (yet they ask me to trust my money with them) and it seems a weak way of providing security. But, then, on the other hand, I'm not a programmer. Not really.

More helpfully in this instance - unless you are hitting some *serious* performance issues, and you believe the problem lies between the web server and the database server and you are too lazy to optimise your queries OR you are writing as part of a huge team of people using different languages (PHP, ASP, COBOL, etc) ... then you probably don't need to go down the path of stored procs.

As has been hinted by others, tracking what your various procedures do would be an unnecessary complication (at best) - especially if your documentation tools (eg. phpdoc) aren't happy to dive into your database to look for, and analyse, them. Relocating (restoring, deploying) databases is a bit of a pain at the best of times, with MySQL, but even more so with stored procs. IIRC they are not stored within the DB, but within the meta-DB or the MySQL DB. If you want some more reasons, give me the nod and I'll think up some more.

[eluser]Xeoncross[/eluser]

However, if I were you, I’d do some loop tests.

I guess that is the only way to tell. Opinions really don't count for much when it comes to performance - only facts.

Query caching doesn’t work for stored procedures, something to keep in mind.

Yes, I would use files or memcache for actual results caching.

I would say that the improvement would have to be quite significant to make it worth while... harder for others to follow the code - having to jump in and out of PHP code and MySQL.

Yes, not being able to see the procedures with a simple edit file like PHP would cause problems. I also have read that there is no way to easily debug them - they either work or they don't.

More helpfully in this instance - unless you are hitting some *serious* performance issues... then you probably don’t need to go down the path of stored procs.

The fact that I don't see much use of them in any Open Source projects seem to suggest that is true. However, I don't want to miss a great performance gain just because others aren't aware of something.

I am hesitant to use procs for must of the complex queries I write because the number and types of queries created depends on so many different factors at the time of creation. Therefore, writing out all possible SQL queries would probably result in over 60+ DB functions. Clearly a bad idea to maintain.

However, as I stated above there is always some really simple query that is run on every page on a site that it might make it worth creating just 2 or 3 small procs to save speed for those.

I guess I just have to try and see what the statistics say.

[eluser]jedd[/eluser]
It's possible that all other free software projects have overlooked this feature, but that seems unlikely. If you do go down this path take care to find out how to backup your procedures, as I do recall it is non-intuitive (compared to dumping your DB proper).

However, as I stated above there is always some really simple query that is run on every page on a site that it might make it worth creating just 2 or 3 small procs to save speed for those.

As I understand it, your expectation here is that the time taken to send the string 'SELECT blah FROM tablename WHERE field = $value' across the network is significantly different from sending 'CALL foo ($value)' across that same network, yes?

I think on a host that runs both your web and database servers, you'd be hard pushed to identify any delta (I could be wrong, though).

I think in a situation where web and database servers are separated by an ethernet cable, that'd it'd be almost as hard. Consider the latency of CSMA/CD, the size of TCP (minimum of 43 bytes, IIRC, and up to 1500 in a single packet), and the delays down and up the respective IP stacks - none of these bottlenecks are really going to be affected by reducing your payload from 60 bytes down to 30 - that is, staying within a single packet. Consider the results of doing pings across your network, using packet sizes ranging in these values. Again, of course, I could be completely wrong - theory is one thing, but reality often disappoints! - and so I'd be fascinated to see the results from your tests.

[eluser]Nguyen The Nguyen[/eluser]
Do CI have a tool to auto build stored procedure ?