Disadvantage of stored procedures

Question

Would like to get a list of advantages and disadvantages of using Stored Procedures. The main advantage of SPs seems to be precompiled and an abstraction of data from the application. Give me your thoughts....

Answer 1

Advantage: the operations team has a hook to monitor or fix problems in production.

Answer 2

Probably want to look here for a discussion of this topic.

Answer 3

Advantage: the DBA can add behavior that the application doesn't care about. For example, storing a modify date on each row.

Answer 4

Disadvantages

• Refactoring is harder. Renaming or changing where the store proc is might produce a bad effect.

• Unit testing stored proc require code assistance outside the DB

Advantage

• You do not need to deploy to make a change.
• Faster sometime
• Easier to expand a system

Answer 5

Advantages -

1. Organized at one place (not sprinkled all over the code)
2. Much faster than dynamic queries

Answer 6

Just a few reasons I use stored procedures exclusively when building applications:

• Stored procedures can be the interface between your application and the underlying database. This way, the server on which the database resides, which is usually more powerful than a desktop machine, can be used to perform more complex procedures.
• If you need to change the structure of the database, you can do so without breaking your application if stored procedures are used as the interface.
• As you write, stored procedures contain precompiled and pre-tested SQL.
• It is easier to perform complex operations with stored procedures than with SQL generated by the client or GUI.

Answer 7

Correction: Whether they're precompiled depends on the database. In SQL Server, for instance, they're not. Stored procedures and parameterized SQL are both compiled before being run. A stored procedure can sometimes reuse an execution plan if a corresponding one exists...but so can parameterized SQL.

Edit: Here's what MSDN says about it:

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.

Answer 8

With the current .Net 3.5 framework libraries, I would use Linq to perform most database operations. There might be places where SP makes more sense. But Linq has provisions to run an SP too.

On the topic of disadvantages of SP, check out the following link - an interesting analysis. Check the blog post's comments too.

http://www.spoiledtechie.com/post/Whats-up-with-Stored-Procedures-these-days.aspx

Answer 9

By using SPs, you also avoid having to give users direct access to tables. All access can be controlled via the SPs.

Answer 10

Advantage: Stored procedures can be used to maintain data integrity and enforce database policy without relying on an external program to do so.

Disadvantage: Can make debugging more complex. Can also be sensitive to being dropped during copy operations, if not done correctly.

Answer 11

Seems like a lot more Advantages than disadvantages for USING Sps..

Answer 12

Another disadvantage is version control, because some of the business logic is now in the database side. Can you easily roll back to v1 (one year ago) from v2 (now)?

A feasible solution is versioning the stored procedure names. But now the database is a mess with old and new stored procedures.

Answer 13

Advantage: your database-related code is more likely to be written by staff who are interested in and skilled at database work.

Answer 14

Advantages: SP's are used to excute set of sql statements. Disadvantages: Debugging is complex

Answer 15

Disadvantages

• Source control can be a pain.
• Debugging is hard.
• If you have a lot of functionality in procs it will making swapping between different database systems harder - It also creates more work if you want to support different database systems.
• Developing stored procedures can be a fairly specialised task, especially as they get more complex.

Answer 16

Advantages: Provides a "public interface" to a database (another abstraction layer).

Also groups all queries at the same location, making it easier for DBAs to see how the database is queried and optimize it accordingly.

Disadvantages: May not be the best place to put complex logic. However, following the idea that complex logic belongs in application code and not in stored procedures, stored procedure become simply CRUD operations (each table has a "Create", "Read", "Update" and "Delete" procedure). In that case, stored procedures don't add any value to the application, they only complexify maintenance and become waste.

Queries are all grouped together, so it's harder to see the context of the application where they are being used. Analyzing the impact of a change is longer, and doing the change is longer as well.

Therefore: use stored procedures to encapsulate complex queries (complex joins, complex where clauses, ...). But don't use stored procedure for complex application/domain/business logic, and don't use stored procedures for CRUD either. So stored procedures should be used in a minority of cases rather than be the standard tool for all queries in an application.

Group code (including queries) to achieve "functional cohesion" instead of grouping by tool/technology. To allow a DBA to optimize a database based on how it is being queried, use a profiler.