索引视图如何提高性能 | 郑州计算机学校_郑州电脑学校_郑州电脑培训

29

03-2014

索引视图如何提高性能

  最近的一个项目中,由于数据量特别大,导致生成一个报表需要等待10多分钟,所以必须对系统进行优化,跟踪程序后发现数据是从由六个表联接而成的视图中过滤出来的,并且六个表中每个表中的数据都是百万级的,这样联接后效率可想而知了?所以得先对视图进行优化,检查这六个表后发现其中的两个表未建索引,于是马上对其设置索引,再把select的没用字段去除后,再重新执行一下查询语句后,发现效率提高了差不多20%,但是总的来说时间还是比较长,看来还得继续改进。听朋友说索引视图可以大大提高效率,于是从网上查找了一些关于索引视图的资料,并对其进行了简单学习,现总结如下:  一、实例  1、 优化前的视图(执行select * fromQuery_NoEmployRegist用时127s)  代码  1 SET ANSI_NULLS ON  2 GO  3 SET QUOTED_IDENTIFIER ON  4 GO  5 ALTER VIEW [dbo].[Query_NoEmployRegist]  6 AS  7 SELECT dbo.Person_BasicInfo.*, dbo.Graduater_GraduaterRegist.RegistNO AS RegistNO,  8 dbo.Graduater_GraduaterRegist.RegistTime AS BaoDaoTime,  9 dbo.Graduater_GraduaterRegist.RegistMan AS RegistMan,  10 dbo.Graduater_Business.ComeFrom AS ComeFrom,  11 dbo.Graduater_Business.Code AS Code, dbo.Graduater_Business.Status AS Status,  12 dbo.Graduater_Business.ApproveResult AS ApproveResult,  13 dbo.Graduater_Business.NewCorp AS NewCorp,  14 dbo.Graduater_Business.CommendNumber AS CommendNumber,  15 dbo.Graduater_Business.EmployStatus AS EmployStatus,  16 dbo.Graduater_Business.NewCommendTime AS NewCommendTime,  17 dbo.Graduater_Business.GetSource AS GetSource,  18 dbo.Graduater_Business.EmployTime AS EmployTime,  19 dbo.Graduater_Business.Job AS Job, dbo.Graduater_Business.FillMan AS FillMan,  20 dbo.Graduater_Business.FillTime AS FillTime,  21 dbo.Graduater_Business.IsCommendOK AS IsCommendOK,  22 dbo.Graduater_Business.ApproveUser AS ApproveUser,  23 dbo.Graduater_Business.ApproveTime AS ApproveTime,  24 dbo.Graduater_Business.RegistTime AS RegistTime,  25 dbo.Graduater_Business.EmployCorp AS EmployCorp,  26 dbo.Graduater_Business.JobRemark AS JobRemark,  31 dbo.Person_Contact.Address AS Address, dbo.Person_Contact.Zip AS Zip,  32 dbo.Person_Contact.Telephone AS Telephone, dbo.Person_Contact.Mobile AS Mobile,  33 dbo.Person_Contact.Email AS Email, dbo.Person_Contact.IM AS IM,  34 dbo.Person_Skill.ForeignLanguage AS ForeignLanguage,  35 dbo.Person_Skill.ForeignLanguageLevel AS ForeignLanguageLevel,  36 dbo.Person_Skill.CantoneseLevel AS CantoneseLevel,  37 dbo.Person_Skill.MandarinLevel AS MandarinLevel,  38 dbo.Person_Skill.Language AS Language,  39 dbo.Person_Skill.TechnicalTitle AS TechnicalTitle,  40 dbo.Person_Skill.ComputerLevel AS ComputerLevel,  41 dbo.Person_EmployPurpose.JobType AS JobType,  42 dbo.Person_EmployPurpose.Vocation AS Vocation,  43 dbo.Person_EmployPurpose.JobPlace AS JobPlace,  44 dbo.Person_EmployPurpose.Salary AS Salary,  45 dbo.Person_EmployPurpose.OnJobDate AS OnJobDate,  46 dbo.Person_EmployPurpose.CorpType AS CorpType,  49 dbo.Graduater_Business.EmployType AS EmployType,  50 dbo.Graduater_Business.EmployTypeCode AS EmployTypeCode,  51 dbo.Graduater_Business.EmployCorpType AS EmployCorpType  56 FROM dbo.Person_BasicInfo INNER JOIN  57 dbo.Graduater_Business ON  58 dbo.Person_BasicInfo.PersonID = dbo.Graduater_Business.PersonID LEFT OUTER JOIN  59 dbo.Graduater_GraduaterRegist ON  60 dbo.Graduater_Business.GradBusinessID = dbo.Graduater_GraduaterRegist.GraduaterGUID  61 INNER JOIN  62 dbo.Person_Contact ON  63 dbo.Person_BasicInfo.PersonID = dbo.Person_Contact.PersonID INNER JOIN  64 dbo.Person_Skill ON  65 dbo.Person_BasicInfo.PersonID = dbo.Person_Skill.PersonID INNER JOIN  66 dbo.Person_EmployPurpose ON  67 dbo.Person_BasicInfo.PersonID = dbo.Person_EmployPurpose.PersonID  68 GO  69 SET ANSI_NULLS OFF  70 GO  71 SET QUOTED_IDENTIFIER OFF  72 GO

该日志 2014年03月29日发表在网络安全技术分类下, 通告目前不可用,你可以至底部留下评论。
转载请注明: 索引视图如何提高性能 | 郑州计算机学校_郑州电脑学校_郑州电脑培训