应用程序需要使用存储过程?

问题的提出 

    当你在开发一个基于数据库的应用的时候，你可能会想这样一个问题：数据库有关的事务操作部分因该放在那里？是以存储过程（stored procedure）的形式放在数据库端呢，还是将查询以及相应运算嵌在应用程序当中呢？要回答这样一个问题，你首先要了解存储过程以及内嵌T-SQL这两种方案各自的优缺点以及他们分别适用的场合。尤其是在新的.NET开发环境中，如何选取正确的解决方案是非常关键的。 

为什么要使用存储过程（Stored Procedures）? 

    你可能对数据库编程已经有一些经验了，对SqlCommand对象也比较熟悉了。但是你是否想过你的这些数据库相关的操作是不是符合优化原则呢？那些相对复杂的数据处理是应当嵌在应用程序中呢，还是把它封装在存储过程中，放置在数据库端？
    再展开讨论之前，让我们先简要的回顾一下存储过程的概念。
    存储过程是一组T-SQL语句，它们存放在一起形成一段T-SQL程序。在运行的时候，你可以传入一些参数；你得到的可以是结果集合（result set），也可以是输出参数（output parameters），甚至是返回值（return value）。存储过程在第一次被执行的时候，数据库系统要首先对它进行分析和编译。编译后得到了一个执行计划（execution plan）。所谓执行计划就是数据库具体执行这个存储过程的先后步骤的过程纪录。这个编译得到的执行计划被放置到数据库的缓存池中以备以后再次使用。如果这个存储过程今后再次被调用，那么数据库将从缓存池中取出这个执行计划来运行。这样就避免了重复对该存储过程进行再次分析和编译，从而提高了数据库的性能。（这些缓存池中的执行计划将一直被保存着，直到数据库重新启动或是系统内存不够用而被清除出缓存池）。
    是否使用存储过程，我们可以从以下三个方面来进行分析。 

一．性能（Performance）
    在以前，存储过程比查询（Query）有性能上的优势。原因是数据库会缓存存储过程的执行计划（execution plan）。但是在最近的SQL数据库7.0以后的版本中，查询的执行计划也被数据库放到缓存池中。这样一来存储过程的传统优势就不再存在了。只要你的查询语句是静态的（static）并且你经常使用它，这样他就不会被数据库清理出缓存池去。如果它的执行计划得到重用，那么理论上讲查询和存储过程没有性能上的区别。要注意的一点是，查询的语句必须保持静态，如果你更动了一些，哪怕是很不重要的一个部分，那么这个查询很可能在缓存池里找不到匹配的执行计划。那么查询只好被数据库重新编译，这将导致性能上的损失。
    不过在网络传输方面，存储过程比查询仍然占有优势。因为使用存储过程只需要向数据库传递存储过程的名字和必要的参数，而不是像查询那样要传输全部查询语句。如果查询逻辑复杂的话，那么查询语句的大小也将会比较可观。另外，设计合理的存储过程可以减少客户端和数据库端之间的往返，甚至减少到一次。
    另外，通过使用远端过程调用（remote procedure calls, RPC）来执行数据库端的存储过程可以加强执行计划的重用性，从而提高性能。当你指定SqlCommand.CommandType为StoredProcedure的时候，存储过程是通过RPC来执行的。RPC包装整理参数然后调用数据库的存储过程的方式使得数据库引擎非常容易发现匹配的执行计划。你在调用该存储过程时可以使用了不同的参数，数据库系统将会使用同一个执行计划的。
    在决定是否使用存储过程的时候，你还要判断你的特定操作是不是利用了存储过程的长处。总体来说： 

" 基于集合的运算（Set-Based）是T-SQL的强项
" 基于行的运算（Row-Based）以及基于字符串的运算（String manipulation）不是T-SQL的强项。至少在下一个版本Yukon数据库出来之前，你应当避免这样的操作。 

    也就是说，有些操作由应用程序的高级语言来完成往往会比数据库来往成更有效。比如比较复杂的字符串处理。这时候，将所有的操作全部放到存储过程中就不是一个最优化的办法。你可能要合理的划分任务，让数据库和应用程序各自完成其擅长的任务。 

二．可维护性和抽象能力（Maintainability and Abstraction）
    使用存储过程另外一个潜在的好处就是可维护性好。尽管我们希望数据库结构永远不要变动，事务处理规则也永远保持不变，但事实上这是不大可能发生的。对于好多更动，你也许只需要更改存储过程的具体实现就可以完成。所有使用它的客户端程序就不需要重新修改，调试和编译。这样很多变动对于客户程序来说就是透明的（transparent）。在大多情况下，这种办法往往是最有效和最简单的。
    另外，通过抽象具体实现（implementation）和将T-SQL语句放在存储过程中，可以使任何客户端调用者以一个统一的形式来访问数据。从全局上看，一种数据操作运算只有一种实现，放在一个地方。这样更改和维护都将非常方便。不同的用户也将永远得到同一样的结果。
    使用存储过程的另外一个维护性方面的好处是你可以有更好的程序版本控制。你可以使用版本控制的软件来帮助你维护存储过程，就象你维护其它源程序那样。比如，你可以使用微软的Visual SourceSafe?来帮你做到这一点。这样你可以很方便的找回以前任何一个版本的存储过程。
    需要指出的一点是使用存储过程不能防止你修改数据库结构和事务处理规则。如果更动比较大，需要重新设计传入的参数或者返回值，那么你将需要修改客户端调用这些存储过程的程序段。
    你应该考虑到使用存储过程来封装你的事务处理逻辑将影响应用的可移植性。存储过程是和SQL数据库捆绑在一起的，如果你想更换数据库平台，你可能要重写这些存储过程。如果可移植性对你的应用的是非常关键的，那么将事务处理逻辑放在数据库系统中立（RDBMS-neutral）的中间层（middle-tier）比较好。 

三．安全性（Security） 

    最后一个使用存储过程的原因是它可以增强数据库系统的安全性。
    从管理用户访问信息角度来讲，它可以通过让用户访问一定的存储过程来保证用户可以访问特定的数据，这是一种间接的数据访问，而不是直接对用户开放式据库表格。其实我们可以将存储过程假想为数据库系统的View。唯一的区别就是存储过程可以变更参数而使得结果动态变化。
    存储过程还可以让你在程序安全性方面有所改进。它可以防备一种叫做SQL注入式的攻击（SQL injection attacks）- 这种攻击主要是用AND或是OR运算符将命令拼接在有效的输入参数之后。存储过程还可以隐藏事务处理规则于数据库端，而不是放在客户程序端。在有些情况下（比如涉及到知识产权等等），这种隐藏是非常重要的。
    此外，使用存储过程可以让你使用ADO.NET提供的SqlParameter类。你可以使用这个类来说明具体的参数类型。这使得你更加容易来验证用户输入的参数是否合法。参数对于存储过程和in-line查询来说是同样重要的，它可以将用户的输入降低到一个很小的范围内。
    当然使用存储过程并不意味着安全方面你可以高枕无忧了。事实上，不好的程序以及数据库管理方面的漏洞仍然可以将你置于可能的攻击之中。如果对SQL数据库的角色（Role）创建以及授权不当，那么将导致用户可以访问一些他们本不应该访问的数据。同样，仅仅使用存储过程并不能完全保证不受SQL注入式攻击。
    另外使用SqlParameter类来校验用户输入也不是绝对安全的，不管是在后台T-SQL写的存储过程中还是嵌在应用中的查询，所有用户的输入，尤其是字符串类型的数据，一定要在交给数据库引擎处理之前进行有效性的校验。 

存储过程适用于你吗？ 

    综上所述，使用存储过程有如下几个突出优点： 

" 提高新能，减少了网络流量 
" 在数据库端一点的维护（single point of maintenance ）
" 抽象和概化业务逻辑，增强了一致性和安全性
" 减少了一些可能的恶意攻击的机会
" 鼓励执行计划的重用性（Encourage execution plan re-use ） 

    如果你的应用程序能有效的利用存储过程的上述优点，那么你就应该尽量使用。但是如果你的应用要求有很高的可移植性，或者数据库的结构变动很大，不能相对稳定下来，那么你可能要试一试其他方法了。比如你现在在SQL数据库上为用户开发一个早期可行性验证程序，今后用户很可能使用MySQL或是Oracle等其它数据库，那么你就因该避免使用SQL数据库的存储过程，而使用程序内嵌的数据库操作语句。这样当你更换数据库平台的时候，可以极大的保证程序不受影响。
    另外，你还要考虑使用存储过程的技术问题。也许你和你的手下非常不熟悉存储过程编程，并且没有时间去很快掌握它。这些因素你也需要通盘考虑。另外如前所述，数据库存储过程擅长于基于集合（set-based）的操作，而不擅长基于行（row-based）的操作。如果你对存储过程没有很好的了解，而不正确的使用了它往往会导致很不好的执行性能。所以如果你决定使用存储过程，那么多花一些时间来学习它是很有必要的。