stored procedures.ppt
TRANSCRIPT
-
advantages & drawbacks
A Study on
-
Advantages
Security
Better performance
Reduce network traffic
Usability
Encapsulation
Maintainability
-
1.Security
You can set up access controls on the stored procedure and that can allow one user to access the stored procedure, instead of directly to the tables affected. You could the precisely identify what they should and should not be able to do with the data. By passing your inserts through a stored procedure, rather than directly to an INSERT statement, you can catch SQL Injection attacks
-
Security
-
Improved perfomance.
They are parsed and optimized when they are first executed, the optimizer engine can compute an execution plan and and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
-
Using a stored procedure to optimize a complex self-join
-
Non parameterised SQL statements
-
Can reduce network traffic
An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network
-
Usability
A Procedure that is stored in the database and called by several different database applications
Database queries can be written once and re-used multiple times without writing the same SQL commands over and over again
-
Business Logic Encapsulation
The database can house a lot of business logic so that the brain of your application is kept neatly in one place.
-
Maintainability
Once it is validated, a stored procedure can be used with confidence in any number of applications. If its definition changes, only the procedure is affected, not the applications that call it. This simplifies maintenance and enhancement. Also, maintaining a procedure on the server is easier than maintaining copies on various client machines.
-
Drawbacks
Can make debugging more complex
Good debugging tools is one of the sad failings of all SQL platforms.
-
Drawbacks
Portability
Complex Stored Procedures that utilize complex, core functionality of the RDBMS used for their creation will not always port to upgraded versions of the same database. This is especially true if moving from one database type (Oracle) to another (MS SQL Server) or (MySQL) database system.
-
Drawbacks
Structure change will impact significantly
If there has been significant change (eg:- new field or indexe) to the tables or data referenced by the procedure, the precompiled plan may actually cause the procedure to perform slower. In this case, recompiling the procedure and forcing a new execution plan can improve performance.
-
Drawbacks
Finding prime numbers in various languages
Poor perfomance in arithmatic calculations
-
Thank You