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 solut