Stored procedures are more effective and resource efficient than SQL queries sent to the relational database management system (RDBMS) by the application server. There are two reasons for that:
- a stored procedure saves the server from interpreting the query as it is pre-compiled
- a stored procedure uses less bandwidth as there is less information transferred between the server and the client
You should therefore utilize this RDBMS function as much as possible.
All recent RDBMS (SQL Server, MySQL, PostgreSQL, etc.) support stored procedures.
When should I use stored procedures? - To avoid sending complex queries to the SQL server, which are analyzed for syntax and then interpreted before being run. These steps use up a lot of resources. - To simplify the website’s code and thus improve maintainability. A website’s ecological footprint also includes the resources used to maintain and upgrade it.