Advantages and Disadvantages of Stored Procedure

Advantages of stored procedures:

the procedures/functions are stored in the database and are, therefore, executed on the database server which is likely to me more powerful than the clients which in turn means that stored procedures should run faster;
the code is stored in a pre-compiled form which means that it is syntactically valid and does not need to be compiled at run-time, thereby saving resources;
each user of the stored procedure/function will use exactly the same form of queries which means the queries are reused thereby reducing the parsing overhead and improving the scalability of applications;
as the procedures/functions are stored in the database there is no need to transfer the code from the clients to the database server or to transfer intermediate results from the server to the clients. This results in much less network traffic and again improves scalability;
when using PL/SQL packages, as soon as one object in the package is accessed, the whole package is loaded into memory which makes subsequent access to objects in the package much faster
stored procedures/functions can be compiled into “native” machine code making them even faster (available with Oracle 9i and above)

Disadvantages:

there is an overhead involved in switching from SQL to PL/SQL, this may be significant in terms of performance but usually this overhead is outweighed by performance advantages of using PL/SQL
more memory may be required when using packages as the whole package is loaded into memory as soon as any object in the package is accessed
native compilation can take twice as long as normal compilation

Despite the advantages listed above, there are some situations where the use of stored procedures is not recommended or may be infeasible.

Disadvantages

  • Applications that involve extensive business logic and processing could place an excessive load on the server if the logic was implemented entirely in stored procedures. Examples of this type of processing include data transfers, data traversals, data transformations and intensive computational operations. You should move this type of processing to business process or data access logic components, which are a more scalable resource than your database server.
  • Do not put all of your business logic into stored procedures. Maintenance and the agility of your application becomes an issue when you must modify business logic in T-SQL. For example, ISV applications that support multiple RDBMS should not need to maintain separate stored procedures for each system.
  • Writing and maintaining stored procedures is most often a specialized skill set that not all developers possess. This situation may introduce bottlenecks in the project development schedule.
About these ads

21 Responses to “Advantages and Disadvantages of Stored Procedure”

  1. utahhomebook.info Says:

    Admirable multimedia!

  2. neelesh Says:

    it is not a details of disadvantage of sproc,please mention what are actually causes of disadvantage of sproc

  3. Muhammad Fahad Says:

    Yes, i agree with you the disadvantages should be discussed. I came to this web address to watch out for Disadvantages of sp. So i would like to request you to please provide disadvantage of using sps.

  4. Ali Mustapha Murfa Says:

    I am a student in Federal Polytechnic Damaturu Yobe State Nigeria at Department of Estate Management.

  5. laxmi Says:

    Yes, i agree with you the disadvantages should be discussed. I came to this web address to watch out for Disadvantages of sp. So i would like to request you to please provide disadvantage of using sps.

  6. Sumesh Says:

    hi,
    could you please tell me in detail about the disadvantage of Usinf Stored proceedure.

  7. mukund Says:

    Despite the advantages listed above, there are some situations where the use of stored procedures is not recommended or may be infeasible.

    Disadvantages

    * Applications that involve extensive business logic and processing could place an excessive load on the server if the logic was implemented entirely in stored procedures. Examples of this type of processing include data transfers, data traversals, data transformations and intensive computational operations. You should move this type of processing to business process or data access logic components, which are a more scalable resource than your database server.
    * Do not put all of your business logic into stored procedures. Maintenance and the agility of your application becomes an issue when you must modify business logic in T-SQL. For example, ISV applications that support multiple RDBMS should not need to maintain separate stored procedures for each system.
    * Writing and maintaining stored procedures is most often a specialized skill set that not all developers possess. This situation may introduce bottlenecks in the project development schedule.

  8. sunan Says:

    hope your assumption is right

  9. Jaykishan Says:

    Please specify limitations in details. This is fine if you give some examples.

  10. Prakash Says:

    I personally feel, the usage of SPC and SQL are purely dependant on the architecture of the application. If the application involves large number of DMLs (Update, Insert, Delete), then the preference would be usage of cached queries (example, PreparedStatements in java, prepare calls from .NET etc.,), because the SPC call will required prepare and unprepare SPC each time when it is called. It is also required to recompile to update the execution plan. So, there will be overhead, which proves SQL is better over SPC. However, if the application is like archive enquiry, then such cases, the proper implementation of SPC can prove healthy and productive.

    What you guys think?

  11. mukund Says:

    Prakash you are correct to some Extent. But take a case where there is a huge dml Operation. Say updating a table with around one million records in one shot. In that case a stored procedure might be preferable – for the simple cause of avoiding network transfer time.
    Also I guess a Stored procedure once compiled will always remain in the memory and will have its execution plan calculated once and for ever.

    Take another case where you have to update delete and insert rows in to tables frequently – say like one million records in One day and not in one shot – Then yes. I will prefer using cached queries.

    Guys what are your opinions.

  12. vineet Says:

    thanx a lot for mentioning those disadvantages…

  13. RAJINDER SINGH Says:

    Yes, i agree with you the disadvantages should be discussed.So i would like to request you to please provide disadvantage of using sps.
    i would also like to request you to please give some more examples of pl/sql.

  14. RAJINDER SODHI Says:

    hi,
    could you please tell me in detail about the disadvantage of sinf Stored procedure.i also hope that your assumption about PL/SQL is right.

  15. RAJINDER SODHI Says:

    Please specify limitations in details related to sybase. This is fine if you give some more examples of PL/SQL.

  16. Devis Says:

    Thank you for the useful info.

  17. anitha1234 Says:

    Hd Wallpapers for free, sorted by categories. Easy to search hd wallpapers for your desktop background. Customize your background with cool hd wallpapers. Shriya

  18. pavan Says:

    The usage of Stored Procedure dependent on the architecture of the application. If the application has large number of DMLs, so the preference would be usage of cached queries.
    In news portal like website Stored Procedure Usage is most important. Stored Procedure is not preferable for small projects.

  19. mahesh Says:

    stored procedure is preferable to large applications only so when u r using SP think as end user.

  20. vidyasagar Says:

    thanks for giving useful info abt proc


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: