单元08提高系统性能.ppt

单元8,提高系统性能,回顾,SQL Server有全局变量和局部变量，变量名称分别以“”和“”开头。 局部变量赋值有两种方式：SELECT和SET 可以使用PRINT语句在消息区输出显示数据 使用IFELSE语句作为条件判断语句 使用CASEEND语句作为多分支语句，它有两种使用语法，并可放到SELECT中。 使用WHILE语句可使一组语句重复多次 GO语句是批处理结束的标志 事务有4大特性，分别使用BEGIN TRAN、COMMIT TRAN和ROLLBACK TRAN来显示控制事务 在ADO.NET 中，可以使用Connection 和Transaction 对象来控制事务,本单元任务,任务一 创建索引，提高“查询成绩”模块的速度 任务二 使用存贮过程实现“成绩分析表”模，以便加快报表速度,新增管理员,新增教师,新增学生,本单元目标,掌握索引的概念 掌握T-SQL创建索引的方法 了解存贮过程的意义 掌握T-SQL创建存贮过程 掌握ADO.NET数据访问的参数设置 掌握ADO.NET中调用存贮过程的方法,任务一创建索引，提高“查询成绩”模块的速度,任务描述：,任务分析：,系统运行一段时间，存贮一定量的数据后，发现查询速度明显放慢，有时候还需要等待3分钟以上，才能显示成绩查询结果。大宝经理马上责令整改，提高“查询成绩”模块的速度，控制在30秒之内。,成绩查询涉及几个表：成绩表、课程表、学生表，这三张表要作连接查询。如果三张表全表浏览，需要花费大量的时间。要提高查询速度，最简洁、有效的方法是建立索引。数据库使用索引的方式与使用书的目录很相似：通过搜索索引找到特定的值，然后跟随指针到达包含该值的行。,汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等 我们可以根据拼音或偏旁部首，快速查找某个字词,理解索引的概念,两个查询SQL的速度对比,【例8-1】在tStuTest表中创建一百万行记录，查询姓名aaa的学生信息。记录查询所需时间。,SELECT GETDATE() SELECT * FROM tstuTest where stuName = aaa SELECT GETDATE(),【例8-2】重启SQL Server实例，查询学号1500000的学生信息。,SELECT GETDATE() SELECT * FROM tstuTest where stuno=1500000 SELECT GETDATE(),比较两个SQL的查询时间，想一想，为什么？,因为stuNo是主键，系统自动为主键创建了主键索引！,索引类型,唯一索引：唯一索引不允许两行具有相同的索引值 主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空 聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个,使用企业管理器创建索引,演示使用企业管理器创建,使用T-SQL创建索引,语法,Create UNIQUE CLUSTERED | NONCLUSTERED Index 索引名 ON 表名（列名1, 列名2）,创建索引示例,Create Index idx_student_name ON Student(StudentName),【例8-3】由于经常按照姓名查询学生信息，创建索引加快查询速度。 【例8-4】经常按照身份证号查询学生信息，创建索引加快查询速度。,Create Index idx_student_ID ON Student(StudentIDNO),创建索引的注意事项,按照下面标准创建索引列： 用于搜索的列 用于排序的列 请不要在下面的列创建索引： 列值的区分度不大，只有几种取值。例如：性别，只有两种取值：男、女。就不建议在性别列上创建索引。 数据表中的数据不多，只有几行数据。,任务实施,1在成绩表上创建索引 2在学生表上创建索引,创建索引示例,Create Index idx_score_studenrno ON Score(StudentNO) Create Index idx_score_SubjectID ON Score(SubjectID) Create Index idx_score_Score1 ON Score(Score1) Create Index idx_score_Score2 ON Score(Score2) Create Index idx_score_Score3 ON Score(Score3),1在成绩表上创建索引 2在学生表上创建索引,Create UNIQUE Index idx_student_studentIDNo on Student(studentIDNo) Create Index idx_student_studentName on Student(studentName) Create Index idx_student_Phone on Student(Phone),任务二 使用存贮过程实现“成绩分析表”,任务描述：,任务分析：,周丽在进行系统性能测试时，发现添加测试数据后，“成绩分析表”模块明显变慢，周丽将结果返回给张力。张力希望加快系统运行速度，张力运行该模块，“成绩分析表”的报表如图8-6所示，张力打开程序代码，发现程序代码比较复杂，张力希望简化程序代码，并使统计报表速度控制在1分钟内。,1可以考虑提高硬件配置，如采用性能更高的CPU和增加内存等。 2在硬件不变的情况下，创建索引。查询成绩可按照学号、课程号、期末成绩、平时成绩、总评成绩查询，因此，在这些列上，都建立索引。 3创建存贮过程，加快系统运行速度。创建存贮过程实现对成绩的汇总分析，可使代码简洁且能提高速度。,存储过程 - - -,单个 SELECT 语句,SELECT 语句块,SELECT语句与逻辑控制语句,可以包含,存储过程,存储过程就是存储在SQL SERVER服务器 存储过程是SQL语句和可选控制流语句的预编译集合 存储过程可以包含数据操纵语句、变量、逻辑 控制语句等,简化用户操作 执行效率更高 减少网络流通量 提高系统安全性 允许模块化程序设计,存储过程的优点,创建存储过程,语法：,Create PROCEDURE 存储过程名 参数1 数据类型 = 默认值 OUTPUT, , 参数n 数据类型 = 默认值 OUTPUT AS SQL语句 GO,和其他语言的函数一样，参数可选,参数分为输入参数、输出参数,输入参数允许有默认值,创建不带参数的存贮过程,为测试表添加10万行测试记录的T-SQL，创建存贮过程。,CREATE PROC p_insert_rows AS -例7-13中的SQL set nocount on SET IMPLICIT_TRANSACTIONS ON- 设置开启自动事务 declare i int, cnt int, d datetime select d=getdate(), i=1, cnt=100000 while (i=cnt) begin INSERT INTO tstuTest VALUES(i,Left(convert(varchar(40),newid(),10),i%2, convert(varchar(40), newid(), rand()*100, d-i%1000) set i = i + 1 if (i%100=0)- 每100行提交一次 commit tran End SET IMPLICIT_TRANSACTIONS OFF- 设置关闭自动事务 commit tran GO,调用存储过程,EXECUTE（执行）语句用来调用存储过程 调用的语法： EXEC 过程名 参数,truncate table tstuTest exec p_insert_rows,创建带输入参数的存贮过程,语法：,(参数1 数据类型, 参数n 数据类型),创建带输入参数的存贮过程,【例8-6】希望更灵活，添加测试记录的编号由用户指定。,alter PROC p_insert_rows(from int, to int) AS set nocount on SET IMPLICIT_TRANSACTIONS ON- 设置开启自动事务 declare i int, d datetime select d=getdate(), i=from while (i=to) begin INSERT INTO tstuTest VALUES(i,Left(convert(varchar(40),newid(),10),i%2, convert(varchar(40), newid(), rand()*100, d-i%1000) set i = i + 1 if (i%100=0)- 每100行提交一次 commit tran End SET IMPLICIT_TRANSACTIONS OFF commit tran GO,exec p_insert_rows 100001,200000,创建带输出参数的存贮过程,语法：,参数名 数据类型OUTPUT,创建带输出参数参数的存贮过程,【例8-7】返回添加数据后测试表的数据行数,ALTER PROC p_insert_rows(num int output , from int=1, to int=100000) AS select num=count(*) from tstuTest; GO,Declare i int exec p_insert_rows i print i,调用存储过程,执行存储过程注意事项： 如果存储过程是批处理中的第一条语句，那么不使用EXECUTE关键字也可以执行该存储过程。 如果存储过程的输入参数已设置默认值，还可以省略那些已提供默认值的参数。要注意的是：如果第N个参数为默认值，那么，第N+1、N+2、N+3.个参数都必须为默认值。 想一想，下面的调用方法正确否？,Declare cnt int EXECUTE p_insert_rows cnt OUTPUT,5000,declare cnt int EXECUTE p_insert_rows cnt OUTPUT, to=5000,重新编译过程,需要重新编译存储过程，以反映对索引所做的修改 通常使用sp_recompile重编译存贮过程： 【例8-8】重新编译存贮过程p_insert_rows。,sp_recompile 存贮过程名,sp_recompile p_insert_rows,语法： 【例8-9】删除存贮过程p_insert_rows。,删除存储过程,DROP PROCEDURE procedure ,.n ,DROP PROCEDURE p_insert_rows,存储过程可看作是一种没有返回值的特殊函数，函数与存贮过程的区别是：函数有返回值，其返回值可以直接在表达式、计算列中使用；而存储过程只能调用执行，它不能通过名称得到返回值，也不能直接在表达式、计算列中使用 语法：,创建用户函数,CREATE FUNCTION 函数名 ( 参数1 参数类型 = 默认值, 参数n 参数类型 = 默认值, ) RETURNS 返回数据类型 BEGIN RETURN 返回值 END,【例8-10】编写一个用户自定义函数f_getClassName，要求对于MySchool数据库中，根据班级号获取班级名。,创建用户函数范例,Create Function f_getClassName(classid int) returns varchar(50) begin declare className varchar(50) select className = className from class where classid=classid return className end GO,select studentName, dbo.f_getClassName(classid) from student,创建参数常用下列两种方法： 通过属性窗体设置 在SqlCommand的CommandText属性中，录入SQL，该SQL语句中使用命名参数，前面冠以，例如 SELECT * FROM Student Where classID = classID 单击【确定】按钮，系统自动完成参数的设置。 使用SqlCommand.Parameter.Add（参数名,类型）方法。,VS中调用存贮过程 -使用参数,【例8-11】在SqlCommand的实例化comm对象中，欲执行添加测试数据的存贮过程，需要添加下列3个参数。,创建参数范例,comm.Parameters.Add(num, SqlDbType.Int); / 输出参数 comm.Parameters0. Direction = ParameterDirection.Output; comm.Parameters.Add(from, SqlDbType.Int); comm.Parameters.Add(to, SqlDbType.Int);,字符串类型的参数 ： comm.Parameters.Add(StudentName, SqlDbType.Varchar, 10),【例8-12】根据界面输入的起至号，调用存贮过程，插入测试数据。,使用SqlCommand调用存贮过程,private void btnOk_Click(object sender, EventArgs e) SqlCommand comm = new SqlCommand(p_insert_rows, DBHelper.connection); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Add(num, SqlDbType.Int); comm.Parameters0. Direction = ParameterDirection.Output; / 输出参数 comm.Parameters.Add(from, SqlDbType.Int); comm.Parameters.Add(to, SqlDbType.Int); comm.Parametersfrom.Value = txtFrom.Text; comm.Parametersto.Value = txtTo.Text; try DBHelper.connection.Open(); comm.ExecuteNonQuery(); txtCnt.Text = comm.Parametersnum.Value.ToString(); catch MessageBox.Show(添加数据出错！); finally DBHelper.connection.Close(); ,如果需要调用存贮过程并返回结果集，通常会使用适配器来执行存贮过程。 【例8-13】修改存贮过程p_insert_rows，除实现原又功能外，还要返回最后3行数据。 重建存贮过程,使用SqlDataAdapter调用存贮过程,ALTER PROC p_insert_rows(num int output , from int=1, to int=100000) AS SELECT TOP 3 * FROM tstuTest order by stuNo DESC GO,关键代码：, 使用适配器调用存贮过程并填充到DataSet,SqlCommand comm = new SqlCommand(p_insert_rows, DBHelper.connection); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.Add(num, SqlDbType.Int); comm.Parameters0.Direction = ParameterDirection.Output; / 输出参数 comm.Parameters.Add(from, SqlDbType.Int); comm.Parameters.Add(to, SqlDbType.Int); comm.Parametersfrom.Value = txtFrom.Text; comm.Parametersto.Value = txtTo.Text; SqlDataAdapter da = new SqlDataAdapter(comm); /用构造好的Command实例化适配器 DataSet ds = new DataSet(); da.Fill(ds, student); dataGridView1.DataSource = ds.Tables0; txtCnt.Text = comm.Parametersnum.Value.ToString();,任务实施,1在成绩表上创建索引 2在学生表上创建索引,创建按照班级汇总分析的存贮过程：,创建存贮过程1,create proc p_score(subjectid int=1, classid int=1 ) as declare i int declare myTab Table(dj int, cnt int) - 表变量（等级，人数） insert into myTab - 按照等级统计人数，并存贮于表变量中 select floor(score1/10), count(*) from score where subjectid=subjectid and studentno in (select studentno from student where classid=classid ) group by floor(score1/10) ,创建按照班级汇总分析的存贮过程：,创建存贮过程2, -将竖表转换为横表 SELECT * FROM ( SELECT SUM(CASE WHEN dj=9 THEN cnt else 0 END) AS d9, SUM(CASE WHEN dj=8 THEN cnt else 0 END) AS d8, SUM(CASE WHEN dj=7 THEN cnt else 0 END) AS d7, SUM(CASE WHEN dj=6 THEN cnt else 0 END) AS d6, SUM(CASE WHEN dj=5 THEN cnt else 0 END) AS d5, SUM(CASE WHEN dj=4 THEN cnt else 0 END) AS d4, SUM(CASE WHEN dj=3 THEN cnt else 0 END) AS d3, SUM(CASE WHEN dj=2 THEN cnt else 0 END) AS d2, SUM(CASE WHEN dj=1 THEN cnt else 0 END) AS d1, SUM(CASE WHEN dj=0 THEN cnt else 0 END) AS d0 FROM myTab ) aa GO,关键代码：,在VS中执行存贮过程1,private void btnOK_Click(object sender, EventArgs e) int classID, subjectID; /获取班级号、课程号 classID = Convert.ToInt32(cbxClass.SelectedValue.ToString(); subjectID = Convert.ToInt32(cbxSubject.SelectedValue.ToString(); SqlCommand comm = new SqlCommand(p_score, DBHelper.connection); comm.CommandType = CommandType.StoredProcedure;/ 命令类型为存贮过程 comm.Parameters.Add(subjectid, SqlDbType.Int);/ 添加传入参数 comm.Parameters.Add(classid, SqlDbType.Int); comm.Parameterssubjectid.Value = subjectID;/ 设置传入参数的值 comm.Parametersclassid.Value = classID; SqlDataAdapter da = new SqlDataAdapter(comm); DsRptScoreAnalyese ds = new DsRptScoreAnalyese(); / 使用类型化数据集 da.Fill(ds, p_score); if (ds.Tables0.Rows.Count 0) ds.Tables0.Rows0className = cbxClass.Text;/设置班级名 ds.Tables0.Rows0subjectName = cbxSubject.Text;/设置课程名 ,关键代码：,在VS中执行存贮过程2, / 创建报表 RptScoreAnalyse rpt = new RptScoreAnalyse(); / 填充报表数据 rpt.SetDataSource(ds.Tables0); / 将报表填充至报表浏览器 crystalReportViewer1.ReportSource = rpt; ,总结,创建索引可以明显提高系统性能。 创建索引的语法是： Create UNIQUE CLUSTERED | NONCLUSTERED Index 索引名 存贮过程可加快查询速度、提高安全性、减少网络流量和模块化编程。 CREATE PROC语句用于创建存贮过程。 EXECUTE语句用于调用存贮过程。 sp_recompile用于重新编译存储过程。 ADO.NET添加参数常使用: SqlCommand.Parameters.Add（参数名,类型） 使用SqlCommand调用存贮过程，应设置属性 CommandType = CommandType.StoredProcedure; CommandText = 存贮过程名,总结,ADO.NET常使用SqlCommand.Parameters.Add（参数名,类型）方法添加参数。 使用SqlCommand调用存贮过程，应设置属性 CommandType = CommandType.StoredProcedure; CommandText = 存贮过程名,作业,谢谢！,