程式效能最佳化之SQL篇，效能最佳化sql

程式效能最佳化之SQL篇，效能最佳化sql

         如果說功能是程式的軀體，那麼效能就是程式的靈魂。完整的功能可以保證程式的軀體是健全的，而良好的效能才是程式靈魂的象徵，本文就程式的效能最佳化做簡單的介紹。

        最近對程式的效能的體會尤為深刻。最近做了一個資料查詢和顯示的功能，從7張表大概1500條資料中查詢25條資料並且顯示出來，時間消耗1秒鐘。我的電腦參數為：CPU:i5處理器，4G記憶體。這個執行速度相當的慢，好在我查詢的資料量比較小，等待時間不是很多，但是程式效能最佳化確實刻不容緩。

        仔細分析了程式，發現有很多地方都是需要修改的，我們先從資料庫開始。下面先來看一段代碼，這個是程式中的SQL語句：

select {0} from ((((({1} inner join {2}) inner join {3}) inner join {4}) inner join {5}) inner join {6}) inner join {7} where {8} ",                      "lp.CnName as ProjectCityName,t.TaskName,ls.CnName as StageAreaName,tm.TaskName as TaskTemplateName,t.StartTime,t.RealStartTime,t.EndTime,t.RealEndTime,t.Executor,cc.CnName as CorporationName",                       "[SubjectDB].[WorkPlan].[Task] as t",                       "[SubjectDB].[WorkPlan].[Plan] as p on t.PlanCode=p.Code",                       "[SubjectDB].[WorkPlan].[TemplateTask] as tm on t.TemplateTaskCode = tm.Code",                        "[SubjectDB].[LandObtained].[ProjectCity] as lp on p.ProjectCityCode = lp.CityCode",                        "[SubjectDB].[LandObtained].[StageArea] as ls on p.StageAreaCode = ls.Code",                        "[SubjectDB].[LandObtained].[Corporation_ProjectInfo] as lcp on p.ProjectCode  = lcp.ProjectInfoCode",                        "[SubjectDB].[Common].[Corporation] as cc on lcp.CorporationCode = cc.Code",                        "t.RealEndTime <= '" + endTime + "'and t.RealStartTime >='" + startTime + "' and t.VersionEndTime is null and p.ProjectCityCode in ('" + strCityCodeIn + "') and t.TaskStatus = '" + taskStatusCode + "' and ( t.TaskName like " + strTaskPointNameIn + ") and t.TemplateTaskCode not in ('" + strTaskPointCodeIn + "')

          分析一下這段代碼，使用了6個inner join，1個in，1個not in，不固定數量的like（strTaskPointNameIn還附加了orlike）。首先，資料查詢就很費勁，這裡應該如何進行最佳化呢？資料庫的使用在效能上有哪些需要注意的地方呢？

           一   inner join，left join ，right join

         1 inner join （等值串連）返回兩個表串連欄位相等的記錄

         2 left join （左串連）返回左表中的所有記錄和右表中串連欄位相等的記錄

        3  right join （右串連）返回右表中所有記錄和左表中串連欄位相等的記錄

如：

A表

ID1	Name1
1	a
2	a
3	a

 

 

B表

ID2	Name2
1	b
3	b
4	b

 

Innerjoin :

         select * from A inner join B onA.ID  = B.ID

結果：

ID1	Name1	ID2	Name2
1	a	1	b
3	a	3	b

 

Leftjoin:

Select * from A left join B on A.ID =  B.ID

結果：

ID1	Name1	ID2	Name2
1	a	1	b
2	a	null	null
3	a	3	b

 

Rightjoin :

Select * from  A right join Bon A .ID  =  B.ID

結果：

ID1	Name1	ID2	Name2
1	a	1	b
3	a	3	b
null	null	4	b

 

       這三種表串連方式，不能直接說那個效能最好，一是看功能需要，二是看具體查詢語句。就上面的SQL語句和功能來看，innerjoin應該是最適合的，一是資料量較小，二是不需要多餘資料。

二  in   Exists

       另一個值得討論的地方就是in和exists的使用了，這兩者是在巢狀查詢的時候使用的。我們先來看它們各自的查詢原理。

        in是把外表和內表做hash串連，exists是把外表做loop迴圈，每次迴圈再對內表進行查詢，一直以來說exists比in的查詢方式更快是不準確的，不同情況結果也不一樣，並且如果內外表資料量相一致的情況下，兩種方式效率是相同的。

        現在，我們還是假設有兩個表：

A表：資料量較小，B表：資料量較大。

 select * from A where cc in(select  cc  from B)

         效率較低，因為in串連使用外表A的cc列的索引進行loop迴圈，然後在B表中尋找等值記錄。

 

         select *  from A where  exists (select  cc from B where  cc = A.cc)

        效率較高，因為使用了B表cc列的索引

    Not   in 和  not exists

         如果查詢語句使用到了not    in，那麼內外兩張表都需要全表掃描，沒有用到索引，效率較低；而not     exists使用到了索引，所以無論哪個表大，not     exists效率較高。

       暫時寫到這裡吧，下篇文章繼續繼續效能最佳化。