Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What are the advantages and disadvantages of using MySQL stored procedures?
There are numerous advantages and disadvantages of using MySQL stored procedures which are as follows −
MySQL Stored Procedure Advantages
Followings are the advantages of using MySQL Stored Procedures −
- Increasing the performance of applications − As we know that after creating the stored procedure it is compiled and stored in the database. But MySQL implements stored procedures slightly different which helps in increasing the performance of the applications. MySQL stored procedures are compiled on demand. After compiling a stored procedure, MySQL puts it into a cache. And MySQL maintains its own stored procedure cache for every single connection. If an application uses a stored procedure multiple times in a single connection, the compiled version is used; otherwise, the stored procedure works like a query.
- Fast − MySQL Stored procedures are fast because the MySQL server takes some advantage of caching. Another reason for its speed is that it makes the reduction in network traffic. Suppose, if we have a repetitive task that requires checking, looping, multiple statements, and no user interaction, does it with a single call to a procedure that's stored on the server.
- Portable − MySQL Stored procedures are portable because when we write our stored procedure in SQL, we know that it will run on every platform that MySQL runs on, without obliging us to install an additional runtime-environment package or set permissions for program execution in the operating system.
- Reusable and transparent − Stored procedures expose the database interface to all applications so that developers don’t have to develop functions that are already supported in stored procedures. Hence, we can say that MySQL stored procedures are reusable and transparent.
- Secure − MySQL stored procedures are secure because the database administrator can grant appropriate permissions to applications that access stored procedures in the database without giving any permissions on the underlying database tables.
MySQL Stored Procedure Disadvantages
Followings are the advantages of using MySQL Stored Procedures −
- Memory usage increased − If we use many stored procedures, the memory usage of every connection that is using those stored procedures will increase substantially.
- Restricted for complex business logic − Actually, stored procedure’s constructs are not designed for developing complex and flexible business logic.
- Difficult to debug − It is difficult to debug stored procedures. Only a few database management systems allow you to debug stored procedures. Unfortunately, MySQL does not provide facilities for debugging stored procedures.
- Difficult to maintain − It is not easy to develop and maintain stored procedures. Developing and maintaining stored procedures are often required a specialized skill set that not all application developers possess. This may lead to problems in both application development and maintenance phases.
Updated on: 22-Jun-2020
2K+ Views
Advertisements