SQL Server 数据库管理系统上的SQL查询优化
摘要
 人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后，其运行速度将有比较明显的提高。
 为了最有效地优化 Microsoft SQL Server 的性能，您必须明确在哪些方面性能可以得到最大程度的改进，并集中分析这些方面。否则，您在这些问题上可能花费大量的时间和精力，而并不能得到明显的性能上的提高。
 设计一个应用系统似乎并不难，但是要想使系统达到最优化的性能并不是一件容易的事。在开发工具、数据库设计、应用程序的结构、查询设计、接口选择等方面有多种选择，这取决于特定的应用需求以及开发队伍的技能。
关键词：SQL Server数据库 性能优化 查询
 数据查询 查询效率 安全性
 
 
 
 
 
 
 
 
 
 
 
 
 
 
ABSTRACT

 People will often fall into a ill idea while using SQL, namely it is correct to pay close attention to the result to the income very much, and has neglected the performance difference that may exist between different implementation methods , this kind of performance difference behaves particularly obviously in the large-scale or complicated database environment (such as on-line transaction processing OLTP or DSS ). I find bad SQL come from appropriate index design , fill connection terms and where clause that can't be optimized of copy often among working practice. After carrying on proper optimization to them, its speed of operation improves obviously!
 In order to optimize the performance of Microsoft SQL Server most effectively, you must define in which respects performance can get maximum improvement , concentrate on analysing these respects. Otherwise, you are on these questions may spend a large amount of time and energy , but can not receive the improvement on obvious performance .
 It seems easy to design a application system, but it is not an easy thing to want to make the system reach the performance optimized most. Respect of choosing on developing instrument , database design , structure , inquiry design , interface of the application program etc. has excellent choices, this depends on specific application demand and develops the skill of the team .
    Keyword: SQL Server database  Performance optimizing  Inquiry   Data inquiry  Inquire about efficiency  Security 

目  录
摘 要-------------------------------------------------------------------------------------------1
ABSTRACT-----------------------------------------------------------------------------------2
第一章 综述 (绪论) ------------------------------------------------------------------------4
引言----------------------------------------------------------------------------------4
数据库优化概述---------------------------------------------------------------------8
第二章 设计数据库-------------------------------------------------------------------------8
熟悉业务系统----------------------------------------------------------------------- 9
规范化与逆规范化------------------------------------------------------------------10
选择数据类型---------------------------------------------------------------------------------10
选择索引---------------------------------------------------------------------------------------14
第三章 查询优化----------------------------------------------------------------------------15
主键---------------------------------------------------------------------------------------------15
通配符------------------------------------------------------------------------------------------19
视图---------------------------------------------------------------------------------------------19
存储过程---------------------------------------------------------------------------------------20
第四章 总结----------------------------------------------------------------------------------21
致谢-------------------------------------------------------------------------------------------- 21

第一章  综述
 SQL Server 是一个后台数据库管理系统，他功能强大、操作简便，日益为广大数据库用户所喜爱，越来越多的开发工具提供了与SQL Server 的接口。了解和掌握SQL Server 的功能，对于一个数据库开发管理人员来说非常必要。
 SQL Server是一种高性能的大型关系型数据库管理系统，广泛的应用在C/S和B/S体系结构的数据库系统中。
 评价系统性能优化的标准有：吞吐量、响应时间、并行能力等。本文主要探讨如何优化SQL，取得最快的系统响应速度。
1.1引言
 在一个大型的数据库中，性能成为人们关注的焦点之一，如何让数据库高效有效的运行成为广大数据库管理人员和开发人员必须要考虑的问题。
何谓“好性能”
性能是一个应用或多个应用在相同的环境下运行时对效率的衡量。性能常用响应时间和工作效率来表示。响应时间是指提交任务到得到返回结果之间的时间开销，含完成一个任务花费的时间。可以从以下三方面来减少响应时间：
 · 减少竞争和等待的次数，尤其是磁盘读写等待次数
 · 用更快的部件
 · 减少利用资源所需的时间
 绝大多数性能的获得来自于优秀的数据库设计、精确的查询分析和适当的索引。最好性能的获得能够通过确立优秀的数据库设计，在开发时使用SQL Server查询优化器来实现。为了取得更好的数据库性能，我们就需要对数据库进行优化，减少系统资源的竞争，如对数据cache,过程cache，系统资源和CPU的竞争。
1.2数据库优化概述
 在SQL Server中，有如下优化层次：
应用层——大部分性能的获得来自于对SQL应用中查询的优化，这必须是以好的数据库设计为基础的。
数据库层——应用共享在数据库层中的资源，这些资源包括硬盘，事务日志和数据cache。
服务器层——在服务器层有许多共享的资源，包括数据高速缓存，存储过程高速缓存，锁，CPU等。
设备层——指的是存储数据的磁盘及其控制器，在这一层，你应尤其关注磁盘的I／O。
网络层——指连接用户和SQL Server的网络。
硬件层——指可利用的CPU。
操作系统层——理想地，SQL Server是一台机器的唯一主要应用，它必须和操作系统以及其他sybase软件，如Backup Server或SQL Server Monitor共享处理器、内存以及其他资源。在大多数情况下面，我们是对应用层进行优化，因为对应用性能的优化是设计和编程人员乐于接受的功能，其结果能被观测及检验。查询的性能是SQL应用的整个性能的一个关键。应用层上的问题包括以下内容：
决策支持DSS.和在线事务处理(OLTP)需要不同的性能策略
事务设计能够减少并发，因为长的事务保持占用锁，也就减少了其他用户对相关数据的存取
关联一致性保证的策略。对数据查询修改时需要考虑join操作对性能的影响
索引可以改善查询性能，但也会增加修改数据的时间
为了安全而设立的审计限制了性能
 在应用层优化的选项包括：
远程处理或复制处理能够把决策支持从OLTP机器中分离出来
利用存储过程来减少编译时间和网络的利用
利用最少量的锁去满足你的应用需要
 数据库层的问题包括：
建立备份和恢复方案
在设备上分布存储数据
审计操作影响性能；仅审计你所需的
日常的维护活动将导致性能的降低和导致用户不能操作数据库表
 在数据库层上优化选择包括：
利用事务日志的阀值来自动转储事务日志防止其超出使用空间
在数据段中用阀值来监视空间的使用
利用分区来加速数据的装入
对象的定位以避免硬盘的竞争
把重要表和索引放入cache中，保证随时取得
 服务器层的问题有：
应用的类型——服务器是支持OLTP还是DSS，或者两者都支持
所支持的用户数影响优化决策——随着用户数的增加，对资源的竞争会发生改变
网络负荷
当用户数和事务数达到一定的数量时复制服务器或其他分布式处理是一个解决的方法
 服务器层的优化的选项包括：
优化内存——一个关键的配置参数和其他方面的参数
决策是客户端处理还是服务器端处理——有些处理能在客户端进行吗
配置cache的大小和I／O的大小
增加多个CPU
为空闲时间排定批处理任务和生成报表
工作负荷发生改变，重新配置特定参数
决定是否可能把DSS移到另一个SQL服务器中设备层
 设备层的问题包括：
主设备、包含用户数据库的设备，用户数据设备，或数据库日志是否要镜像
怎样在设备之间分布系统数据库、用户数据库和数据库日志
为获得对堆表插入操作的高性能，是否有必要进行分区
 设备层上优化的选项包括：
用多个中等大小的设备及多个控制器可能比用少量的大设备有更好的I／O性能
分布数据库，表和索引以在不同的设备上进行I／O装载
网络层
实际上，SQL Server的所有用户都是通过网络存取他们的数据。网络层上的主要问题有：
网络的流量
网络的瓶颈
网络的速度
网络层上优化的选项包括：
配置包的大小，以使其与应用的需要相匹配
配置子网
分隔出繁忙的网络运用
创建一个高容量的网络
配置多个网络引擎
更好地设计应用，限制所需的网络传输
硬件层
在硬件层上的问题包括
CPU的效率
磁盘的存取：控制器和磁盘
磁盘备份
内存的使用
在硬件层上优化的选项包括：
增加CPU以适应工作负荷
配置调度程序以提高CPU利用率
遵循多处理器应用设计指导以减少竞争
配置多个数据cache操作系统层
操作系统层的主要问题有：
文件系统——是否被SQL Server独占使用
内存管理——精确估算操作系统和其他程序的内存占用
CPU的利用——整个系统共有多少处理器可用?有多少分配给SQL Server
在操作系统层优化的选项包括：
网络接口
在文件和原始分区之间选择
增加内存
把客户操作和批处理移到其他机器上
SQL Server利用多个CPU

第二章 设计数据库
 要在良好的SQL Server方案中实现最优的性能，最关键的是要有一个很好的数据库设计方案。在实际工作中，许多SQL Server方案往往是由于数据库设计得不好导致性能很差。所以要设计好数据库就要从以下几点来讨论。
2.1 熟悉业务系统
 对业务系统的熟悉程度对整个数据库系统的性能有很大影响，一个对业务不熟悉的设计人员，尽管有丰富的数据库知识，也很难设计出性能最佳的数据库应用系统。所以我们在做“三江学院师资管理系统”这个项目前跟指导老师和用户收集了充足的资料，通过这些资料和经过我们的分析，我在底层数据库建立了13张数据库表，它包括教师表（Teacher）、党派表（Clan）、用户表（DBA）、入党表（JoinClan）、类别表（Kind）、学位学历表（konwledgeDegree）、进修情况表（operation）、联系电话表（Phone）、兼职情况表（Plurality）、工作简历表（Resume）、任职情况表（SchoolDuty）、任课情况表（SchoolTeach）、学科表（Subject）。
 通过这些表我建立了E-R图

                                   m

                           
   
                                               
         m             1                         1                  m

                                                                    
      m             1             n           1                  m
                              
                                            
      m              1              m        1                  m

                                             
      m            1                          1                  m

                                   1

 
2.2 规范化与逆规范化
 数据库被规范化后，减少了数据冗余，数据量变小，数据行变窄。这样SQL Server 2000的每一页可以包括更多行，那么每一区里的数据量更多，从而加速表的扫描，改进了单个表的查询性能。但是，当查询涉及多个表的时候，需要用很多连接操作把信息从各个表中组合在一起，导致更高的CPU和I/O花销。那么，有很多时候需要在规范化和逆规范化之间保持平衡，用适当的冗余信息来减少系统开销，用空间代价来换取时间代价。有查询教师全部信息表TeacherInfo，它里面记录了教师基础信息，教师类别信息，教师兼职信息，教师任课信息，教师入党信息，教师任职信息，教师学科信息，教师进修情况信息…..这些信息分别在教师表、类别表、兼职情况表、任课情况表、入党表、任职情况表、学科表、进修情况表中存放。如果按照规范化的要求，TeacherInfo查询时就必须要与这么多个表进行连接或者嵌套查询。如果TeacherInfo表中的数据量是在百万级的，那么在一般配置的服务器上一次复杂查询所需要的时间可能会达到好几分钟。事实上，只要在设计时保证数据的逻辑有效性，很多信息都可以直接冗余在TeacherInfo表中，这些冗余的数据能够极大的提高查询的效率，从而减少CPU和I/O操作。
2.3 选择数据类型
 对每一属性选择什么样的数据类型很大程度上依据表的要求，但是在不违背表要求的前提下，选择适当的数据类型可以提高系统性能。比如有text列存放一本书的信息，用BLOB而不是character(1024)，BLOB存放的是指针或者文件参照变量，真正的文本信息可以放在数据库之外，从而减少数据库缓存空间，使得程序运行的速度提高。用户可以根据自己的需要定义自己的数据类型。
2.4  选择索引
 在应用系统中,尤其在联机事务处理系统中,对数据查询及处理速度已成为衡量应用系统成败的标准。而采用索引来加快数据处理速度也成为广大数据库用户所 接受的优化方法。在良好的数据库设计基础上，能有效地使用索引是SQL Server取得高性能的基础，SQL Server采用基于代价的优化模型，它对每一个提交的有关表的查询，决定是否使用索引或用哪一个索引。因为查询执行的大部分开销是磁盘I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读表的每一个数据页，如果有索引指向数据值，则查询只需读几次磁盘就可以了。所以如果建立了合理的索引，优化器就能利用索引加速数据的查询过程。但是，索引并不总是提高系统的性能，在增、删、改操作中索引的存在会增加一定的工作量，因此，在适当的地方增加适当的索引并从不合理的地方删除次优的索引，将有助于优化那些性能较差的SQL Server应用。实践表明，合理的索引设计是建立在对各种查询的分析和预测上的，只有正确地使索引与程序结合起来,才能产生最佳的优化方案。本文就SQL Server索引的性能问题进行了一些分析和实践。
聚簇索引(clustered indexes)的使用
 聚簇索引是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。建立聚簇索引的思想是：
大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个事务的环境中，对最后一页的封锁严重影响系统的吞吐量。
在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(between、<、<=、>、>=)或使用group by或order by的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。
在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列(如IDENTITY)上，否则会经常引起封锁冲突。
在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。
选择聚簇索引应基于where子句和连接操作的类型。聚簇索引的侯选列是：● 主键列,该列在where子句中使用并且插入是随机的。
 ● 按范围存取的列，如age > 31 and pri_order <= 35 。
 ● 在group by或order by中使用的列。
 ● 不经常修改的列。
 ● 在连接操作中使用的列。
非聚簇索引(nonclustered indexes)的使用
 SQL Server缺省情况下建立的索引是非聚簇索引，由于非聚簇索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面。换句话说非聚簇索引具有在索引结构和数据本身之间的一个额外级。一个表如果没有聚簇索引时,可有250个非聚簇索引。每个非聚簇索引提供访问数据的不同排序顺序。在建立非聚簇索引时，要权衡索引对查询速度的加快与降低修改速度之间的利弊。另外，还要考虑这些问题：
 ● 索引需要使用多少空间。
 ● 合适的列是否稳定。
 ● 索引键是如何选择的，扫描效果是否更佳。
 ● 是否有许多重复值。
 对更新频繁的表来说，表上的非聚簇索引比聚簇索引和根本没有索引需要更多的额外开销。对移到新页的每一行而言，指向该数据的每个非聚簇索引的页级行也必须更新，有时可能还需要索引页的分理。从一个页面删除数据的进程也会有类似的开销，另外，删除进程还必须把数据移到页面上部，以保证数据的连续性。所以，建立非聚簇索引要非常慎重。非聚簇索引常被用在以下情况:
 ● 某列常用于集合函数(如Sum,....)。
 ● 某列常用于join,order by,group by。
 ● 查寻出的数据不超过表中数据量的20%。
三、覆盖索引(covering indexes)的使用
 覆盖索引是指那些索引项中包含查寻所需要的全部信息的非聚簇索引，这 种索引之所以比较快也正是因为索引页中包含了查寻所必须的数据,不需去访问数据页。 如果非聚簇索引中包含结果数据,那么它的查询速度将快于聚簇索引。
 但是由于覆盖索引的索引项比较多,要占用比较大的空间。而且update 操 作会引起索引值改变。所以如果潜在的覆盖查询并不常用或不太关键，则覆盖索引的增加反而会降低性能。
 索引的有无，建立方式的不同将会导致不同的查询效果，选择什么样的索引基于用户对数据的查询条件,这些条件体现于where从句和join表达式中。一般来说建立索引的思路是：
 (1)、主键时常作为where子句的条件，应在表的主键列上建立聚簇索引，尤其当经常用它作为连接的时候。
 (2)、有大量重复值且经常有范围查询和排序、分组发生的列，或者非常频繁地被访问的列，可考虑建立聚簇索引。
 (3)、经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列，如果可能尽量使关键查询形成覆盖查询。
 (4)、如果知道索引键的所有值都是唯一的，那么确保把索引定义成唯一索引。
 (5)、在一个经常做插入操作的表上建索引时，使用fillfactor(填充因子)来减少页分裂，同时提高并发度降低死锁的发生。如果在只读表上建索引，则可以把fillfactor置为100。
 (6)、在选择索引键时，设法选择那些采用小数据类型的列作为键以使每个索
 引页能够容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必须遍历的索引页面降到最小。此外，尽可能地使用整数为键值，因为它能够提供比任何数据类型都快的访问速度。
引的维护
 上面讲到,某些不合适的索引影响到SQL Server的性能,随着应用系统的运行,数据不断地发生变化,当数据变化达到某一个程度时将 会影响到索引的使用。这时 需要用户自己来维护索引。索引的维护包括：
重建索引
 随着数据行的插入、删除和数据页的分裂，有些索引页可能只包含几页数据，另外应用在执行大块I/O的时候，重建非聚簇索引可以降低分片，维护大块I/O的效率。重建索引实际上是重新组织B-树空间。在下面情况下需要重建索引：
 (1)、数据和使用模式大幅度变化。
 (2)、排序的顺序发生改变。
 (3)、要进行大量插入操作或已经完成。
 (4)、使用大块I/O的查询的磁盘读次数比预料的要多。
 (5)、由于大量数据修改，使得数据页和索引页没有充分使用而导致空间的使用超出估算。
 (6)、dbcc检查出索引有问题。
 当重建聚簇索引时,这张表的所有非聚簇索引将被重建.
索引统计信息的更新
 当在一个包含数据的表上创建索引的时候，SQL Server会创建分布数据页来存放有关索引的两种统计信息：分布表和密度表。优化器利用这个页来判断该索引对某个特定查询是否有用。但这个统计信息并不动态地重新计算。这意味着，当表的数据改变之后，统计信息有可能是过时的，从而影响优化器追求最有工作的目标。因此，在下面情况下应该运行update statistics命令：
 (1)、数据行的插入和删除修改了数据的分布。
 (2)、对用truncate table删除数据的表上增加数据行。
 (3)、修改索引列的值。
 

第三章 查询优化
3.1  主键
 主键用整型会极大的提高查询效率，而字符型的比较开销要比整型的比较开销大很多，用字符型数据作主键会使数据插入、更新与查询的效率降低。数据量小的时候这点降低可能不会被注意，可是当数据量大的时候，小的改进也能够提高系统的响应速度。
 虽然整型能提高查询效率，但在具体的方案中考虑到教师工号的唯一性和我们所做软件的数据量，我们还是牺牲了这一效率，采用了字符型数据作主键。
3.2  通配符
 在数据库管理系统中，查询是一个很重要的内容。然而，在多数情况下人们不能准确知道作为查询条件的字段内容，如：在“师资管理系统”中要查询教师内容，通过教师工号，教师类别号，教师姓名，教师学科号之中的一个或多个查询，而查询者可能只知道其中的姓或名，这时，为保证能查到满足条件的数据记录，只能进行模糊查询。以下是我在“师资管理系统”中为查询教师的存储过程的代码：
CREATE procedure seachTeacher
@teacherID varchar(10),
@kindID  char(2),
@name varchar(12),
@subjectID varchar(8)
as
if (@teacherID  is not null and @kindID is not null and @name is not null and @subjectID is not null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where a.teacherID=@teacherID  and b.kindID=@kindID and a.name like ('%'+ @name + '%') and c.subjectID=@subjectID
order by a.teacherID
end
if (@teacherID is null and @kindID is not null and @name is not null and @subjectID is not null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where  b.kindID=@kindID and a.name like ('%'+ @name + '%') and c.subjectID=@subjectID
order by a.teacherID
end
if (@teacherID is not null and @kindID is  null and @name is not null and @subjectID is not null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where a.teacherID=@teacherID  and a.name like ('%'+ @name + '%') and c.subjectID=@subjectID
order by a.teacherID
end
if (@teacherID is  null and @kindID is  null and @name is not null and @subjectID is not null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where  a.name like ('%'+ @name + '%') and c.subjectID=@subjectID
order by a.teacherID
end
if (@teacherID is not null and @kindID is not null and @name is  null and @subjectID is not null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where a.teacherID=@teacherID and b.kindID=@kindID  and c.subjectID=@subjectID
order by a.teacherID
end
if (@teacherID is  null and @kindID is not null and @name is  null and @subjectID is not null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where  b.kindID=@kindID  and c.subjectID=@subjectID
order by a.teacherID
end
if (@teacherID is not null and @kindID is  null and @name is  null and @subjectID is not null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where a.teacherID=@teacherID  and c.subjectID=@subjectID
order by a.teacherID
end
if (@teacherID is  null and @kindID is  null and @name is  null and @subjectID is not null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where  c.subjectID=@subjectID
order by a.teacherID
end
if (@teacherID is not null and @kindID is not null and @name is not null and @subjectID is  null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where a.teacherID=@teacherID and b.kindID=@kindID and a.name like ('%'+ @name + '%')
order by a.teacherID
end
if (@teacherID is  null and @kindID is not null and @name is not null and @subjectID is  null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where  b.kindID=@kindID and a.name like ('%'+ @name + '%')
order by a.teacherID
end
if (@teacherID is not null and @kindID is  null and @name is not null and @subjectID is  null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where a.teacherID=@teacherID  and a.name like ('%'+ @name + '%')
order by a.teacherID
end
if (@teacherID is  null and @kindID is  null and @name is not null and @subjectID is  null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where  a.name like ('%'+ @name + '%')
order by a.teacherID
end
if (@teacherID is not null and @kindID is not null and @name is  null and @subjectID is  null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where a.teacherID=@teacherID and b.kindID=@kindID
order by a.teacherID
end
if (@teacherID is  null and @kindID is not null and @name is  null and @subjectID is  null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where  b.kindID=@kindID
order by a.teacherID
end
if (@teacherID is not null and @kindID is  null and @name is  null and @subjectID is  null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
where a.teacherID=@teacherID
order by a.teacherID
end
if (@teacherID is  null and @kindID is  null and @name is  null and @subjectID is  null)
begin
select a.teacherID,b.kindName,a.name,c.subjectName from Teacher as a join Kind as b on a.kindID=b.kindID join Subject as c on a.subjectID=c.subjectID
order by a.teacherID
end
GO
大家可以从以上代码中看到有4个参数就有16中情况的可能性，这大大的影响了查询的效率，在性能上是不可取的，怎么可以提高效率又可以满足上面的查询要求呢，我们这里可以用通配符来达到这种效果。在“师资管理系统”中要查询教师学科信息，有6个参数，那么就要有64个可能性，如果象上面那样做的话，那么代码太复杂了。而我用了通配符就比较简单，以下是我写的代码：
CREATE procedure subjectpro
@subjectID varchar(8),
@subjectName varchar(20),
@subjectKind varchar(10),
@isBase  char(2),
@department varchar(20)
as
declare @subjectID1 VARCHAR(10),
 @subjectName1 varchar(22),
 @subjectKind1 varchar(12),
 @isBase1  varchar(4),
 @department1 varchar(22)
set @subjectID1 = case when @subjectID is null then '%' else '%'+@subjectID+'%' end;
set @subjectName1 = case when @subjectName is null then '%' else '%'+@subjectName+'%' end;
set @subjectKind1= case when @subjectKind is null then '%' else '%'+@subjectKind+'%' end;
set @isBase1 = case when @isBase is null then '%' else '%'+@isBase+'%' end;
set @department1 = case when @department is null then '%' else '%'+@department+'%' end;
select * from Subject
where (subjectID like @subjectID1) and
      (subjectName like @subjectName1) and
      (subjectKind like @subjectKind1) and
      (isBase like @isBase1) and
      (department like @department1);
GO
对于海量数据，劣质SQL语句和优质SQL语句之间的速度差別可以达到上百倍，可見对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。用好通配符就能提高SQL语句的性能。
3.3 视图
视图是从一个或多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。和表一样，视图也包括几个被定义的数据列和多个数据行，但就本质而言，这些数据列和数据行来源与其所引用的表。所以视图不是真实存在的基础表而是一张虚表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。
使用视图有以下几个优点：
为用户集中数据 视图创建了一个受控制的环境，它允许访问某些数据，并隐藏其他的数据。不必要的、敏感的或不合适的数据都可以不在视图中显示。用户可以操作视图中数据的显示，与表类似。另外使用合适的权限和一些限制，用户可以修改视图产生的数据。
掩盖数据库的复杂性 视图对用户掩盖数据库设计的复杂性，使开发者具有更改设计而不影响用户与数据库打交道的能力。视图还可以隐藏比较复杂的查询，包括对异构数据的分布查询。用户可以简单地查询视图，而不用编写复杂的查询或执行脚本。
简化用户权限的管理 数据库拥有者可以给其他的用户授权，使他们可以通过试图来查询数据，而不是限制用户查询基表中特定列的权限。这样，还可以使得基表设计中的更改不至于影响用户的查询。
组织数据导出到其他应用程序 可以创建基于连接两个或多个表的复杂查询的视图，然后把数据导出到另一个应用程序作进一步的分析。
视图的基础是建立在基础表上的查询，视图的优化取决于其所基于的SQL语句的优化。通常来讲，单表视图和只读视图效率最佳。
3.4 存储过程
   在大型数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程和触发器，都是SQL语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程。存储过程在运算时生成执行方式，所以，以后对其再运算时其执行速度很快。SQL Server 2000不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。
   存储过程就是将所需要的工作，预先以SQL程序写好，命名后进行保存，以后需要作这些工作时可以使用EXECUTE指令来调用，即可自动完成相应任务。这里的存储过程也是自动化的一个方面，总之就是为了方便管理的一种措施。并且存储过程有许多的优点：
1．执行效率高（这点不容置疑）2．统一的操作流程：也就是通过存储过程的操作避免了一些操作过程中可能无意中认为的错误，只要确定了制作存储过程时是正确地，以后在调用过程中就不用担心了。大家使用时流程是一样的。3．重复使用4．安全性：这一点我们在数据库的安全策略里讨论过，可以参考前边的文章。也就是说：我们可以利用存储过程作为数据存储的管道。可以让客户在一定的范围内对数据进行操作。另外，存储过程是可以加密的，这样别人就看不到他的内容了。
 在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用存储过程来封装数据库操作。如果项目的存储过程较多，书写又没有一定的规范，将会影响以后的系统维护困难和大存储过程逻辑的难以理解，另外如果数据库的数据量大或者项目对存储过程的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的存储过程要比一个性能差的存储过程的效率甚至高几百倍。 　
 对于存储过程的优化，一是要精心设计、调整其主干SQL语句，技巧如前文所述。此外，还要考虑所有存储过程之间合理的模块分割，保证合适的代码粒度。在存储过程中，还要尽量使用预编译SQL而少用动态SQL，以避免存储过程整体的编译开销。

第四章 总结
 经过三个多月的设计和开发，师资管理系统基本开发完毕。其功能基本符合用户需求，能够完成用户管理、教师管理、学科管理的存储和基本信息的查询以及教师年龄段内的职称分布，专任教师中的不任课人数，学历下专任教师的职称分布，学科下职称分布等的统计。并提供部分系统维护功能,使用户方便进行数据备份和恢复、数据删除。对于数据的一致性的问题也通过程序进行了有效的解决。
 但是由于毕业设计时间较短，所以该系统还有许多不尽如人意的地方，比如联机论文比较少，用户界面不够美观，出错处理不够等多方面问题。这些都有待进一步改善。
 
 
致谢
 在本次毕业设计中，我从指导老师杨少雄老师身上学到了很多东西。杨老师认真负责的工作态度，严谨的治学精神和深厚的理论水平都使我收益匪浅。他无论在理论上还是在实践中，都给与我很大的帮助，使我得到不少的提高这对于我以后的工作和学习都有一种巨大的帮助，感谢他耐心的辅导。
 另外，在系统开发过程中杨芃，王沣，钱立娟三位同学也给于我很大的帮助，帮助解决了不少的难点，使得系统能及时开发完成。

相关论文