STORED PROCEDURES: GOOD OR BAD(存儲過程：好還是壞)
2024-07-21 02:07:07
供稿：網友
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.
我猜想這兩個方面都有它們的優點和缺點，我認為最好的解決方法就是一分為二的看待問題。所有的insert、update、select等都可以在能讓我使用的sql server的xml更新功能的存儲過程中完成，而把商業業務邏輯放到能讓我輕松調試和擴展的組件中去。
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 “yukon”與asp.net “whidbey”都針對這個問題作了討論。yukon支持clr。這也就意味著我們可以在具有強大的調試功能的vs.net里，使用任意的.net語言來替代t-sql來編寫存儲過程。在asp.net “whidbey”中，有計劃提出一種稱為provider的可擴展的方法。這種新的provider model會支持許多新的特性，例如membership、 personalization、role manager、site navigation、build providers和health 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.
我一直在等待它們各自的版本的發布。無論成功與否，時間會證明一切。
