提高SQL Server数据库访问速度的方法
- 引言 SQL Server数据库作为微软在Windows平台上开发的数据库，已经过多次的功能改进，出现了SQL Server2000版本，虽然SQL Server2000还无法和Oracle、Informix等大型数据库相比，但它在市场上的占有率足以说明了其先进性。很多程序员只注重程序算法的编写，并不对SQL Server2000数据库处理进行深入的考虑，从而导致数据库数据或并发用户增多时，系统执行速度越来越慢，甚至导致死机。本文根据我校在开发成绩录入与管理系统过程中出现的问题，对如何提高SQL Server数据库访问速度进行研究。 我校成绩录入与管理系统中主要包含的数据表为：学生表，成绩表，课程表，专业表，学院表。各个学期成绩表等如图1所示(部分表以及表结构已经省略)。  1 合理使用视图和分区视图 视图可以看成是虚拟表或存储查询，可以通过select对视图中的数据进行查询，但是无法插入、更新、删除视图中的数据信息。很多程序员在编程过程中只使用数据表，而其它的一些操作全部利用程序来完成。过去我校的成绩录入与管理系统中，所有的数据操作都利用表来完成，大部分数据交换和数据调用都通过函数来实现，因此随着数据量的增大整个系统的执行速度越来越慢。系统经过改进，把需要利用多个select查询的数据创建成视图，系统速度有了很大的提高。 以图2所示的数据表为例，在一个页面中想要完成学生的成绩查询，并且要知道学生的姓名和课程名称，如果通过程序实现，必须利用三次select语句才能把图2所示三个表中所需要的字段显示出来。这种方法大大影响了数据库访问的速度。若利用视图，则只需进行一次select查询就可以得到想要的数据，其效率远远大于程序实现的效率。 视图是不保存任何记录的，它存储的是查询语句，所显示的记录来自于数据表(可以为多个数据表)。可以依据各种查询需要创建不同的数据表，不会因此增加数据库的数据量。 再举一个例子：我校每个学期都会有20万条的成绩数据，迄今为止成绩录入与管理系统已经运行了5个学期，各学期总成绩就有100万条数据，对于如此大量的数据，如何来管理，就每个程序员来说都是非常头疼的问题。 对此可以引入分区视图来解决。分区视图是指将一个或多个服务器上的数据表数据合并，通过定义check约束，将搜索范围限制到这些表上。分区视图的关键是check约束，如果不定义check约束，查询分析器必须搜索所有的表。具体到我校的成绩录入与管理系统的处理上，我们为每个学期单独创建一个表，表名为成绩[学期][学年]例如：表名为成绩20061。可以利用SQL中设计视图功能创建各个学期表。 在成绩表中需要注意，一定要建立学年、学期列的check约束。接着定义一个视图把以前所有学期的数据使用union all连接起来作为单个的结果集。别出此语句中的搜索条件，将其搜索范围限制在这个表上。这种方法的使用可以大大提高SQL Server数据库的访问速度。 2 使用存储过程提高数据处理速度 存储过程最大的优点是将存储处理翻译成可执行码保存在系统表内，当作数据库的对象之一。由于存储过程已事先被翻译成可执行码，可以直接执行，所以其执行速度会很快。对一些功能固定的需求，把它写成存储过程可提高SQL Server数据处理的速度。以我校成绩录入与管理系统为例：我校每学期成绩表中数据都在20万条以上，而且把各个学期的数据连接到一起形成了总成绩视图，数据量超过100万条，要操作这样庞大的数据库，如果使用方法不当，就会严重地降低执行速度。我们考虑将经常使用的功能全部写成存储过程来提高数据库访问的速度，如：学期数据导入、成绩数据备份、总成绩查询等。结果表明执行速度有很大的提高。 对于学期成绩数据导入功能说明如下：在每学期成绩库中有20多万条数据，每学期成绩录入结束以后都要进行学期成绩数据导入。以前使用insert语句进行程序导入，执行速度很慢，甚至有时慢得无法执行，后来通过一个简单的存储过程很快地解决了问题，20万条数据导入，执行时间在10秒左右。 我校教务教学管理中使用学分制，通过学分绩的高低来反映一个学生学习成绩的好坏。计算学分绩的公式是((学生成绩—50)／10)*课程的学分。在学分和学分绩插入操作种需要进行判断：考试成绩不及格，则学分和学分绩都为0；考试成绩在60分以上，则计算出相应的学分和学分绩。最后通过“delete from成绩表”清空成绩表中的成绩数据，以便下学期正常进行成绩录入工作。合理地建立存储过程可以有效地提高数据库访问速度；还可以把一些程序算法封装到存储过程中来提高软件产品的可维护性，即使是条件发生改变时，也不需要修改客户端的应用程序，只要对数据库端编写的存储过程进行修改即可。 3 索引设计与优化 目前很多软件为方便使用者学习也在帮助文档中添加索引功能，微软的MSDN是最典型的例子之一。在MSDN中专门有一项索引服务，很多程序员可能熟悉而且经常使用这个功能。通过索引查询信息不但方便而且能大大缩短查找信息的时间。在SQL Server数据库设计中也采用了这种概念，为了加快数据库访问的速度，可以对某些常用的查询条件创建索引。 在我校成绩录入与管理系统开发的过程中，解决数据库访问速度是一个关键问题。以往，期末考试结束以后，很多教师集中进行学生成绩录入，并行用户往往都在100人以上，而且对数据库反复进行插入、更新、删除数据等操作，在这种情况下，程序执行速度非常慢，录入一条成绩数据往往需要1分多钟，而且有时还出现一些莫名其妙的错误，这使开发人员十分困惑。经过对数据库各个表的仔细核查，并对每个表相应的字段建立索引后，问题完全得到解决，经过测试在线用户100人时，录入一条成绩数据大约2～3秒，系统处理速度得到很大提高。下面是建立索引的一个具体例子：在学生表中经常用到的字段只有一个：学号，我们可以对学号建立索引，建立方法如下create index sid on学生表(学号)。 提高数据库访问速度，不但要使用索引，而且还要优化索引；但是不能盲目使用索引，更不能对所有的字段都加上索引，这样不但不会提高速度，反而会因为过多的索引导致表扫描增多，增大查询造成的I／O开销。常用的索引主要分为两种：聚集索引、非聚集索引。在建立索引过程中要根据具体情况来选择不同的索引。聚集索引是表中存储的数据按照索引的顺序存储，检索效率比普通索引要高，但对数据新增、修改、删除的影响比较大。这就是说聚集索引适合固定表，如学生表、专业表、课程表、学院表，而不适合要不断新增、修改、删除成绩信息的成绩表。非聚集索引和聚集索引相反，不影响表中的数据存储顺序，检索效率比聚集索引低，但是对新增、修改、删除的影响很小，在成绩表中就必须建立非聚集索引了。 4 结束语 在SQL Server数据库中合理有效地利用以上提到方法固然能够提高访问速度，但是在具体的程序、数据库开发过程中还要不断的改进这些方法，并且还要注意在实际项目中的灵活应用。提高SQL Server数据库访问速度的方法还有很多。在具体数据库开发过程中需要程序员们不断地优化数据库的操作，提升其性能，使得SQL Server数据库潜能得到全部发挥。希望本文能够起到抛砖引玉的作用，并让更多的程序员来关心数据库操作的效率问题。 |