Menu
I'm running a website for a jewellery wholesaler.
The prices on all their products are calculated using the current bullion metal fixes that are updated every night.
Currently, for the website, the calculations are worked out via a php include function, which works fine under current circumstances.
There are around 10,000 products, but the prices are calculated in real-time (ie when the web page is requested). The calculations are simple, but there are lots of them (Around 50+) and i'm worried that an increase in traffic may slow the current script down.
I'm redesigning the site and was wondering whether it would be beneficial to create a procedure in MySQL to do the calculations instead.
Is this likely to be faster that the current php script? Anyone know any good reading reference on using procedures?
547
MySQL is faster in scope of SQL query. PHP is faster in PHP code. If you make SQL query to find out SQRT() it should be definitely slower (unless PHP is broken) because MySQL parser and networking overhead.
Stored procedures promote bad development practices, in particular they require you to violate DRY (Don't Repeat Yourself), since you have to type out the list of fields in your database table half a dozen times or more at least. This is a massive pain if you need to add a single column to your database table.
Within a Stored Procedure you can write procedural code that controls the flow of execution. That includes if or else constructs, and error-handling code. A Stored Procedure helps improve performance when performing repetitive tasks because they are compiled the first time they are executed.
A stored procedure is a subroutine stored in the database catalog. Applications can call and execute the stored procedure. The CALL SQL statement is used to execute a stored procedure. Stored procedures can have IN , INOUT and OUT parameters, depending on the MySQL version.
Here's a benchmark with stored procedure vs php.
http://mtocker.livejournal.com/45222.html
The stored procedure was slower by 10x.
You might also want to look at this:
http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html
184
If the reason you are thinking about this is due to performance and scalability, then I would recommend continuing the calculation in PHP.
The reason for this is that regardless whether there is a performance penalty in your PHP, when you are scaling your web application it is generally much easier to move to multiple web servers than multiple database servers. It is therefore preferable to do more calculation in PHP and less in MySQL.
Other than the performance aspect, I still generally prefer avoiding stored procedures in favour of having the logic in the application because
These problems can of course all be solved, without too much difficulty, but it all adds to the complexity overhead.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
