最近的一个项目中，由于数据量特别大，导致生成一个报表需要等待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 |
