🚫 Ad Blocker Detected

Please disable your AD blocker to continue using this site. Ads help us keep the content free! please press keyboard F5 to refresh page after disabled AD blocker

請關閉廣告攔截器以繼續使用本網站。廣告有助於我們保證內容免費。謝謝! 關閉後請按 F5 刷新頁面

0%

撰寫SQL的建議

Agenda

前文

本篇會分享在撰寫SQL時建議和比較分享

永遠先考慮T-SQL改寫

  1. 符合SARG Statement進行撰寫
    • <、>、=、<=、>=、LIKE(視%所在位置,前面有%讓DB engine選擇不走INDEX)
  2. 不要在Where欄位做運算
  3. 使用ANSI 92相容的Join方式連接資料庫(避免使用舊式Join)
  4. 避免row by row操作

符合SARG格式的撰寫 + 適當Index設計可以解決大部分的效能問題

使用Like查詢建議

  • 盡量別把%放在前面
  • 如果查詢條件是CNAME LIKE '%范'想讓讓查詢走索引(seek 查詢),在後面加一個條件AND CNAME > ''讓查詢走Seek.
1
2
3
4
5
SELECT [MID]
,[NickName]
,[CName]
FROM [Member_Basic] WITH (NOLOCK)
WHERE CNAME LIKE '%范' AND CNAME > ''

使用Count函數建議

如果要取得筆數數量使用COUNT(*)Count(c1)效能好.

Count(c1)會忽略c1 IS NULL數量.

另外如果[資料筆數]>2^15-1(大於INT最大值)筆數量可使用count_big(*)方法

如果需要COUNT資料很大造成效能影響可以透過DMV取得當前資料表數量(資料會不準確,因為並非及時更新)

1
2
3
4
5
6
7
SELECT SUM(p.rows)
FROM sys.partitions p
WHERE p.[object_id] = object_id('dbo.Person') AND p.index_id < 2

SELECT SUM(p.row_count)
FROM sys.dm_db_partition_stats p
WHERE p.[object_id] = object_id('dbo.Person') AND p.index_id < 2

使用if exists (select 1 from dbo.table)取代count函式判斷資料是否存在

NOT IN vs NOT EXISTS

在查詢時避免使用NOT IN,因為會被QO改寫成 <> NULL,在SQL中NULL代表不知道(Unknow),所以會什麼都查不到

  • 因為此欄位是可空(NULL)時會造成非預期結果(因為NULL會造成判斷失誤NULL不是一個值他代表未知)
  • 使用NOT EXISTS替代NOT IN
  • NOT EXISTS可空欄位效能比NOT IN還要好(如果有建立Index兩個產生執行計畫理論上是一樣)

下面有一個範例來解說為什麼避免在可空欄位使用NOT IN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE T(
ID INT
)

INSERT INTO T VALUES (1)
INSERT INTO T VALUES (2)

SELECT *
FROM dbo.T
WHERE ID NOT IN (
SELECT ID
FROM (SELECT NULL v UNION ALL SELECT 1) t1
)

SELECT *
FROM dbo.T
WHERE NOT EXISTS (
SELECT ID
FROM (SELECT NULL v UNION ALL SELECT 1) t1
WHERE ID = v
)

--DROP TABLE T

上圖可以看到在T資料表中有兩筆資料,如果我們使用NOT INNOT EXISTS結果會不一樣,原因是使用NOT IN在判斷NULL時會造成NULL<>任何值,所以就撈不出任何資料,相反使用NOT EXISTS取得的結果就符合我們預期.

避免在Where條件中對欄位進行操作運算

@col > ‘’ 替代 @col NOT NULL AND <> ‘’

我們會有一種需求須要判斷此

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE TABLE T(
Col VARCHAR(50)
)

INSERT INTO T
SELECT TOP 100000 NULL
FROM sys.all_columns c1
CROSS JOIN sys.all_columns c2


INSERT INTO T VALUES ('DANIEL')
INSERT INTO T VALUES ('DANIEL2')
INSERT INTO T VALUES ('')
INSERT INTO T VALUES ('')

--CREATE INDEX
CREATE INDEX IX_Col on dbo.T(
Col
)

SELECT *
FROM dbo.T
WHERE col > ''

SELECT *
FROM dbo.T
WHERE col IS NOT NULL AND col <> ''
--DROP TABLE T

__此文作者__:Daniel Shih(石頭)
__此文地址__: https://isdaniel.github.io/dbnote/
__版權聲明__:本博客所有文章除特別聲明外,均採用 CC BY-NC-SA 3.0 TW 許可協議。轉載請註明出處!

如果本文對您幫助很大,可街口支付斗內鼓勵石頭^^