开发学院数据库MSSQL Server 【探讨】索引视图如何提高性能 阅读

【探讨】索引视图如何提高性能

 2010-01-25 00:00:00 来源:WEB开发网   
核心提示: 3、 创建索引的视图(执行select * from Query_NoEmployRegist用时51s)代码1SETANSI_NULLSon2GO3SETQUOTED_IDENTIFIERon4GO56ALTERVIEW[dbo].[Query_NoEmployRegist]7WITHSCHE

3、  创建索引的视图(执行select * from Query_NoEmployRegist用时51s)

代码

 1 SET ANSI_NULLS on
 2 GO
 3 SET QUOTED_IDENTIFIER on
 4 GO
 5 
 6 ALTER VIEW [dbo].[Query_NoEmployRegist]
 7 WITH SCHEMABINDING AS
 8 SELECT 
 9       dbo.Person_BasicInfo.PersonID,
10       dbo.Person_BasicInfo.IdentityID,
11       dbo.Person_BasicInfo.Name,
12       dbo.Person_BasicInfo.Sex, 
13       dbo.Person_BasicInfo.Folk,
14       dbo.Person_BasicInfo.Politics, 
15       dbo.Person_BasicInfo.Birthday,
16       dbo.Person_BasicInfo.StudentSource, 
17       dbo.Person_BasicInfo.StudentSourceCode,
18       dbo.Person_BasicInfo.EduLevel, 
19       dbo.Person_BasicInfo.EduLevelCode,
20       dbo.Person_BasicInfo.EduNumber, 
21       dbo.Person_BasicInfo.Stature, 
22       dbo.Person_BasicInfo.Avoirdupois, 
23       dbo.Person_BasicInfo.MarriageStatus,
24       dbo.Person_BasicInfo.College,
25       dbo.Person_BasicInfo.GraduatedDate, 
26       dbo.Person_BasicInfo.Train, 
27       dbo.Person_BasicInfo.Major,
28       dbo.Person_BasicInfo.Degree, 
29       dbo.Person_BasicInfo.DegreeCertificate, 
30       dbo.Person_BasicInfo.StudyMode,        
31       dbo.Graduater_GraduaterRegist.RegistNO AS RegistNO, 
32       dbo.Graduater_GraduaterRegist.RegistTime AS BaoDaoTime, 
33       dbo.Graduater_GraduaterRegist.RegistMan AS RegistMan, 
34       dbo.Graduater_Business.ComeFrom AS ComeFrom, 
35       dbo.Graduater_Business.Code AS Code, dbo.Graduater_Business.Status AS Status, 
36       dbo.Graduater_Business.ApproveResult AS ApproveResult, 
37       dbo.Graduater_Business.NewCorp AS NewCorp, 
38       dbo.Graduater_Business.CommendNumber AS CommendNumber, 
39       dbo.Graduater_Business.EmployStatus AS EmployStatus, 
40       dbo.Graduater_Business.NewCommendTime AS NewCommendTime, 
41       dbo.Graduater_Business.GetSource AS GetSource, 
42       dbo.Graduater_Business.EmployTime AS EmployTime, 
43       dbo.Graduater_Business.Job AS Job, dbo.Graduater_Business.FillMan AS FillMan, 
44       dbo.Graduater_Business.FillTime AS FillTime, 
45       dbo.Graduater_Business.IsCommendOK AS IsCommendOK, 
46       dbo.Graduater_Business.ApproveUser AS ApproveUser, 
47       dbo.Graduater_Business.ApproveTime AS ApproveTime, 
48       dbo.Graduater_Business.RegistTime AS RegistTime, 
49       dbo.Graduater_Business.EmployCorp AS EmployCorp, 
50       dbo.Graduater_Business.JobRemark AS JobRemark, 
51       dbo.Person_Contact.Address AS Address, dbo.Person_Contact.Zip AS Zip, 
52       dbo.Person_Contact.Telephone AS Telephone, dbo.Person_Contact.Mobile AS Mobile, 
53       dbo.Person_Contact.Email AS Email, dbo.Person_Contact.IM AS IM, 
54       dbo.Person_Skill.ForeignLanguage AS ForeignLanguage, 
55       dbo.Person_Skill.ForeignLanguageLevel AS ForeignLanguageLevel, 
56       dbo.Person_Skill.CantoneseLevel AS CantoneseLevel, 
57       dbo.Person_Skill.MandarinLevel AS MandarinLevel, 
58       dbo.Person_Skill.Language AS Language, 
59       dbo.Person_Skill.TechnicalTitle AS TechnicalTitle, 
60       dbo.Person_Skill.ComputerLevel AS ComputerLevel, 
61       dbo.Person_EmployPurpose.JobType AS JobType, 
62       dbo.Person_EmployPurpose.Vocation AS Vocation, 
63       dbo.Person_EmployPurpose.JobPlace AS JobPlace, 
64       dbo.Person_EmployPurpose.Salary AS Salary, 
65       dbo.Person_EmployPurpose.OnJobDate AS OnJobDate, 
66       dbo.Person_EmployPurpose.CorpType AS CorpType, 
67       dbo.Person_EmployPurpose.Job AS RequireJob, 
68       dbo.Graduater_Business.EmployType AS EmployType, 
69       dbo.Graduater_Business.EmployTypeCode AS EmployTypeCode, 
70       dbo.Graduater_Business.EmployCorpType AS EmployCorpType, 
71 FROM dbo.Person_BasicInfo INNER JOIN
72       dbo.Graduater_Business ON 
73       dbo.Person_BasicInfo.PersonID = dbo.Graduater_Business.PersonID INNER JOIN
74       dbo.Graduater_GraduaterRegist ON 
75       dbo.Graduater_Business.GradBusinessID = dbo.Graduater_GraduaterRegist.GraduaterGUID
76        INNER JOIN
77       dbo.Person_Contact ON 
78       dbo.Person_BasicInfo.PersonID = dbo.Person_Contact.PersonID INNER JOIN
79       dbo.Person_Skill ON 
80       dbo.Person_BasicInfo.PersonID = dbo.Person_Skill.PersonID INNER JOIN
81       dbo.Person_EmployPurpose ON 
82       dbo.Person_BasicInfo.PersonID = dbo.Person_EmployPurpose.PersonID
83 GO
84 CREATE UNIQUE CLUSTERED INDEX Query_NoEmployRegist_Ind 
85      ON Query_NoEmployRegist(GraduatedDate, StudentSourceCode,RegistTime,ApproveTime,PrintTime,ComeFrom)
86 SET ANSI_NULLS ON
87 GO
88 SET QUOTED_IDENTIFIER ON
89 GO

上一页  1 2 3 4 5 6 7 8  下一页

Tags:探讨 索引 视图

编辑录入:爽爽 [复制链接] [打 印]
[]
  • 好
  • 好的评价 如果觉得好,就请您
      0%(0)
  • 差
  • 差的评价 如果觉得差,就请您
      0%(0)
赞助商链接