天道酬勤,学无止境

What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]

What are the advantages/disadvantages of keeping SQL in your C# source code or in Stored Procs? I've been discussing this with a friend on an open source project that we're working on (C# ASP.NET Forum). At the moment, most of the database access is done by building the SQL inline in C# and calling to the SQL Server DB. So I'm trying to establish which, for this particular project, would be best.

So far I have:

Advantages for in Code:

  • Easier to maintain - don't need to run a SQL script to update queries
  • Easier to port to another DB - no procs to port

Advantages for Stored Procs:

  • Performance
  • Security

评论

I am not a fan of stored procedures

Stored Procedures are MORE maintainable because: * You don't have to recompile your C# app whenever you want to change some SQL

You'll end up recompiling it anyway when datatypes change, or you want to return an extra column, or whatever. The number of times you can 'transparently' change the SQL out from underneath your app is pretty small on the whole

  • You end up reusing SQL code.

Programming languages, C# included, have this amazing thing, called a function. It means you can invoke the same block of code from multiple places! Amazing! You can then put the re-usable SQL code inside one of these, or if you want to get really high tech, you can use a library which does it for you. I believe they're called Object Relational Mappers, and are pretty common these days.

Code repetition is the worst thing you can do when you're trying to build a maintainable application!

Agreed, which is why storedprocs are a bad thing. It's much easier to refactor and decompose (break into smaller parts) code into functions than SQL into... blocks of SQL?

You have 4 webservers and a bunch of windows apps which use the same SQL code Now you realized there is a small problem with the SQl code so do you rather...... change the proc in 1 place or push the code to all the webservers, reinstall all the desktop apps(clickonce might help) on all the windows boxes

Why are your windows apps connecting directly to a central database? That seems like a HUGE security hole right there, and bottleneck as it rules out server-side caching. Shouldn't they be connecting via a web service or similar to your web servers?

So, push 1 new sproc, or 4 new webservers?

In this case it is easier to push one new sproc, but in my experience, 95% of 'pushed changes' affect the code and not the database. If you're pushing 20 things to the webservers that month, and 1 to the database, you hardly lose much if you instead push 21 things to the webservers, and zero to the database.

More easily code reviewed.

Can you explain how? I don't get this. Particularly seeing as the sprocs probably aren't in source control, and therefore can't be accessed via web-based SCM browsers and so on.

More cons:

Storedprocs live in the database, which appears to the outside world as a black box. Simple things like wanting to put them in source control becomes a nightmare.

There's also the issue of sheer effort. It might make sense to break everything down into a million tiers if you're trying to justify to your CEO why it just cost them 7 million dollars to build some forums, but otherwise creating a storedproc for every little thing is just extra donkeywork for no benefit.

This is being discussed on a few other threads here currently. I'm a consistent proponent of stored procedures, although some good arguments for Linq to Sql are being presented.

Embedding queries in your code couples you tightly to your data model. Stored procedures are a good form of contractual programming, meaning that a DBA has the freedom to alter the data model and the code in the procedure, so long as the contract represented by the stored procedure's inputs and outputs is maintained.

Tuning production databases can be extremely difficult when the queries are buried in the code and not in one central, easy to manage location.

[Edit] Here is another current discussion

In my opinion you can't vote for yes or no on this question. It totally depends on the design of your application.

I totally vote against the use of SPs in an 3-tier environment, where you have an application server in front. In this kind of environment your application server is there to run your business logic. If you additionally use SPs you start distributing your implementation of business logic all over your system and it will become very unclear who is responsible for what. Eventually you will end up with an application server that will basically do nothing but the following:

(Pseudocode)

Function createOrder(Order yourOrder) 
Begin
  Call SP_createOrder(yourOrder)
End

So in the end you have your middle tier running on this very cool 4 Server cluster each of them equipped with 16 CPUs and it will actually do nothing at all! What a waste!

If you have a fat gui client that directly connects to your DB or maybe even more applications it's a different story. In this situation SPs can serve as some sort of pseudo middle tier that decouples your application from the data model and offers a controllable access.

Advantages for in Code:

  • Easier to maintain - don't need to run a SQL script to update queries
  • Easier to port to another DB - no procs to port

Actually, I think you have that backwards. IMHO, SQL in code is pain to maintain because:

  • you end up repeating yourself in related code blocks
  • SQL isn't supported as a language in many IDE's so you have just a series of un-error checked strings performing tasks for you
  • changes in a data type, table name or constraint are far more prevalent than swapping out an entire databases for a new one
  • your level of difficulty increases as your query grows in complexity
  • and testing an inline query requires building the project

Think of Stored Procs as methods you call from the database object - they are much easier to reuse, there is only one place to edit and in the event that you do change DB providers, the changes happen in your Stored Procs and not in your code.

That said, the performance gains of stored procs is minimal as Stu said before me and you can't put a break point in a stored procedure (yet).

CON

I find that doing lots of processing inside stored procedures would make your DB server a single point of inflexibility, when it comes to scaling your act.

However doing all that crunching in your program as opposed to the sql-server, might allow you to scale more if you have multiple servers that runs your code. Of-course this does not apply to stored procs that only does the normal fetch or update but to ones that perform more processing like looping over datasets.

PROS

  1. Performance for what it may be worth (avoids query parsing by DB driver / plan recreation etc)
  2. Data manipulation is not embedded in the C/C++/C# code which means I have less low level code to look through. SQL is less verbose and easier to look through when listed separately.
  3. Due to the separation folks are able to find and reuse SQL code much easier.
  4. Its easier to change things when schema changes - you just have to give the same output to the code and it will work just fine
  5. Easier to port to a different database.
  6. I can list individual permissions on my stored procedures and control access at that level too.
  7. I can profile my data query/ persistence code separate from my data transformation code.
  8. I can implement changeable conditions in my stored procedure and it would be easy to customize at a customer site.
  9. It becomes easier to use some automated tools to convert my schema and statements together rather than when it is embedded inside my code where I would have to hunt them down.
  10. Ensuring best practices for data access is easier when you have all your data access code inside a single file - I can check for queries that access the non performant table or that which uses a higher level of serialization or select *'s in the code etc.
  11. It becomes easier to find schema changes / data manipulation logic changes when all of it is listed in one file.
  12. It becomes easier to do search and replace edits on SQL when they are in the same place e.g. change / add transaction isolation statements for all stored procs.
  13. I and the DBA guy find that having a separate SQL file is easier / convenient when the DBA has to review my SQL stuff.
  14. Lastly you don't have to worry about SQL injection attacks because some lazy member of your team did not use parametrized queries when using embedded sqls.

The performance advantage for stored procedures is often negligable.

More advantages for stored procedures:

  • Prevent reverse engineering (if created With Encryption, of course)
  • Better centralization of database access
  • Ability to change data model transparently (without having to deploy new clients); especially handy if multiple programs access the same data model

I fall on the code side. We build data access layer that's used by all all the apps (both web and client), so it's DRY from that perspective. It simplifies the database deployment because we just have to make sure the table schema's are correct. It simplifies code maintenance because we don't have to look at source code and the database.

I don't have much problem with the tight coupling with the data model because I don't see where it's possible to really break that coupling. An application and its data are inherently coupled.

Stored procedures.

If an error slips or the logic changes a bit, you do not have to recompile the project. Plus, it allows access from different sources, not just the one place you coded the query in your project.

I don't think it is harder to maintain stored procedures, you should not code them directly in the database but in separate files first, then you can just run them on whatever DB you need to set-up.

Advantages for Stored procedures:

More easily code reviewed.

Less coupled, therefore more easily tested.

More easily tuned.

Performance is generally better, from the point of view of network traffic - if you have a cursor, or similar, then there aren't multiple trips to the database

You can protect access to the data more easily, remove direct access to the tables, enforce security through the procs - this also allows you to find relatively quickly any code that updates a table.

If there are other services involved (such as Reporting services), you may find it easier to store all of your logic in a stored procedure, rather than in code, and having to duplicate it

Disadvantages:

Harder to manage for the developers: version control of the scripts: does everyone have their own database, is the version control system integrated with the database and IDE?

In some circumstances, dynamically created sql in code can have better performance than a stored proc. If you have created a stored proc (let's say sp_customersearch) that gets extremely complicated with dozens of parameters because it must be very flexible, you can probably generate a much simpler sql statement in code at runtime.

One could argue that this simply moves some processing from SQL to the web server, but in general that would be a good thing.

The other great thing about this technique is that if you're looking in SQL profiler you can see the query you generated and debug it much easier than seeing a stored proc call with 20 parameters come in.

I like stored procs, dont know how many times I was able to make a change to an application using a stored procedure which didn't produce any downtime to the application.

Big fan of Transact SQL, tuning large queries have proven to be very useful for me. Haven't wrote any inline SQL in about 6 years!

You list 2 pro-points for sprocs:

Performance - not really. In Sql 2000 or greater the query plan optimisations are pretty good, and cached. I'm sure that Oracle etc do similar things. I don't think there's a case for sprocs for performance any more.

Security? Why would sprocs be more secure? Unless you have a pretty unsecured database anyway all the access is going to be from your DBAs or via your application. Always parametrise all queries - never inline something from user input and you'll be fine.

That's best practice for performance anyway.

Linq is definitely the way I'd go on a new project right now. See this similar post.

@Keith

Security? Why would sprocs be more secure?

As suggested by Komradekatz, you can disallow access to tables (for the username/password combo that connects to the DB) and allow SP access only. That way if someone gets the username and password to your database they can execute SP's but can't access the tables or any other part of the DB.

(Of course executing sprocs may give them all the data they need but that would depend on the sprocs that were available. Giving them access to the tables gives them access to everything.)

Think of it this way

You have 4 webservers and a bunch of windows apps which use the same SQL code Now you realized there is a small problem with the SQl code so do you rather...... change the proc in 1 place or push the code to all the webservers, reinstall all the desktop apps(clickonce might help) on all the windows boxes

I prefer stored procs

It is also easier to do performance testing against a proc, put it in query analyzer set statistics io/time on set showplan_text on and voila

no need to run profiler to see exactly what is being called

just my 2 cents

I prefer keeping in them in code (using an ORM, not inline or ad-hoc) so they're covered by source control without having to deal with saving out .sql files.

Also, stored procedures aren't inherently more secure. You can write a bad query with a sproc just as easily as inline. Parameterized inline queries can be just as secure as a sproc.

Use your app code as what it does best: handle logic.
User your database for what it does best: store data.

You can debug stored procedures but you will find easier to debug and maintaing logic in code. Usually you will end recompiling your code every time you change the database model.

Also stored procedures with optional search parameters are very inneficient because you have to specify in advance all the possible parameters and complex searches are sometimes not possible because you cant predict how many times a parameter is going to be repeated in the seach.

When it comes to security, stored procedures are much more secure. Some have argued that all access will be through the application anyway. The thing that many people are forgetting is that most security breaches come from inside a company. Think about how many developers know the "hidden" user name and password for your application?

Also, as MatthieuF pointed out, performance can be much improved due to fewer round trips between the application (whether it's on a desktop or web server) and the database server.

In my experience the abstraction of the data model through stored procedures also vastly improves maintainability. As someone who has had to maintain many databases in the past, it's such a relief when confronted with a required model change to be able to simply change a stored procedure or two and have the change be completely transparent to ALL outside applications. Many times your application isn't the only one pointed at a database - there are other applications, reporting solutions, etc. so tracking down all of those affected points can be a hassle with open access to the tables.

I'll also put checks in the plus column for putting the SQL programming in the hands of those who specialize in it, and for SPs making it much easier to isolate and test/optimize code.

The one downside that I see is that many languages don't allow the passing of table parameters, so passing an unknown number data values can be annoying, and some languages still can't handle retrieving multiple resultsets from a single stored procedure (although the latter doesn't make SPs any worse than inline SQL in that respect).

One of the suggestions from a Microsoft TechEd sessions on security which I attended, to make all calls through stored procs and deny access directly to the tables. This approach was billed as providing additional security. I'm not sure if it's worth it just for security, but if you're already using stored procs, it couldn't hurt.

Definitely easier to maintain if you put it in a stored procedure. If there's difficult logic involved that will potentially change in the future it is definitely a good idea to put it in the database when you have multiple clients connecting. For example I'm working on an application right now that has an end user web interface and an administrative desktop application, both of which share a database (obviously) and I'm trying to keep as much logic on the database as possible. This is a perfect example of the DRY principle.

I'm firmly on the side of stored procs assuming you don't cheat and use dynamic SQL in the stored proc. First, using stored procs allows the dba to set permissions at the stored proc level and not the table level. This is critical not only to combating SQL injection attacts but towards preventing insiders from directly accessing the database and changing things. This is a way to help prevent fraud. No database that contains personal information (SSNs, Credit card numbers, etc) or that in anyway creates financial transactions should ever be accessed except through strored procedures. If you use any other method you are leaving your database wide open for individuals in the company to create fake financial transactions or steal data that can be used for identity theft.

Stored procs are also far easier to maintain and performance tune than SQL sent from the app. They also allow the dba a way to see what the impact of a database structural change will have on the way the data is accessed. I've never met a good dba who would allow dynamic access to the database.

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • 将SQL保留在存储的Procs与代码中有什么优缺点?(What are the pros and cons to keeping SQL in Stored Procs versus Code [closed])
    问题 关门了。 这个问题是基于意见的。 它当前不接受答案。 7年前关闭。 已锁定。 该问题及其答案被锁定,因为该问题是题外话,但具有历史意义。 它目前不接受新的答案或互动。 将SQL保留在C#源代码或存储的Procs中有哪些优点/缺点? 我一直在和一个我们正在开发的开源项目的朋友讨论这个问题(C#ASP.NET论坛)。 目前,大多数数据库访问是通过在C#中构建SQL内联并调用SQL Server DB来完成的。 因此,我正在尝试确定对于该特定项目而言哪一个是最佳的。 到目前为止,我有: 在Code中的优势: 易于维护-无需运行SQL脚本即可更新查询移植到另一个数据库更容易-没有proc移植到端口 存储过程的优点: 表现安全 回答1 我不喜欢存储过程 存储过程具有更多的可维护性,因为:*每当您想更改某些SQL时,您都不必重新编译C#应用程序 无论如何,当数据类型更改,或者想要返回额外的列或其他内容时,最终还是要重新编译它。 总体上,您可以从应用程序下方“透明地”更改SQL的次数非常少 您最终将重用SQL代码。 包括C#在内的编程语言具有令人称奇的功能,称为函数。 这意味着您可以从多个位置调用同一代码块! 惊人的! 然后,您可以将可重用的SQL代码放入其中之一,或者,如果您想获得真正的高科技,则可以使用为您提供帮助的库。 我相信它们被称为“对象关系映射器”,并且如今非常普遍。
  • When is it better to write “ad hoc sql” vs stored procedures [duplicate]
    This question already has answers here: What are the pros and cons to keeping SQL in Stored Procs versus Code [closed] (47 answers) Closed 4 years ago. I have 100% ad hoc sql through out my application. A buddy of mine recommended that I convert to stored procedures for the extra performance and security. This brought up a question in my mind, besides speed and security is there any other reason to stick with ad hoc sql queries?
  • Are these the main differences between RestSharp and ServiceStack's Client Code? [closed]
    Closed. This question is opinion-based. It is not currently accepting answers. Want to improve this question? Update the question so it can be answered with facts and citations by editing this post. Closed 6 years ago. Improve this question I have been unable to make a definitive choice and was hoping that somebody (or a combination of a couple of people) could point out the differences between using RestSharp versus ServiceStack's client services (keeping in mind that I am already using ServiceStack for my service). Here is what I have so far (differences only). The list is fairly small as
  • What are the pros and cons of the assorted Java web frameworks? [closed]
    As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. Closed 9 years ago. I am considering creating my own website using Java and am trying to decide what framework to use. However, doing a quick search for Java frameworks returns more than 50 to choose from! My website is just going to be for my own enjoyment
  • To ARC or not to ARC? What are the pros and cons? [closed]
    Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed 7 years ago. Improve this question I've yet to use ARC, since the majority of the code in the project I'm working on at the moment was written pre-iOS 5.0. I was just wondering, does the convenience of not retaining/releasing manually (and presumably more reliable code that comes as a result?) outweigh any 'cost' of using ARC? What are your experiences of ARC, and would you recommend it? So: How
  • What reasons are there to prefer glob over readdir (or vice-versa) in Perl?
    This question is a spin-off from this one. Some history: when I first learned Perl, I pretty much always used glob rather than opendir + readdir because I found it easier. Then later various posts and readings suggested that glob was bad, and so now I pretty much always use readdir. After thinking over this recent question I realized that my reasons for one or the other choice may be bunk. So, I'm going to lay out some pros and cons, and I'm hoping that more experienced Perl folks can chime in and clarify. The question in a nutshell is are there compelling reasons to prefer glob to readdir or
  • MySQL versus PDO [duplicate]
    This question already has answers here: mysqli or PDO - what are the pros and cons? [closed] (13 answers) Closed 7 years ago. I'm fairly new to PHP and have built a medium sized website using standard MySQL database calls. However, I have recently learned about PDO and I am hoping to find out from the community if it is worth switching from MySQL over to PDO. For security I have been using mysql_real_escape_string. Info about the site: I'm using a mix of INSERT and SELECT calls. The data returned from SELECT calls isn't massive (no more than 30 records returned by using LIMIT). There will also
  • How do you keep parents of floated elements from collapsing? [duplicate]
    This question already has answers here: What methods of ‘clearfix’ can I use? (29 answers) Closed 6 years ago. Although elements like <div>s normally grow to fit their contents, using the float property can cause a startling problem for CSS newbies: If floated elements have non-floated parent elements, the parent will collapse. For example: <div> <div style="float: left;">Div 1</div> <div style="float: left;">Div 2</div> </div> The parent div in this example will not expand to contain its floated children - it will appear to have height: 0. How do you solve this problem? I would like to create
  • What are the pros and cons of View-first vs. ViewModel-first in the MVVM pattern [closed]
    Closed. This question is opinion-based. It is not currently accepting answers. Want to improve this question? Update the question so it can be answered with facts and citations by editing this post. Closed 4 years ago. Improve this question I'm giving a presentation on using MVVM in real world applications and I'm including a section on the religious wars design decisions involved when using MVVM as a pattern in your application. In an MVVM application there are two main ways (that I know of) to instantiate a new View/ViewModel pair: View-First in which you create a view and it creates its own
  • What are the pros and cons of performing calculations in sql vs. in your application
    shopkeeper table has following fields: id (bigint),amount (numeric(19,2)),createddate (timestamp) Let's say, I have the above table. I want to get the records for yesterday and generate a report by having the amount printed to cents. One way of doing is to perform calculations in my java application and execute a simple query Date previousDate ;// $1 calculate in application Date todayDate;// $2 calculate in application select amount where createddate between $1 and $2 and then loop through the records and convert amount to cents in my java application and generate the report Another way is
  • LINQ-to-SQL vs stored procedures? [closed]
    As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. Closed 8 years ago. I took a look at the "Beginner's Guide to LINQ" post here on StackOverflow (Beginners Guide to LINQ), but had a follow-up question: We're about to ramp up a new project where nearly all of our database op's will be fairly simple data
  • mysqli or PDO - what are the pros and cons? [closed]
    As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. Closed 8 years ago. Locked. This question and its answers are locked because the question is off-topic but has historical significance. It is not currently accepting new answers or interactions. In our place we're split between using mysqli and PDO for stuff
  • What are the pros and cons of the leading Java HTML parsers? [closed]
    Closed. This question needs to be more focused. It is not currently accepting answers. Want to improve this question? Update the question so it focuses on one problem only by editing this post. Closed 7 years ago. Improve this question Searching SO and Google, I've found that there are a few Java HTML parsers which are consistently recommended by various parties. Unfortunately it's hard to find any information on the strengths and weaknesses of the various libraries. I'm hoping that some people have spent some comparing these libraries, and can share what they've learned. Here's what I've seen
  • C# Interfaces. Implicit implementation versus Explicit implementation
    What are the differences in implementing interfaces implicitly and explicitly in C#? When should you use implicit and when should you use explicit? Are there any pros and/or cons to one or the other? Microsoft's official guidelines (from first edition Framework Design Guidelines) states that using explicit implementations are not recommended, since it gives the code unexpected behaviour. I think this guideline is very valid in a pre-IoC-time, when you don't pass things around as interfaces. Could anyone touch on that aspect as well?
  • What are the pros and cons of the SVN plugins for Eclipse, Subclipse and Subversive? [closed]
    As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. Closed 8 years ago. SVN in Eclipse is spread into two camps. The SVN people have developed a plugin called Subclipse. The Eclipse people have a plugin called Subversive. Broadly speaking they both do the same things. What are the advantages and disadvantages
  • Dynamic Sorting within SQL Stored Procedures
    This is an issue that I've spent hours researching in the past. It seems to me to be something that should have been addressed by modern RDBMS solutions but as yet I have not found anything that really addresses what I see to be an incredibly common need in any Web or Windows application with a database back-end. I speak of dynamic sorting. In my fantasy world, it should be as simple as something like: ORDER BY @sortCol1, @sortCol2 This is the canonical example given by newbie SQL and Stored Procedure developers all over forums across the Internet. "Why isn't this possible?" they ask
  • Ad hoc queries vs stored procedures vs Dynamic SQL [closed]
    As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. Closed 8 years ago. Ad hoc queries vs stored procedures vs Dynamic SQL. Can anyone say pros and cons?
  • Pros & Cons of Google App Engine [closed]
    Closed. This question is opinion-based. It is not currently accepting answers. Want to improve this question? Update the question so it can be answered with facts and citations by editing this post. Closed 7 years ago. Improve this question [An Updated List 21st Aug 09] Help me Compile a List of all the Advantages & Disadvantages of Building an Application on the Google App Engine Pros: No need to buy servers or server space (no maintenance). Makes solving the problem of scaling easier. Free up to a certain level of consumed resources. Cons: Locked into Google App Engine ? Developers have read
  • ResultSet: Retrieving column values by index versus retrieving by label
    When using JDBC, I often come across constructs like ResultSet rs = ps.executeQuery(); while (rs.next()) { int id = rs.getInt(1); // Some other actions } I asked myself (and authors of code too) why not to use labels for retrieving column values: int id = rs.getInt("CUSTOMER_ID"); The best explanation I've heard is something concerning performance. But actually, does it make processing extremely fast? I don't believe so, though I have never performed measurements. Even if retrieving by label would be a bit slower, nevertheless, it provide better readability and flexibility, in my opinion. So
  • Inserting into Oracle and retrieving the generated sequence ID
    I have a handful of raw SQL queries for SQL Server which use SCOPE_IDENTITY to retrieve the generated ID for a specific INSERT immediately after that INSERT occurs all in one execution… INSERT into Batch( BatchName, BatchType, Source, Area ) Values ( @strBatchName, @strType, @strSource, @intArea ); SELECT SCOPE_IDENTITY() BatchID; The question is: What’s the best way to do that for an Oracle database? Can this be done on Oracle through standard SQL or do I have to switch this to use a stored procedure and place something similar in the body of the stored proc? If it must be a stored proc, then