记一次苦逼的SQL查询优化

8/10/2015来源:ASP.NET技巧人气:2465

记一次苦逼的SQL查询优化

最近在维护公司项目时,需要加载某页面,总共加载也就4000多条数据,竟然需要35秒钟,要是数据增长到40000条,我估计好几分钟都搞不定。卧槽,要我是用户的话估计受不了,趁闲着没事,就想把它优化一下,走你。先把查询贴上:

Sql代码
  1. selectPub_AidBasicInformation.AidBasicInfoId,
  2. Pub_AidBasicInformation.UserName,
  3. Pub_AidBasicInformation.District,
  4. Pub_AidBasicInformation.Street,
  5. Pub_AidBasicInformation.Community,
  6. Pub_AidBasicInformation.DisCard,
  7. Pub_application.CreateOnASAppCreateOn,
  8. Pub_User.UserNameasDepartmentUserName,
  9. Pub_Consult1.ConsultId,
  10. Pub_Consult1.CaseId,
  11. Clinicaltb.Clinical,AidNametb.AidName,
  12. Pub_Application.IsUseTraining,
  13. Pub_Application.ApplicationId,
  14. tab.num
  15. FROMPub_Consult1
  16. INNERJOINPub_ApplicationONPub_Consult1.ApplicationId=Pub_Application.ApplicationId
  17. INNERJOINPub_AidBasicInformationONPub_Application.AidBasicInfoId=Pub_AidBasicInformation.AidBasicInfoId
  18. INNERJOIN(selectConsultId,dbo.f_GetClinical(ConsultId)asClinical
  19. fromPub_Consult1)ClinicaltbonClinicaltb.ConsultId=Pub_Consult1.ConsultId
  20. leftjoin(selectdistinctApplicationId,sum(TraniningNumber)asnumfromdbo.Review_Aid_UseTraining_RecordwhereAidReferralIdisnullgroupbyApplicationId)tabontab.ApplicationId=Pub_Consult1.ApplicationId
  21. INNERJOIN(selectConsultId,dbo.f_GetAidNamebyConsult1(ConsultId)asAidNamefromPub_Consult1)AidNametbonAidNametb.ConsultId=Pub_Consult1.ConsultId
  22. LEFTOUTERJOINPub_UserONPub_Application.ReviewUserId=Pub_User.UserId
  23. WHEREPub_Consult1.Directory=0
  24. orderbyPub_Application.CreateOndesc

执行后有图有真相:

这么慢,没办法就去看看查询计划是怎么样:

这是该sql查询里面执行三个函数时生成查询计划的截图,一看就知道,执行时开销比较大,而且都是花费在聚集索引扫描上,把鼠标放到聚集索引扫描的方块上面,依次看到如下详细计划:

从这几张图里,可以看到查询I/O开销,运算符开销,估计行数,以及操作的对象和查询条件,这些都为优化查询提供了有利证据。第1,3张图IO开销比较大,第2张图估计行数比较大,再根据其它信息,首先想到的应该是去建立索引,不行的话再去改查询。先看看数据库引擎优化顾问能给我们提供什么优化信息,有时候它能够帮我们提供有效的信息,比如创建统计,索引,分区什么的。先打开SQL Server PRofiler 把刚刚执行的查询另存为跟踪(.trc)文件,再打开数据库引擎优化顾问,做如下图操作

最后生成的建议报告如下:

在这里可以单击查看一些建议,分区,创建索引,根据提示创建了如下索引:

Sql代码
  1. CREATENONCLUSTEREDINDEXindex1ON[dbo].[Pub_AidBasicInformation]
  2. (
  3. [AidBasicInfoId]ASC
  4. )
  5. CREATENONCLUSTEREDINDEXindex1ON[dbo].[Pub_Application]
  6. (
  7. [ApplicationId]ASC,[ReviewUserId]ASC,[AidBasicInfoId]ASC,[CreateOn]ASC
  8. )
  9. CREATENONCLUSTEREDINDEXindex1ON[dbo].[Pub_Consult1]
  10. (
  11. [Directory]ASC,[ApplicationId]ASC
  12. )
  13. CREATENONCLUSTEREDINDEXidnex1ON[dbo].[Review_Aid_UseTraining_Record]
  14. (
  15. [AidReferralId]ASC,[ApplicationId]ASC
  16. )

索引创建后,再次执行查询,原以为可提高效率,没想到我勒个去,还是要30几秒,几乎没什么改善,优化引擎顾问有时候也会失灵,在这里只是给大家演示有这种解决方案去解决问题,有时候还是靠谱的,只是这次不靠谱。没办法,只有打开函数仔细瞅瞅,再结合上面的查询计划详细图,删除先前创建的索引,然后创建了如下索引:

Sql代码Flashva