STORED PROCEDURES: GOOD OR BAD(存储过程:好还是坏)

翻译 2004年10月10日 13:49:00

Author

Date Of Submission

User Level

Saikalyan Prasad Rao

07/06/2004

Intermediate

作者

提交日期

用户级别

Saikalyan Prasad Rao

07/06/2004

中级

 

I am sure this issue has been taken up and discussed in lots of articles on the net. This article aims to look at both sides of the coin. First we will dwell on the advantages of Stored Procedures.

我确信这个问题在网上已经被讨论多次了。这篇文章是从两方面讨论。首先我们先详细论述一下存储过程的优点。

Stored Procedures provide performance benefits such as local to database, pre-compiling and caching, a programming framework with use of input/output parameters, reuse of procedures and security feature such as encryption and privilege limits to users. A part from that it offers modularization of code and changes are immediately affected unlike business components which need to be recompiled and deployed. Not forgetting that with the advent of .Net, deployment issues have been reduced quite a lot. But nevertheless changes made to any component do need to be rebuilt. The other benefits include saving on round trips to the client apps and reduction of network traffic.


存储过程提供诸如数据本地化、预处理和缓冲等特点,一个使用输入输出参数的架构,可重复使用存储过程和安全特性作为加密手段和权限设定来限制用户。其中一部分来自它的代码模块化,数据变更的时候不必像商业组件那样需要重新编译和部署。别忘了自.Net的出现后,部署已经被大大简化了,但对任何组件的更改仍需重新编译。另一个好处就是节省了与客户端应用程序的交互时间和加快网络响应。

But on the flip side Stored Procedures do come with its own share of problems. Debugging and maintenance has always been a known issue and it makes it even the more difficult when developers like me get used to VS.Net debugger. On a side note, I do think Microsoft has always built a very good debugger in VS/VS.Net.


但是,存储过程的另一个副作用则产生于它自身。调试与维护已成为一个众所周知的话题,对于像我这样已习惯了VS.Net的调试器的开发者来说,这个可能更为困难。从某个角度来说,我认为微软VS/VS.Net调试器并不总是表现得很好。

Managing changes in stored procedures and applying service pack releases can be a bit teething at times. Apart from this there are issues pertaining to migration. What if your application which was built with SQL Server needs to be ported to Oracle or any other database? It would/is a nightmare converting all those stored procedures and T-SQL specific code to a compatible/ANSI SQL code for that database.


有时存储过程中的变动和应用服务包应用可能有点不便。除了这点之外,还有一个移植方面的问题。如果你的应用程序是用SQL Sever编写的,但现在要移植到Oracle或其他数据库你该怎么办?把所有的存储过程和T-SQL代码转向一个与目标数据库相兼容的SQL代码简直就是个噩梦。

Personally, I would like to go in for stored procedures and leverage most of the database capabilities if I knew my project was going to use a specific database and wouldn’t change. I am sure many must be thinking on the same lines. After all one of the cool features that I liked about SQL Server was its support for XML. You should try doing bulk updates through XML, works like a charm and that too with less amount of code. In fact in .Net, datasets have the capability to output out XML representation of data which saves you the effort of writing code to formulate the XML. Pumping in of Business Logic in Stored Procedures have been done and makes a lot of sense for small projects. But if you want to scale up your application it poses a problem since your database and business logic get tied to your database tier. I am sure for small projects it wouldn’t matter much but for a large scale enterprise level solution this would at some point in time pose a huge problem.


如果我知道我的项目工程使用一个特定的数据库而且不会更改的话,我提倡使用存储过程。我相信很多人也有同样的想法。毕竟
SQL Server有着一个能够支持XML的特性。你可以通过XML来进行大量的数据更新。事实上,在.NET里,数据集能够把已存储的数据以XML的数据表现形式输出XML文件。存储过程中商业逻辑的导入使得很多小项目变得非常容易。但是,如果你想扩展的你应用程序,就会产生一个问题,因为你的数据库和商业逻辑捆绑在你的数据库tier。对于小工程来说这并不重要,但对于大型企业级的解决方案来讲,就可能是个大难题了。

I am sure there will always be two different schools of thoughts on whether or not to use stored procedures. All said and done, it does raise an interesting issue. If we weren’t to use stored procedures, what could be an alternative? Different solutions come to mind such as a generic DB layer component which would have all ANSI SQL statements which would allow one to connect to various databases or the ad-hoc SQL approach. But both of these approaches do come with its share of hurdles and pitfalls. We all know how brittle ad-hoc scripts are since any small change to the database could have sever impacts on your system. Building a generic DB component needs to have a properly designed database which would get affected every time your database changes.


我相信对于是否使用存储过程肯定有不同的意见。这会引起一个很有趣的话题。如果我们不使用存储过程的话,会出现什么替代方法呢?不同的解决方案会导致这样的一个数据库层组件的产生:它拥有所有ANSI SQL表达式来适应多种不同的数据库或是特定的SQL方法。但这两种方法都有共同的缺点。我们都知道,特定的脚本是很脆弱的,对数据库中的任何一个很小的变化都可能影响到你的系统。建立一个通用的数据库组件需要一个设计良好的的数据库来接受外界对数据库的改动。

I guess with both sides having its own share of advantages and disadvantages, I feel the best approach would be is to make best of both the worlds. All insertions, updating, selects etc to be done in stored procedures which would enable me to leverage some of the cool features of SQL Server like XML updates and put the business logic into components which would allow me to easily debug and scale them.


我猜想这两个方面都有它们的优点和缺点,我认为最好的解决方法就是一分为二的看待问题。所有的
InsertUpdateSelect等都可以在能让我使用的SQL ServerXML更新功能的存储过程中完成,而把商业业务逻辑放到能让我轻松调试和扩展的组件中去。

The upcoming release of
SQL Server Yukon” and ASP.Net “Whidbey” aims to address these issues. Yukon is coming up with inbuilt support for CLR. That means we can now code stored procedures in any of the .Net languages which is easier to write than T-SQL and at the same time leverage the powerful debugging features of VS.Net. In ASP.Net “Whidbey” there are plans of introducing a new extensibility point called Providers. This new Provider Model would support many new features likes Membership, Personalization, Role Manager, Site Navigation, Build Providers, and Health Monitoring etc. The Provider Model in ASP.Net Whidbey enables developers to completely un-plug the logic/behavior/data interaction of a particular feature of ASP.Net and replaces it with one’s own logic/data layer. In short the Provider model provides both data and business logic abstraction.


即将发布
SQL Server YukonASP.Net “Whidbey”都针对这个问题作了讨论。Yukon支持CLR。这也就意味着我们可以在具有强大的调试功能的VS.Net里,使用任意的.NET语言来替代T-SQL来编写存储过程。在ASP.Net “Whidbey”中,有计划提出一种称为Provider的可扩展的方法。这种新的Provider Model会支持许多新的特性,例如Membership PersonalizationRole ManagerSite NavigationBuild ProvidersHealth Monitoring等。ASP.Net Whidbey 中的Provider Model可让开发者完全去除ASP.NET中的逻辑/行为/数据交互,取而代之的是一个逻辑/数据层。简言之,Provider Model把数据与商业逻辑抽象化。

Both the upcoming releases have tried to bridge the gaps. I for one am eagerly waiting for their respective releases to happen. Whether successful or not, only time will tell.

我一直在等待它们各自的版本的发布。无论成功与否,时间会证明一切。

stored procedures 存储过程介绍

什么是存储过程呢? The stored procedures is a set of sqls which have been compiled and stored in the web...
  • sbitswc
  • sbitswc
  • 2013年03月04日 22:50
  • 610

Stored Procedure存储过程基础知识详解

存储过程是做项目的必备技术,只要你面试找工作,数据库及存储过程也是必考的,下面一起来了解存储过程的简单基本技术知识: 一、存储过程基本概念? 存储过程就是作为可执行对象存放在数据库中的一个或多个S...
  • u013519551
  • u013519551
  • 2017年05月19日 15:04
  • 623

数据库之存储过程(stored procedure)简述

存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。...
  • xunileida
  • xunileida
  • 2012年04月22日 12:00
  • 1327

ASP与存储过程(Stored Procedures)

 ASP与存储过程(Stored Procedures)     存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。     定义总是很抽象。存储过程其实就是能完成一定操作的一组SQL语...
  • lvlingwy
  • lvlingwy
  • 2007年05月30日 14:52
  • 337

光设计费就上百亿的三峡大坝到底是好还是坏?

光设计费就上百亿的三峡大坝到底是好还是坏?水利水电看了些文章,但字里行间也能明白作者的意思,隔行如隔山,只想说“多思考,少抱怨”。 1、“隔行如隔山” 这句话是发自肺腑的,也是我平时绝少参与其他行...
  • jxgxlm2008
  • jxgxlm2008
  • 2016年08月02日 15:12
  • 706

好人多还是坏人多?

因为我要举个例子,希望我那个同事看见了千万别拿我开刀。我只是想证明这世界上坏人远远要比好人多,或者干脆就没有一个好人,至少说没有一个绝对的好人。有句老话:人非圣贤,孰能无过。想这就是对坏人最好的美化。...
  • snfxwl
  • snfxwl
  • 2009年05月13日 22:17
  • 180

Stored Procedures

  • zgqtxwd
  • zgqtxwd
  • 2008年04月24日 13:43
  • 107

good(),eof(),fail(),bad()0的用法和区别

eof()方法用于判断最后一次读取数据时候是否遇到EOF,若是返回true   fail()方法用于判断最后一次读取数据的时候是否遇到了类型不配的情况,若是返回true(如果遇到了EOF,该方法也返...
  • septemberAs
  • septemberAs
  • 2017年03月30日 16:49
  • 429

STOREDPROCEDURES:GOODORBAD(存储过程:好还是坏)

  • zgqtxwd
  • zgqtxwd
  • 2008年04月27日 03:57
  • 113

c++ good() eof() fail() bad()

// fortest.cpp : 定义控制台应用程序的入口点。 // #include "stdafx.h" #include #include #include #include #inc...
  • h1023417614
  • h1023417614
  • 2015年09月11日 10:06
  • 862
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:STORED PROCEDURES: GOOD OR BAD(存储过程:好还是坏)
举报原因:
原因补充:

(最多只允许输入30个字)