資料庫效能補充

by adonis 18. 二月 2016 16:30

作    者:楊先民
審    稿:張智凱

 

前言

前幾個星期在上效能調效課程時,有學員問了一個問題,我覺得可以拿出來寫一篇文章,並且探討一下這樣的 SQL句法到底合不合適在 SQL Server中撰寫。

 

使用者需求

在講到使用者需求之前,我們先來看一個簡單的範例,這個例子是在寫預存程序時發生的。

我們都知道,預存程序是會將編譯好的執行計劃放在快取中的,所以你第一次執行的執行計劃將會是以後二、三次甚至是之後100次的執行計劃結果。

例如我們寫一個程式如下:

image

這個預存程序,是有玄機的,當你如果輸入參數為 London時,因為資料回傳結果太多,所以會用SCAN的方式處理,但是如果你的回傳結果是只有1筆時,這時SQL Server就會使用 index seek的方式處理資料。

但是問題來了,如果使用者好死不死第一次就是用 London當參數查資料,這個執行計劃一但被快取,以後不管你是使用什麼參數,SQL Server都只會用 Scan的方式查詢資料。

瞧出問題點了嗎?這就是快取的好處也是快取的壞處。正常的SQL教材都會說,啊你就用 with recompile的方式處理啊,但個人覺得這是一個很蠢的解決方法,因為使用了 with recompile的方式,變成你的預存程序每次都會重新編譯,這樣就喪失了當初我們要使用預存程序的最大的理由之一了。所以在上課我這段都會跳過,我覺得最佳解應該是你的 SQL句法要寫好才是治本,而不是耍蠢的使用 with recompile的方式解決問題。

所以我認為,這個例子最佳的解決是下面的句子,這樣你無論查詢什麼結果,SQL Server保證一定會使用索引。

image

以上圖為例,設定你想要的欄位列(city和postalcode),使用 where句法,如果想排序也是沒問題,這樣你只要設定 city為主索引,ModifiedDate為副索引,再 include postalcode就好。

如此一來,無論你的參數是使用什麼,SQL Server皆會使用索引。

如果你以為,本期的專欄就是在講這個,那你就錯了,因為有個學員有這樣的問題:
如果使用者有【全選】的需求時怎麼辦呢?

也就是說,使用者有時會輸入參數,有時不輸入,則會變成全部資料都要,那該怎麼辦呢?

其實我個人並不建議所謂的【全選】需求,因為你又要全部資料,又要他快是不合理的事情,所以我就先來試驗一下,讓各位知道有這個需求是不好的。

我把程式改寫成以下的寫法:

image

設定參數有預設值,如果沒有填值,就會被帶到後面的 OR 句法。

結果產生出來的執行計劃如下:

image

這個是 London參數時的執行計劃。

image

而這個是全部資料時的執行計劃。

使用 London時的成本為0.1547,而全選則是0.30
而且請注意,執行計劃並不是使用 index seek,而是使用 index scan,也就是我們有索引,也有使用WHERE句法,結果竟然是使用 scan而非 seek的方式,另人不高興的結果。
好吧,這裡再想一個餿主意,改用假設計的方式撰寫,也就是把程式改成以下的寫法:

image

如果使用者輸入的不是全選,就執行非全選的假設句段落,如果輸入的是全選,則使用全選的段落。

結果更慘,所得到的執行計劃成本是1.42,不知它到底在忙些什麼…所以本例而言,寫成假設句是最差的結果。

最後我想到了 index hint,就來強迫SQL Server使用索引吧,再度把句子改成以下的寫法:

image

最後結果和第一次修改成全選的結果一樣… 後來想想也是,Query hint頂多強迫 SQL Server使用索引,但是沒有辦法建議到底是 index scan還是 index seek,所以這個問題就算使用 Query hint也只是徒勞而以。

所以最後的結論已經出來了,就是不要有全選的功能,不然你的效能不可能快的起來,後來該名學員是有補充,因為他們要做報表所以才會有此需求。

唉喲,早點講嘛,報表已經被歸類在資料倉儲的領域中了,不能和SQL Server所謂的 OLTP等而觀之,要知道報表可以慢慢等他產生,最後印出來,或是使用 OLAP的資料庫,然而有關SQL效能的問題基本上都是針對 OLTP的環境來說明的啊,所以我還是覺得兩者不能混而談之。

LAST

Tags:

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List