如何寫出高效的ＳＱＬ指令碼《一》-ASP.Net

如何寫出高效的ＳＱＬ指令碼《一》
註：測試記錄：每個表記錄數：3040000。
如何寫出高效的ＳＱＬ指令碼：
1. 設計如何滿足ＳＡＲＧ形式的ＳＱＬ指令碼
SARG的定義：用於限制搜尋的一個操作，因為它通常是指一個特定的匹配，一個值得範圍內的匹配或者兩個以上條件的AND串連。［可以理解為索引掃描］形式如下：
列名 操作符
或
操作符列名
列名可以出現在操作符的一邊，而常數或變數出現在操作符的另一邊。如：
Name=’ATA’
數量>5000
5000Name=’ATA’ and 數量>5000
如果一個運算式不能滿足SARG的形式，那它就無法限制搜尋的範圍了，也就是SQL SERVER必須對每一行都判斷它是否滿足WHERE子句中的所有條件。所以一個索引對於不滿足SARG形式的運算式來說是無用的
2. Ｌｉｋｅ
Like語句是否屬於SARG取決於所使用的萬用字元的類型
如：name like ‘ATA%’ ，這就屬於SARG
而：name like ‘%ATA’ ,就不屬於SARG。
原因是萬用字元%在字串的開頭使得索引無法使用。
3. ＯＲ　和　ＩＮ
or 會引起全表掃描
Name=’ATA’ and 數量>5000 符號SARG，而：Name=’ATA’ or 數量>5000 則不符合SARG。使用or和Ｉｎ會引起全表掃描
4. 非操作符、函數引起的不滿足SARG形式的語句
不滿足SARG形式的語句最典型的情況就是包括非操作符的語句，如：NOT、!=、、!、NOT EXISTS、NOT IN、NOT LIKE，is null, not null等，另外還有函數。下面就是幾個不滿足SARG形式的例子：
ABS(數量)Name like ‘%ATA’
有些運算式，如：
WHERE 數量*2>5000
SQL SERVER也會認為是SARG，SQL SERVER會將此式轉化為：
WHERE數量>2500/2
不推薦這樣使用，因為有時SQL SERVER不能保證這種轉化與原始運算式是完全等價的。
5. 函數charindex()、前面加萬用字元%的LIKE，後面加%的效率比較
如果在LIKE前面加上萬用字元%，那麼將會引起全表掃描，所以其執行效率是低下的。用函數charindex()來代替LIKE速度會有大的提升的說法不對的，測試如下：
1. select fcandidateid,fcandidatename from tcandidate where fcandidatename like ’Tim%’
用時：36秒，記錄結果數：２００萬
2. select fcandidateid,fcandidatename from tcandidate
where charindex(’Tim’,fcandidatename)>0
用時：47秒，記錄結果數：２００萬
3. select fcandidateid,fcandidatename from tcandidate
where fcandidatename like ’%Tim%’
用時：45秒，記錄結果數：２００萬
通過以上3個例子可以看出，再使用Like的時候，後面加“Tim%”符合SARG規則，用時明顯少於後兩種，後兩種的效能基本上差不多
如果非的模糊，比如：substring(fcandidatename,1,1)=’A’,那麼可以考慮這樣：fcandidatename like ‘A%’來代替（因為這樣用的是索引掃描，不是表掃描）

如何寫出高效的ＳＱＬ指令碼《二》
6. 欄位提取要按照“需多少、提多少”的原則，避免“select *“
下面我來做一個測試：
a) select fcandidateid,fcandidatename from tcandidate where fcandidatename like ’Tim%’
用時：35秒　記錄結果數：２００萬
b) select fcandidateid,fcandidatename,fCredentialsID,fbirthday from tcandidate where fcandidatename like ’Tim%’
用時：51秒記錄結果數：２００萬
c) select fcandidateid from tcandidate where fcandidatename like ’Tim%’
用時：23秒　記錄結果數：２００萬
由此看來，我們每少提取一個欄位，資料的提取速度就會有相應的提升，當然提升的速度還要看您捨棄的欄位的大小來判斷。
7. order by 的使用，用那些欄位作為排序效率比較高
1. 使用具有族索引或者primary key的欄位排序。
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like ’Tim%’ order by fcandidateid desc
用時：45秒 記錄結果數：２００萬
2. 使用數位欄位（既不是primary key,也不是族索引、foreign key）
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like ’Tim%’ order by fRegionID desc
用時：1分15秒　記錄結果數：２００萬
3. 使用字串的欄位[數字字串]（既不是primary key,也不是族索引、foreign key）
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like ’Tim%’ order by fCredentialsID desc
用時：1分22秒 　記錄結果數：２００萬
4. 使用字串[字母組成的字串]
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like ’Tim%’ order by fcandidatename desc
用時：1分34秒 　記錄結果數：２００萬
5. 使用日期欄位
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fcandidatename like ’Tim%’ order by fbirthday desc
用時：1分9秒 　記錄結果數：２００萬
從以上可以看出，使用族索引、Primary key的欄位進行排序，速度是比較快的，另外，如果表有foreign key的欄位，在排序的時候，可以優先考慮這些欄位。
以上進行了數字、數字字串、字母字串、日期的排序，關於效能大家可以參看以上的測試結果，測試的結果日期欄位的排序比數位要高，這個可以在多測試一下類似的資料量，比較一下到底數字和日期欄位到底哪個速度快些。
另外的幾種情況，大家就一目瞭然了。
同時，按照某個欄位進行排序的時候，無論是正序還是倒序，速度是基本相當的。
8. 插入大的二進位值到Image列需要注意的
如果要插入大的二進位值到Image列，使用預存程序，千萬不要用內嵌INsert來插入。因為這樣應用程式首先將二進位值轉換成字串（尺寸是它的兩倍），伺服器收到字元後又將他轉換成二進位值.
預存程序就沒有這些動作: 在前台調用這個預存程序傳入二進位參數，這樣處理速度明顯改善。
9. 高效的TOＰ
事實上，在查詢和提取超大容量的資料集時，根據需要提取一次需要的資料，如果允許盡量使用ｔｏｐ語句。如：

select top 50000 * from (
select top 2000000 fcandidateid,fcandidatename,fbirthday from tcandidate
where fcandidatename like ’Tim%’
order by fcandidateid desc) as a

用時：1179毫秒

大家可以仔細看上面的查詢語句，可以和以上的語句項比較，就可以知道top的效率有多高了
這條語句，從理論上講，整條語句的執行時間應該比子句的執行時間長，但事實相反。因為，子句執行後返回的是2000000條記錄，而整條語句僅返回50000條語句，所以影響資料庫回應時間最大的因素是物理I/O操作。而限制物理I/O操作此處的最有效方法之一就是使用TOP關鍵詞了。TOP關鍵詞是SQL SERVER中經過系統最佳化過的一個用來提取前幾條或前幾個百分比資料的詞。所以經過最佳化演算法的ＴＯＰ效率就很高了
10. 如何使用ＳＱＬ的函數注意的問題
1. ＳＱＬ的函數在ＳＱＬ指令碼中不同的位置消耗的成本就不一樣
select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID
from tcandidate where dateadd(year,5,fbirthday)=’1981/09/08’
CPU:2079 用時：25317 毫秒 記錄：1000000
2. select fcandidateid,fCredentialsID,fcandidatename,fbirthday,fRegionID from tcandidate where fbirthday=dateadd(year,-5,’1981/09/08’)
CPU:1219 用時：21666毫秒 記錄結果：1000000
所以從以上可以看出，不同的SQL函數方的位置不一樣，效能和消耗的成本也不一樣，總體原則把ＳＱＬ函數放到條件的右邊效能消耗的成本等比較低。
3. 注意使用DISTINCT，在沒有必要時不要用，它同UNION一樣會使查詢變慢，如果確認結果集中沒有重複的記錄，請不要輕易用ｄｉｓｔｉｃｔ

11. 表和暫存資料表的用法
1. 一般情況下盡量使用表變數而不用暫存資料表，為何推薦表變數，請看下面的解釋：
l 表變數（如局部變數）具有明確定義的範圍，在該範圍結束時會自動清除這些表變數。
l 與暫存資料表相比，表變數導致預存程序的重新編譯更少。
l 涉及表變數的事務僅維持表變數上更新的期間。因此，使用表變數時，需要鎖定和記錄資源的情況更少。因為表變數具有有限的範圍並且不是持久性資料庫的一部分，所以交易回復並不影響它們。
2. 什麼時候使用表變數而不使用暫存資料表
• 插入到表中的行數。
• 從中儲存查詢的重新編譯的次數。
• 查詢類型及其對效能的指數和統計資訊的依賴性。
3. 關於表變數的缺陷，大家可以到msdn上搜尋一下（table variable）