Menu
I have a theoretical question.
I can't see any difference between declaring a function within a PHP file and creating a stored procedure in a database that does the same thing.
Why would I want to create a stored procedure to, for example, return a list of all the Cities for a specific Country, when I can do that with a PHP function to query the database and it will have the same result?
What are the benefits of using stored procedures in this case? Or which is better? To use functions in PHP or stored procedures within the database? And what are the differences between the two?
Thank you.
338
By grouping SQL statements, a stored procedure allows them to be executed with a single call. This minimizes the use of slow networks, reduces network traffic, and improves round-trip response time. OLTP applications, in particular, benefit because result set processing eliminates network bottlenecks.
Security: Stored procedures allow you to enhance the security of an application or a database by restricting the users from direct access to the table. Low network traffic: The server only passes the procedure name instead of the whole query, reducing network traffic.
A stored procedure provides an important layer of security between the user interface and the database. It supports security through data access controls because end users may enter or change data, but do not write procedures.
Some benefits include:
Maintainability: you can change the logic in the procedure without needing to edit app1, app2 and app3 calls.
Security/Access Control: it's easier to worry about who can call a predefined procedure than it is to control who can access which tables or which table rows.
Performance: if your app is not situated on the same server as your DB, and what you're doing involves multiple queries, using a procedure reduces the network overhead by involving a single call to the database, rather than as many calls as there are queries.
Performance (2): a procedure's query plan is typically cached, allowing you to reuse it again and again without needing to re-prepare it.
(In the case of your particular example, the benefits are admittedly nil.)
69
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
