利用SQL Server存储过程提高在线考试系统的性能

随着计算机网络技术和数据库技术的飞速发展，高校的数字化校园建设进程日益深入，其中数字化教学与学习环境建设成为其核心内容。为推动数字化校园建设进程，创建数字化教学环境,利用Delphi7和SQL Server2000开发出适用于多学科的高校通用计算机在线考试软件系统。Delphi在基于C/S模式的数据库应用开发领域具有很强的数据库支持能力，因为它提供了丰富的数据库操作组件，常用的有TQuery、TTable、TADOQuery和TADOTable等[1]。通过这些组件，程序员可以在短时间内完成一个应用程序模块的设计，大大减少了开发人员的工作量。但是，这些组件在操作数据库时却存在局限性，即必须将数据库中的记录从服务器端取出放入到客户端，再根据查询条件进行选择。这种实现方法，如果待访问的数据量较大，势必因为查询时间较长而降低程序执行的速度，从而降低应用程序的性能。解决这一问题利用了SQL Server2000的存储过程来实现[2]。
1 存储过程及其优点
1.1 存储过程的定义
    存储过程是存储在服务器上的一组预先定义并编译好的用来实现某种特定功能的SQL语句。它可以接受参数、返回状态值或参数值。存储过程在服务器端执行，通过应用程序调用的方法实现，执行结束后再把结果返回到客户端。
1.2 存储过程的优点
    （1）存储过程执行速度快，便于提高系统的性能。由于存储过程在第一次执行之后，其执行规划就储存在高速缓冲存储区中，在以后的操作中。只需从高速缓冲存储区中调用编译好的二进制形式存储过程来执行[3]。
    （2）减少网络流量，提高系统执行效率。客户端应用程序调用一个存储过程，只需通过网络发送该过程名和少量入口参数，数据库服务器就可执行该过程，执行完成后，只返回结果状态或将最终结果集给客户端应用程序，无需通过网络传送大量的SQL操作命令和中间结果，最大限度地减少网络通信负担，提高了执行效率。
    （3）维护了数据库的安全性。如果用户被授予执行存储过程的权限，即使该用户没有在执行该存储过程中可参考的表或视图的访问权限，该用户也可以完全执行该存储过程而不受影响。因此，可以通过创建存储过程来完成数据的插入、更新和删除等操作，并且可以通过编程方式控制在操作中对信息的访问权限[4]。
    （4）提高软件系统的可维护性。存储过程在创建时就在服务器上经过调试和编译，可以避免不必要的错误出现。另外，存储过程将相关业务逻辑封装在一起，可以大大提高整个软件系统的可维护性。因为当相关业务逻辑发生变化时，不需要修改并编译客户端应用程序，只需要修改位于服务器端的实现相应业务逻辑的存储过程即可。
    （5）充分增强SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和运算。
    基于以上优点，在进行C/S模式数据库应用系统开发时，通常把比较浪费时间、影响网络传送的相关业务逻辑编写成存储过程由服务器执行，同时利用服务器的SQL语法进行优化处理，只将少量的结果显示在客户端，充分提高软件系统的性能[5]。
2 存储过程的创建方法
     以SQL Server 2000数据库管理系统为背景，创建方法如下：
    CREATE  PROC[EDURE]
    procedure_name[;number]
           [{@parameter data_type}[VARYING][=default] [OUTPUT] ][,...n]
       [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]
       [FOR REPLICATION]
AS sql_statement [...n ]
    下面对语法中各选项进行说明：
    （1）选项“procedure_name”指存储过程名，不能超过128个字符，“[;number]”指程序编号，用于区别同名的存储过程。
    （2）选项“@parameter”表示参数，每个存储过程中最多可以设定1 024个参数，要求在每个参数名前加上符号“@”。
    （3）选项“[=default]”指为对应参数设定默认值。
    （4）选项“OUTPUT”用来指定参数既有输入又有输出。即在调用这个存储过程时，如果所指定的参数是要输入的参数，同时也需要在结果中输出，则该项必须为“OUTPUT”。
    （5）选项“WITH”用来指定存储过程的处理方式。
    （6）选项“FOR REPLICATION”表示只能由复制方式执行[6]。
3 存储过程在考试软件系统中的应用
    （1）考试系统在运行时要经过三个步骤，考生抽取套题编号、抽取套题内容和将套题内容插入答题表进行答题。如果不采用存储过程，则需用下面方法实现。
     ①随机抽取套题编号
    select 套题编号from tblcrecord order by newid()
    ②抽取对应套题内容,套题编号存储在dm.strNum中
      with qry1 do
     Begin
           Close ;
           SQL.Clear ;
        SQL.Add(′Select * from tblTK Where 套题编号
             ='''+dm.strNum +''');
       Open ;
       End;
       ③插入考试题到考试表tblKS中
       qry1.First;
     While not qry1.Eof Do
       begin
             qry2.Append;
             qry2.FieldByName(‘考生学号’).Value:= qry1.Field-
            ByName(‘考生学号’).Value;
         …
         qry2.Post;
       end;
       显然，此种方法步骤繁琐，耗时较多，在考试过程中会产生抽题缓慢的现象，系统性能严重降低。为避免此种情况的发生，考试系统采用了存储过程的方法，代码如下[7]：
       CREATE PROCEDURE cp_Paper_Add
       @MyNo char(10),
       @Mynum char(20)
       AS
       set nocount on
       exec(′insert into tblKS (学生学号、套题编号、试题序号、试题内容、试题答案、试题分数、是否答题、套题名称、考试科目、试题类型、学生分数、类别序号) select''''+@MyNo+'''',''''+@Mynum+''',试题序号,试题内容,试题答案,试题分数,“否”,套题名称,科目名称,试题类型,0,类别序号  from tblTK where  套题编号='''+@Mynum+''' )
       GO
       通过执行存储过程，将题库中与考生抽取套题编号相匹配的记录提取出来，并批量插入到考试表中，同时对考生分数进行了初始化为0的动作。而本考试系统就是将考生的学号和考生抽取的套题编号作为参数传递给存储过程，因此，大幅度提高了考试软件的效率。
       （2）在考试系统进行不同类型题目抽取时，如果在Delphi中直接应用SQL语句，应使用下面代码：
       with cds1 do
       Begin
           Close ;
           SQL.Clear ;
              SQL.Add(′Select * from tblKS Where 学生学号=′);
       SQL.Add(‘ ?苁+dm.strNo +?苁  and 试题类型=′′单项选择题′′ order by 试题序号′);
       Open ;
       End;
    此时，程序是在运行时才编译的。这样就增加了运行的时间，同时还要把完整的SQL语句传递给Sql Server服务器执行，这样考试过程中势必增加网络流量，降低软件的运行效率。
    如果采用存储过程，可用下面方法实现：
    CREATE PROCEDURE sp_Query_All
       @strNo char(10)
       AS
       set nocount on
       Select Select * from tblKS Where 学生学号=@strNO and 试题类型=′′单项选择题′′ order by 试题序号
       GO
    在客户端调用方法如下：
       with cds1 do
       Begin
           Close ;
           SQL.Clear ;
           SQL.Add('exec sp_Query_All :strNO');

         Params.ParamByName('strNO').Value:=DM.strNO;
　    Open ;
　    End;
    此时，存储过程是预先编译的，自然节省了编译的时间，同时只向服务器传递了一个参数DM.strNO，大大减少了网络流量，提高了考试系统的性能。
    （3）在考试系统中添加考生信息时，首先要判断是否存在该考生学号信息，然后再进行添加操作。如果采用普通SQL语句，代码如下：
      with qry1 do
       Begin
           Close ;
           SQL.Clear ;
           SQL.Add(′select * from tblstudents where考生学号=
            :strNO′);
       Params.ParamByName('strNO').Value :=DM.strNO;   
       Open ;
       if recordcount=0 then
       begin
            qry2.Append;
            qry2.FieldByName(‘考生学号’).Value:= DM.strNO;
           …
           qry2.Post;
           End;

     采用存储过程，可以用流控制语句编写。采用存储过程代码如下：
    CREATE PROCEDURE sp_Stud_Add
       @No char(10),
       @Name char(20),
       @Sex char(2),
       @Class char(30),
       @XueYuan char(20),
       @XH smallint
        AS
       if not exists (Select *  from tblStudents where 学生学号=
        @No)
        begin
     insert into tblStudents  values(@No,@Name,@Sex,@Class,
        @XueYuan,@XH)
       end
       GO
    通过if 流控制语句实现了相同学号不能插入的功能，增强了SQL语言的功能和灵活性，避免了普通SQL语句的繁琐与低效，提高了考试系统的性能。
    在开发数据库应用系统时，合理使用存储过程来完成相关的业务逻辑操作，能够显著提高系统的性能、运行效率和可维护性。当然，并非所有的业务逻辑都要使用存储过程来实现。如果都使用存储过程的方法实现，一定会增加服务器的负担；同时，客户端的资源也不能得到充分的利用[8]。因此，要根据具体情况，将那些使用频繁、比较复杂的业务逻辑用存储过程的方法实现，合理规划服务器端和客户端程序，使相关事务能够均衡处理，以此提高软件系统的性能和效率。
参考文献
[1] 吴小林，蒋先刚，高艳锦.基于Delphi的多层数据库应用系统连接技术的研究[J].华东交通大学学报，2005，22（1）：68-70.
[2] 史创明.SQL Server 2000中文版数据库管理与应用标准教程[M].北京：清华大学出版社，2006：264-278.
[3] 张莉，陈雷，孙龙清,等.SQL Server数据库原理及应用（第2版）[M]. 北京：清华大学出版社，2009：265-275.
[4] 李舒，陈丽君.高校学生成绩管理系统的设计与实现[J].辽宁大学学报.自然科学版，2006，33（3）：284-285.
[5] 曾毅，王玉萍.SQL Server数据库中存储过程的实现[J].科技信息，2008(25)：88-89.
[6] 肖辉辉，段艳明.SQL Server 2000存储过程在VB中的应用[J].福建电脑，2007(12)：189-190.
[7] 马晓梅.SQL Server 2000实验指导（第2版）[M].北京：清华大学出版社，2008：105-112.
[8] 孙晓枫，范正翘，袁海文.存储过程在SQL Server数据库自我管理中的高级应用[J].计算机应用，2002，22（4）：92-93.