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

先把查詢貼上：

       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

執行后有圖有真相：

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

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

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

先看看數據庫引擎優化顧問能給我們提供什么優化信息，有時候它能夠幫我們提供有效的信息，比如創建統計，索引，分區什么的。

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

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

在這里可以單擊查看一些建議，分區，創建索引，根據提示創建了如下索引：

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)

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

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

再次執行查詢

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

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

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

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

為了看函數執行效果先刪除索引，看看查詢中函數f_GetAidNamebyConsult1要干的事情，截取查詢中與該函數有關的子查詢：

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 

得到下圖的結果：

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

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

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(    --根據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)

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