什么利弊保持在SQL存储的特效与code [英] What are the pros and cons to keeping SQL in Stored Procs versus Code

问题描述

什么是保持SQL在C＃源$ C ​​$ C或存储特效的优势/劣势？我一直在与我们正在对（C＃ASP.NET论坛）工作的一个开源项目的朋友讨论这个。目前，大多数的数据库访问是通过建立在C＃中的SQL内联和调用到SQL Server数据库完成。所以我试图建立当中，对这个特定的项目，将是最好的。

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.

到目前为止，我有：

在code的优点：

• 更容易维护 - 不需要运行SQL脚本来更新查询


• 更容易移植到另一个DB - 没有特效端口

有关存储特效优点：

• 性能


• 安全

推荐答案

我不是存储过程的风扇

存储过程更易于维护，因为：
     *您不必重新编译你的C＃应用​​程序，每当你想改变一些SQL

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

您最终会重新编译反正它时，数据类型发生改变，或者你想返回一个额外的列，或什么的。你可以'透明'从你的应用程序下更改SQL的次数是pretty小就全

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

  
• 您最终会重用SQL code。

  

编程语言，C＃在内，有这种令人不可思议的事情，调用的函数。这意味着你可以从多个地方调用code的同一块！惊人！然后，您可以将可重复使用的SQL code里面的其中之一，或者如果你想获得真正的高科技，你可以用它会为你的图书馆。我相信他们是所谓的对象关系映射，并共同pretty这些天。

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重复是当你试图建立一个维护应用程序，你可以做的最糟糕的事情！

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

同意，这就是为什么storedprocs是一件坏事。它更容易重构和分解（分解成更小的部分）code到功能比SQL到SQL ...块？

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?

您有4个网络服务器和一堆它们使用相同的SQL code现在你意识到有一个小问题与SQL code所以你相当的Windows应用程序的改变......在proc在1处或推code到所有的Web服务器，重新安装上的所有窗户框所有的桌面应用程序（的ClickOnce可能会帮助）

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

为什么你的窗户直接连接到一个中央数据库应用程序吗？这似乎是一个巨大的安全漏洞在那里，和瓶颈它排除了服务器端缓存。难道他们不应该通过Web服务来连接或类似web服务器的？

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?

所以，推1个新的存储过程，或4个新的Web服务器？

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

在此情况下，它的是的易于推动一种新的存储过程，但在我的经验，95％的推变化影响code，而不是数据库。如果你推的20件事给Web服务器，当月和1到数据库中，你几乎失去很多，如果你不是推21事到Web服务器，和零到数据库中。

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.

更容易code审查。

你能解释一下怎么样？我不明白这一点。尤其看到作为存储过程可能不在源控制，因此无法通过基于Web的单片机浏览器进行访问等。

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.

Storedprocs住在数据库中，这似乎对外界作为黑盒子。就像希望把他们的源代码控制简单的事情变成了一场噩梦。

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.

还有纯粹的努力的问题。它可能是有意义的所有事情都分解成<一个href=\"http://ptrthomas.word$p$pss.com/2006/06/06/java-call-stack-from-http-upto-jdbc-as-a-picture/\">million层的，如果你想证明你的CEO为什么它只是花费他们700万美元建立一些论坛，但在其他方面创造的每一件小事一个storedproc是没有好处只是额外donkeywork。

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.

这篇关于什么利弊保持在SQL存储的特效与code的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！