最近在維護公司項目時，需要加載某頁面，總共加載也就4000多條數(shù)據(jù)，竟然需要35秒鐘，要是數(shù)據(jù)增長到40000條，我估計好幾分鐘都搞不定。臥槽，要我是用戶的話估計受不了，趁閑著沒事，就想把它優(yōu)化一下，走你。

先把查詢貼上：

       select Pub_AidBasicInformation.AidBasicInfoId,       Pub_AidBasicInformation.UserName,       Pub_AidBasicInformation.District,       Pub_AidBasicInformation.Street,       Pub_AidBasicInformation.Community,       Pub_AidBasicInformation.DisCard,       Pub_application.CreateOn AS AppCreateOn,       Pub_User.UserName as DepartmentUserName,        Pub_Consult1.ConsultId,       Pub_Consult1.CaseId,       Clinicaltb.Clinical,AidNametb.AidName,       Pub_Application.IsUseTraining,       Pub_Application.ApplicationId,       tab.numFROM   Pub_Consult1INNER JOIN Pub_Application ON Pub_Consult1.ApplicationId = Pub_Application.ApplicationIdINNER JOIN Pub_AidBasicInformation ON Pub_Application.AidBasicInfoId = Pub_AidBasicInformation.AidBasicInfoId                                                           INNER JOIN(select ConsultId,dbo.f_GetClinical(ConsultId) as Clinical            from Pub_Consult1) Clinicaltb on Clinicaltb.ConsultId=Pub_Consult1.ConsultIdleft join (select distinct ApplicationId, sum(TraniningNumber) as num from dbo.Review_Aid_UseTraining_Record  where  AidReferralId is null  group by  ApplicationId) tab on tab.ApplicationId=Pub_Consult1.ApplicationIdINNER JOIN(select ConsultId,dbo.f_GetAidNamebyConsult1(ConsultId) as AidName  from Pub_Consult1) AidNametb on AidNametb.ConsultId=Pub_Consult1.ConsultId                              LEFT OUTER JOIN Pub_User ON Pub_Application.ReviewUserId = Pub_User.UserId     WHERE Pub_Consult1.Directory = 0     order by Pub_Application.CreateOn desc

執(zhí)行后有圖有真相：

這么慢，沒辦法就去看看查詢計劃是怎么樣：

這是該sql查詢里面執(zhí)行三個函數(shù)時生成查詢計劃的截圖，一看就知道，執(zhí)行時開銷比較大，而且都是花費在聚集索引掃描上，把鼠標放到聚集索引掃描的方塊上面，依次看到如下詳細計劃：

從這幾張圖里，可以看到查詢I/O開銷，運算符開銷，估計行數(shù)，以及操作的對象和查詢條件，這些都為優(yōu)化查詢提供了有利證據(jù)。第1，3張圖IO開銷比較大，第2張圖估計行數(shù)比較大，再根據(jù)其它信息，首先想到的應(yīng)該是去建立索引，不行的話再去改查詢。

先看看數(shù)據(jù)庫引擎優(yōu)化顧問能給我們提供什么優(yōu)化信息，有時候它能夠幫我們提供有效的信息，比如創(chuàng)建統(tǒng)計，索引，分區(qū)什么的。

先打開SQL Server PRofiler 把剛剛執(zhí)行的查詢另存為跟蹤(.trc)文件,再打開數(shù)據(jù)庫引擎優(yōu)化顧問，做如下圖操作

最后生成的建議報告如下：

在這里可以單擊查看一些建議，分區(qū)，創(chuàng)建索引，根據(jù)提示創(chuàng)建了如下索引：

CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_AidBasicInformation](    [AidBasicInfoId] ASC)CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_Application](    [ApplicationId] ASC,[ReviewUserId] ASC,[AidBasicInfoId] ASC,[CreateOn] ASC)CREATE NONCLUSTERED INDEX index1 ON [dbo].[Pub_Consult1](    [Directory] ASC,[ApplicationId] ASC) CREATE NONCLUSTERED INDEX idnex1 ON [dbo].[Review_Aid_UseTraining_Record](    [AidReferralId] ASC,[ApplicationId] ASC)

索引創(chuàng)建后，再次執(zhí)行查詢，原以為可提高效率，沒想到我勒個去，還是要30幾秒，幾乎沒什么改善，優(yōu)化引擎顧問有時候也會失靈，在這里只是給大家演示有這種解決方案去解決問題，有時候還是靠譜的，只是這次不靠譜。沒辦法，只有打開函數(shù)仔細瞅瞅，再結(jié)合上面的查詢計劃詳細圖，刪除先前創(chuàng)建的索引，然后創(chuàng)建了如下索引：

CREATE NONCLUSTERED INDEX index1 ON dbo.Report_AdapterAssessment_Aid(    AdapterAssessmentId ASC, ProductDirAId  ASC)CREATE NONCLUSTERED INDEX index1 ON dbo.Report_AdapterAssessment(    ConsultId ASC)

再次執(zhí)行查詢

好了，只需3.5秒，差不多提高10倍速度，看來這次是湊效了哈。

再來看看查詢計劃是否有改變，上張圖來說明下問題：

從上圖當中我們可以看到，索引掃描不見了，只有索引查找，聚集索引查找，鍵查找，而且運算符開銷，I/O開銷都降低了很多。索引掃描(Index Scan),聚集索引掃描(Clustered Index Scan)跟表掃描(Table Scan)差不多，基本上是逐行去掃描表記錄，速度很慢，而索引查找(Index Seek)，聚集索引查找，鍵查找都相當?shù)目臁?yōu)化查詢的目的就是盡量把那些帶有XXXX掃描的去掉，換成XXXX查找。

這樣夠了嗎？但是回頭又想想，4000多條數(shù)據(jù)得3.5秒鐘，還是有點慢了，應(yīng)該還能再快點，所以決定再去修改查詢。看看查詢，能優(yōu)化的也只有那個三個函數(shù)了。

為了看函數(shù)執(zhí)行效果先刪除索引，看看查詢中函數(shù)f_GetAidNamebyConsult1要干的事情，截取查詢中與該函數(shù)有關(guān)的子查詢：

select Pub_Consult1.ConsultId,AidName from (select ConsultId,dbo.f_GetAidNamebyConsult1(ConsultId) as AidNamefrom Pub_Consult1) AidNametb inner join Pub_Consult1on AidNametb.ConsultId=Pub_Consult1.ConsultId 

得到下圖的結(jié)果：

沒想到就這么點數(shù)據(jù)竟然要46秒，看來這個函數(shù)真的是罪魁禍首。

該函數(shù)的具體代碼就不貼出來了，而且該函數(shù)里面還欠套的另外一個函數(shù)，本身函數(shù)執(zhí)行起來就慢，更何況還函數(shù)里子查詢還包含函數(shù)。其實根據(jù)幾相關(guān)聯(lián)的表去查詢幾個字段，并且把一個字段的值合并到同一行，這樣沒必要用函數(shù)或存儲過程，用子查詢再加sql for xml path就行了，把該函數(shù)改成如下查詢：

with cte1 as(    select A.AdapterAssessmentId,case when B.AidName is null then A .AidName else B.AidName end AidName    from Report_AdapterAssessment_Aid as A left join Pub_ProductDir as B    on A.ProductDirAId=B.ProductDirAId), cte2 as(    --根據(jù)AdapterAssessmentId分組并合并AidName字段值    select AdapterAssessmentId,(select AidName+',' from cte1                              where AdapterAssessmentId= tb.AdapterAssessmentId                              for xml path(''))as AidName    from cte1 as tb    group by AdapterAssessmentId),cte3 as(    select ConsultId,LEFT(AidName,LEN(AidName)-1) as AidName    from    (       select Pub_Consult1.ConsultId,cte2.AidName from Pub_Consult1,Report_AdapterAssessment,cte2       where Pub_Consult1.ConsultId=Report_AdapterAssessment.ConsultId       and Report_AdapterAssessment.AdapterAssessmentId=cte2.AdapterAssessmentId       and  Report_AdapterAssessment.AssessTuiJian is null    ) as tb)

這樣查詢出來的結(jié)果在沒有索引的情況下不到1秒鐘就行了。再把主查詢寫了：