How is the performance of a Stored Procedure? Is it worth using them instead of implementing a complex query in a PHP/MySQL call?
where as stored procedure is compiled when it is submitted for the first time & this compiled content is stored in something called procedure cache,for subsequent calls no compilation,just execution & hence better performance than query.
In MySQL or any other SQL server as MSSQL or Oracle, stored procedures increase dramatically the speed of the queries involved because this are already compiled.
Stored procedures are precompiled and optimised, which means that the query engine can execute them more rapidly. By contrast, queries in code must be parsed, compiled, and optimised at runtime.
A stored procedure is invoked as a function call instead of a SQL query. Stored procedures can have parameters for both passing values into the procedure and returning values from the call. Results can be returned as a result set, or as an OUT parameter cursor.
Stored procedures will give you a small performance boost, but mostly they are for doing tasks that are difficult or impossible to do with a simple query. Stored procedures are great for simplifying access to data for many different types of clients. Database administrators love them because they control how the database is used as opposed to leaving those details to the developer.
Look to indexes and proper table design to get better performance.
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