问题描述
现在两个各有10W＋条数据的表查询时很费时，而且经常出现进程aspnet_wp.exe被意外回收的情况，具体代码如下：SELECTa.DocumentID,a.ID,a.IsIncept,a.IsFiling,a.DocumentCode,a.Title,a.Resume,a.SubjectWord,a.FileSize,a.FileContent,b.PrmContentsASsecretGradeFROM(SELECTdbo.DocLinkArchive.*,dbo.Doc_InceptArchiveDoc.OldTableNoASDocumentCode,dbo.Doc_InceptArchiveDoc.FileTitleASTitle,dbo.Doc_InceptArchiveDoc.ResumeASResume,dbo.Doc_InceptArchiveDoc.ThemeWordASSubjectWord,dbo.Doc_InceptArchiveDoc.secretGradeASsecretGrade,dbo.Doc_InceptArchiveDoc.FileSizeASFileSize,dbo.Doc_InceptArchiveDoc.FileContentASFileContentFROMdbo.DocLinkArchiveINNERJOINdbo.Doc_InceptArchiveDocONdbo.DocLinkArchive.ID=dbo.Doc_InceptArchiveDoc.ID)aLEFTOUTERJOIN(SELECTID,PrmContentsFROMdbo.Doc_PrmDetailsWHEREDelFlag=0ANDPrmID=100)bONa.secretGrade=b.ID请教大虾们如何优化提高查询速度？
解决方案
解决方案二：
其中SELECTdbo.DocLinkArchive.*,dbo.Doc_InceptArchiveDoc.OldTableNoASDocumentCode,dbo.Doc_InceptArchiveDoc.FileTitleASTitle,dbo.Doc_InceptArchiveDoc.ResumeASResume,dbo.Doc_InceptArchiveDoc.ThemeWordASSubjectWord,dbo.Doc_InceptArchiveDoc.secretGradeASsecretGrade,dbo.Doc_InceptArchiveDoc.FileSizeASFileSize,dbo.Doc_InceptArchiveDoc.FileContentASFileContentFROMdbo.DocLinkArchiveINNERJOINdbo.Doc_InceptArchiveDocONdbo.DocLinkArchive.ID=dbo.Doc_InceptArchiveDoc.ID占查询成本的46％，平均查询时间在1分半左右
解决方案三：
去掉括号:LEFTOUTERJOINdbo.Doc_PrmDetailsbonDelFlag=0ANDPrmID=100a.secretGrade=b.ID-----查看一下条件的是否有索引
解决方案四：
条件字段没有建立索引改了之后无法运行啊！！～～
解决方案五：
好了可以运行了可是查询还是异常的慢！
解决方案六：
去嵌套查询，去外连接！改用SELECTdbo.DocLinkArchive.DocumentID,dbo.DocLinkArchive.ID,dbo.DocLinkArchive.IsFiling,dbo.DocLinkArchive.DocumentCode,dbo.Doc_InceptArchiveDoc.OldTableNoASDocumentCode,dbo.Doc_InceptArchiveDoc.FileTitleASTitle,dbo.Doc_InceptArchiveDoc.ResumeASResume,dbo.Doc_InceptArchiveDoc.ThemeWordASSubjectWord,--dbo.Doc_InceptArchiveDoc.secretGradeASsecretGrade,dbo.Doc_InceptArchiveDoc.FileSizeASFileSize,dbo.Doc_InceptArchiveDoc.FileContentASFileContent,dbo.Doc_PrmDetails.Doc_PrmDetailsASsecretGradefromdbo.DocLinkArchive,dbo.Doc_InceptArchiveDo,dbo.Doc_PrmDetailswheredbo.DocLinkArchive.ID=dbo.Doc_InceptArchiveDoc.IDandDoc_InceptArchiveDoc.secretGrade=Doc_PrmDetails.IDunionallSELECTdbo.DocLinkArchive.DocumentID,dbo.DocLinkArchive.ID,dbo.DocLinkArchive.IsFiling,dbo.DocLinkArchive.DocumentCode,dbo.Doc_InceptArchiveDoc.OldTableNoASDocumentCode,dbo.Doc_InceptArchiveDoc.FileTitleASTitle,dbo.Doc_InceptArchiveDoc.ResumeASResume,dbo.Doc_InceptArchiveDoc.ThemeWordASSubjectWord,--dbo.Doc_InceptArchiveDoc.secretGradeASsecretGrade,dbo.Doc_InceptArchiveDoc.FileSizeASFileSize,dbo.Doc_InceptArchiveDoc.FileContentASFileContent,nullfromdbo.DocLinkArchive,dbo.Doc_InceptArchiveDocwheredbo.DocLinkArchive.ID=dbo.Doc_InceptArchiveDoc.IDandnotexist(select'X'fromDoc_InceptArchiveDocwhereDoc_InceptArchiveDoc.secretGrade=Doc_PrmDetails.ID)
解决方案七：
试试看上面的语句，反正思想就是“去嵌套查询，去外连接”，然后用三个表连接查询再“unionall（不要是union）”不匹配“notexist（不要用notin）”最后一个表的条件的数据