单元 提高系统性能

单元提高系统性能第1页，课件共43页，创作于2023年2月回顾SQLServer有全局变量和局部变量，变量名称分别以“@@”和“@”开头。局部变量赋值有两种方式：SELECT和SET可以使用PRINT语句在消息区输出显示数据使用IF…ELSE语句作为条件判断语句使用CASE…END语句作为多分支语句，它有两种使用语法，并可放到SELECT中。使用WHILE语句可使一组语句重复多次GO语句是批处理结束的标志事务有4大特性，分别使用BEGINTRAN、COMMITTRAN和ROLLBACKTRAN来显示控制事务在ADO.NET中，可以使用Connection和Transaction对象来控制事务第2页，课件共43页，创作于2023年2月本单元任务

任务一创建索引，提高“查询成绩”模块的速度任务二使用存贮过程实现“成绩分析表”模，以便加快报表速度新增管理员新增教师新增学生第3页，课件共43页，创作于2023年2月本单元目标掌握索引的概念掌握T-SQL创建索引的方法了解存贮过程的意义掌握T-SQL创建存贮过程掌握ADO.NET数据访问的参数设置掌握ADO.NET中调用存贮过程的方法第4页，课件共43页，创作于2023年2月任务一创建索引，提高“查询成绩”模块的速度

任务描述：任务分析：第5页，课件共43页，创作于2023年2月汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等我们可以根据拼音或偏旁部首，快速查找某个字词理解索引的概念第6页，课件共43页，创作于2023年2月两个查询SQL的速度对比【例8-1】在tStuTest表中创建一百万行记录，查询姓名aaa的学生信息。记录查询所需时间。【例8-2】重启SQLServer实例，查询学号1500000的学生信息。比较两个SQL的查询时间，想一想，为什么？第7页，课件共43页，创作于2023年2月索引类型唯一索引：唯一索引不允许两行具有相同的索引值主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个第8页，课件共43页，创作于2023年2月使用企业管理器创建索引演示使用企业管理器创建第9页，课件共43页，创作于2023年2月使用T-SQL创建索引

语法第10页，课件共43页，创作于2023年2月创建索引示例CreateIndexidx_student_nameONStudent(StudentName)

【例8-3】由于经常按照姓名查询学生信息，创建索引加快查询速度。【例8-4】经常按照身份证号查询学生信息，创建索引加快查询速度。CreateIndex

idx_student_IDON

Student(StudentIDNO)第11页，课件共43页，创作于2023年2月创建索引的注意事项按照下面标准创建索引列：①用于搜索的列②用于排序的列请不要在下面的列创建索引：列值的区分度不大，只有几种取值。例如：性别，只有两种取值：男、女。就不建议在性别列上创建索引。数据表中的数据不多，只有几行数据。第12页，课件共43页，创作于2023年2月任务实施1．在成绩表上创建索引2．在学生表上创建索引第13页，课件共43页，创作于2023年2月创建索引示例CreateIndexidx_score_studenrnoONScore(StudentNO)CreateIndexidx_score_SubjectIDONScore(SubjectID)CreateIndexidx_score_Score1ONScore(Score1) CreateIndexidx_score_Score2ONScore(Score2) CreateIndexidx_score_Score3ONScore(Score3)1．在成绩表上创建索引2．在学生表上创建索引CreateUNIQUEIndexidx_student_studentIDNoonStudent(studentIDNo)CreateIndexidx_student_studentNameonStudent(studentName)CreateIndexidx_student_PhoneonStudent(Phone)第14页，课件共43页，创作于2023年2月任务二使用存贮过程实现“成绩分析表”

任务描述：任务分析：第15页，课件共43页，创作于2023年2月存储过程------------------------单个SELECT语句SELECT语句块SELECT语句与逻辑控制语句可以包含存储过程存储过程就是存储在SQLSERVER服务器

存储过程是SQL语句和可选控制流语句的预编译集合

存储过程可以包含数据操纵语句、变量、逻辑控制语句等第16页，课件共43页，创作于2023年2月简化用户操作

执行效率更高减少网络流通量提高系统安全性允许模块化程序设计存储过程的优点第17页，课件共43页，创作于2023年2月创建存储过程语法：参数分为输入参数、输出参数输入参数允许有默认值第18页，课件共43页，创作于2023年2月创建不带参数的存贮过程为测试表添加10万行测试记录的T-SQL，创建存贮过程。第19页，课件共43页，创作于2023年2月调用存储过程EXECUTE（执行）语句用来调用存储过程调用的语法：

EXEC过程名[参数]truncatetabletstuTestexecp_insert_rows第20页，课件共43页，创作于2023年2月创建带输入参数的存贮过程语法：第21页，课件共43页，创作于2023年2月创建带输入参数的存贮过程【例8-6】希望更灵活，添加测试记录的编号由用户指定。execp_insert_rows100001,200000

第22页，课件共43页，创作于2023年2月创建带输出参数的存贮过程语法：第23页，课件共43页，创作于2023年2月创建带输出参数参数的存贮过程

【例8-7】返回添加数据后测试表的数据行数Declare@iintexecp_insert_rows@iprint@i第24页，课件共43页，创作于2023年2月调用存储过程执行存储过程注意事项：如果存储过程是批处理中的第一条语句，那么不使用EXECUTE关键字也可以执行该存储过程。如果存储过程的输入参数已设置默认值，还可以省略那些已提供默认值的参数。要注意的是：如果第N个参数为默认值，那么，第N+1、N+2、N+3…….个参数都必须为默认值。想一想，下面的调用方法正确否？Declare@cntintEXECUTEp_insert_rows@cntOUTPUT,,5000declare@cntintEXECUTEp_insert_rows@cntOUTPUT,@to=5000第25页，课件共43页，创作于2023年2月重新编译过程需要重新编译存储过程，以反映对索引所做的修改通常使用sp_recompile重编译存贮过程：【例8-8】重新编译存贮过程p_insert_rows。第26页，课件共43页，创作于2023年2月语法：【例8-9】删除存贮过程p_insert_rows。删除存储过程第27页，课件共43页，创作于2023年2月存储过程可看作是一种没有返回值的特殊函数，函数与存贮过程的区别是：函数有返回值，其返回值可以直接在表达式、计算列中使用；而存储过程只能调用执行，它不能通过名称得到返回值，也不能直接在表达式、计算列中使用语法：

创建用户函数第28页，课件共43页，创作于2023年2月【例8-10】编写一个用户自定义函数f_getClassName，要求对于MySchool数据库中，根据班级号获取班级名。创建用户函数范例selectstudentName,dbo.f_getClassName(classid)fromstudent第29页，课件共43页，创作于2023年2月创建参数常用下列两种方法：①通过属性窗体设置在SqlCommand的CommandText属性中，录入SQL，该SQL语句中使用命名参数，前面冠以@，例如SELECT*FROMStudentWhereclassID=@classID单击【确定】按钮，系统自动完成参数的设置。②使用SqlCommand.Parameter.Add（参数名,类型）方法。VS中调用存贮过程-使用参数第30页，课件共43页，创作于2023年2月【例8-11】在SqlCommand的实例化comm对象中，欲执行添加测试数据的存贮过程，需要添加下列3个参数。创建参数范例字符串类型的参数：comm.Parameters.Add("@StudentName",SqlDbType.Varchar,10)第31页，课件共43页，创作于2023年2月【例8-12】根据界面输入的起至号，调用存贮过程，插入测试数据。使用SqlCommand调用存贮过程

第32页，课件共43页，创作于2023年2月如果需要调用存贮过程并返回结果集，通常会使用适配器来执行存贮过程。【例8-13】修改存贮过程p_insert_rows，除实现原又功能外，还要返回最后3行数据。①重建存贮过程使用SqlDataAdapter调用存贮过程

第33页，课件共43页，创作于2023年2月关键代码：②使用适配器调用存贮过程并填充到DataSet

第34页，课件共43页，创作于2023年2月任务实施1．在成绩表上创建索引2．在学生表上创建索引第35页，课件共43页，创作于2023年2月创建按照班级汇总分析的存贮过程：创建存贮过程1第36页，课件共43页，创作于2023年2月创建按照班级汇总分析的存贮过程：创建存贮过程2第37页，课件共43页，创作于2023年2月关键代码：在VS中执行存贮过程1第38页，课件共43页，创作于2023年2月关键代码：在VS中执行存贮过程2第39页，课件共43页，创作于2023年2月总结创建索引可以明显提高系统性能。创建索引的语法是：Create[UNIQUE][CLUSTERED|NONCLUSTERED]Index索引名存贮过程可加快查询速度、提高安全性、减少网络流量和模块化编程。CREATEPROC语句用于创建存贮过程。EXECUTE语句用于调用存贮过程。sp_recompile用于重新编译存储过程。ADO.NET添加参数常使用:SqlCommand.Parameters.Add（参数名,类型）使用SqlCommand调用存贮过程，应设置属性CommandType=CommandType.StoredProcedure;

CommandText=