19

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....

Drew
  • 29,895
  • 7
  • 74
  • 104
CSharpAtl
  • 7,374
  • 8
  • 39
  • 53

18 Answers18

39

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.

ckarras
  • 4,946
  • 2
  • 33
  • 37
  • 5
    Queries don't provide an abstraction layer over the database structure, since you have to specify the table names, column names and key names for the joins. But I agree about views, they can be another way to make an abstraction layer over the database structure (both for querying data, and for modifying it through "instead of" triggers). The major difference between the two abstraction layers (stored procedures vs views) is that stored procedures are "verbs" while views are "nouns". – ckarras Apr 21 '11 at 02:09
11

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.

Ryan Lundy
  • 204,559
  • 37
  • 180
  • 211
10

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

DCNYAM
  • 11,966
  • 8
  • 53
  • 70
  • 5
    not that big, since it can also be accomplished with views or other database platform specific access control tools (roles,...) – ckarras May 27 '09 at 18:36
  • 3
    No, it still is a big advantage, regardless of other means available. – Gromer May 27 '09 at 18:50
  • 2
    @Gromer: What advantages do stored procedure have over these other available means? Is there something they do better? Something that can be done only with stored procedures? – ckarras May 28 '09 at 00:42
  • 1
    @ckarras: Are you talking about only letting the user access Views and only using "INSTEAD OF triggers" in the views? I have never seen such aproach in action and seems a hack in comparison to just using stored procedures... Also IT IS less powerful and secure than just letting the database user just have the permission to execute very particular actions (stored procedures). – Durden81 Oct 11 '11 at 09:07
  • @ckarras: Also if you want to protect your code from being stolen by developers stored procedures are the best tool. You only allow programmers access to the source code, and DBAs only the DB. You don't give ANY permission to your developers apart from executing SPs. If they want to do ANYTHING in the db they have to go to the DBA desk and talk it with them and tell them to do it. Only from their machine they will be able to see the underlying db structure and the code of the stored procedures. Also this level of security with your code can be only done with SPs. – Durden81 Oct 11 '11 at 09:14
9

Disadvantages

  • Refactoring is harder. Renaming or changing where the stored procedure is might produce a bad effect.

  • Unit testing stored procedure require code assistance outside the DB

Advantage

  • You do not need to deploy to make a change.
  • Faster sometime
  • Easier to expand a system
Community
  • 1
  • 1
Patrick Desjardins
  • 136,852
  • 88
  • 292
  • 341
6

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.

Steve
  • 1,065
  • 15
  • 34
5

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

Vin
  • 6,115
  • 4
  • 41
  • 55
4

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.
Jeff Jones
  • 361
  • 1
  • 9
4

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.

yogman
  • 4,021
  • 4
  • 24
  • 27
  • 2
    Surely this just means you are not treating your stored procedures correctly. They should be treated as source code and managed alongside your java or C# or whatever – WW. Jan 20 '09 at 06:55
  • 1
    WW, I totally agree with you in principle. But is there a tool to support that? SourceSafe? CVS? Subversion? Git? – yogman Jan 20 '09 at 20:47
4

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.
Martynnw
  • 10,625
  • 5
  • 28
  • 27
2

The advantage I see in using Stored Procedures over writing the same logic within application code is that it can reduce the number of calls the application makes to the database.

A stored procedure can take in its arguments and make different decisions and actions based on those arguments instead of returning a result to the application, and then the application making a decision and decide it needs to perform another action and make another database call.

The bottle neck in performance is almost always inter-process communication. I try to make the minimal amount of calls to the database.

Clinton
  • 198
  • 8
2

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

Michael L Perry
  • 7,327
  • 3
  • 35
  • 34
2

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

Michael L Perry
  • 7,327
  • 3
  • 35
  • 34
2

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

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
1

Another advantage can be in large enterprise environments where you could have multiple client applications & environments (such as web, desktop, reporting tools spread over different OS's) that use the database. For some business rule changes the change can be made in the database and this would be effective in all environments.

Alan Savage
  • 822
  • 1
  • 12
  • 24
1

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

0

Advantages -

  1. Organized at one place (not sprinkled all over the code)
  2. Much faster than dynamic queries
Ajay
  • 746
  • 1
  • 10
  • 25
  • 2
    -1, they're not necessarily any faster than dynamic queries. Sometimes they are, but often the performance is exactly the same. Not all stored procedures can be effectively precompiled. – John M Gant May 27 '09 at 18:26
0

the simple answer would be as a follow: adv: it is the most powerful structure to encapsulating T-SQL Codes. it is not limited to SELECT, and it supports all the DML codes. it provides to receive inputs and to return outputs directly.

dis: it is not possible to call it in the SELECT, so you cannot run it for several records.

Mohamad Reza Taghipour
  • 1
  • 2
0

It increases the load on server. If other applications or more than one application are using the same database server they become slow.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Rakesh kumar -Melstar
  • 1