[SQL SERVER][Performance]善用計算的資料行
今天雖然是星期六但還是沒啥放假的感覺，
因為下午接到客戶DBA來電說:為什麼建立了Index但該查詢在執行計畫中依然還是Full Table Scan，
而查詢最佳化程式卻不採用所建立的Index，這裡我大概模擬下午整個調校過程。
-- 建立建立測試資料表
create table USER_INFO
(
ID int not null,
FIRST_NAME nvarchar(5) null,
LAST_NAME nvarchar(15) null,
BIRTH datetime null,
CONSTRAINT [PK_USER_INFO] PRIMARY KEY CLUSTERED
(ID ASC)
)
-- 新增資料
insert into USER_INFO
SELECT TOP 20000 ROW_NUMBER() OVER (ORDER BY t1.[SYS_SERIAL]) RowID,
SUBSTRING(t1.[FULLNAME],1,1),
SUBSTRING(t1.[FULLNAME],2,4),
t1.BIRTH
from demo.dbo.RIMD t1
-- Create non clustered index on Computed Column
CREATE NONCLUSTERED INDEX nidx_USER_INFO_FIRST_LAST_NAME
ON dbo.USER_INFO (FIRST_NAME asc,LAST_NAME asc)
GO
CREATE NONCLUSTERED INDEX nidx_USER_INFO_Birty
ON dbo.USER_INFO (Birth asc)
GO
查看資料表和索引使用的空間大小
-- 原本查詢Statement
Select ID,(FIRST_NAME+ LAST_NAME) AS FULLNAME, Birth
FROM dbo.USER_INFO
WHERE FIRST_NAME+ LAST_NAME='****'
OR DAY(Birth)=18
ORDER BY FullName ASC
在執行計畫中可以看到該查詢語法無法讓查詢最佳化程式有效利用我們所建立的非叢集索引，
原因很簡單，因為該查詢語法並不符合SARG格式，基於查詢調校第一步，
就是將非SARG改成SARG(因為這是最直覺且直接的)，而我將利用計算的資料行(空間換時間)完成整個調校過程。
-- 建立測試資料表
create table USER_INFO_CL
(
ID int not null,
FIRST_NAME nvarchar(5) null,
LAST_NAME nvarchar(15) null,
BIRTH datetime null,
CONSTRAINT [PK_USER_INFO_CL] PRIMARY KEY CLUSTERED
(ID ASC)
)
-- 新增資料
insert into USER_INFO_CL
SELECT TOP 20000 ROW_NUMBER() OVER (ORDER BY t1.[SYS_SERIAL]) RowID,
SUBSTRING(t1.[FULLNAME],1,1),
SUBSTRING(t1.[FULLNAME],2,4),
t1.BIRTH
from demo.dbo.RIMD t1
查看資料表大小
-- 新增計算的資料行並實體化資料(儲存在資料表)
ALTER TABLE dbo.USER_INFO_CL ADD
FullName AS (FIRST_NAME+LAST_NAME) PERSISTED,
BirthDay AS DAY(BIRTH) PERSISTED
GO
-- Create non clustered index on Computed Column
CREATE NONCLUSTERED INDEX nidx_USER_INFO_CL_FullName
ON dbo.USER_INFO_CL (FullName asc) INCLUDE (BirthDay)
GO
CREATE NONCLUSTERED INDEX nidx_USER_INFO_CL_BirthDay
ON dbo.USER_INFO_CL (BirthDay asc) INCLUDE (FullName)
GO
建立索引時請依查詢Statement考慮排序(原本查詢有排序的需求)。
再次查看資料表大小
相關計算的資料行資料已被實體化儲存在資料表。
改寫查詢Statement
--改寫查詢Statement
SELECT ID,FullName,BirthDay
FROM dbo.USER_INFO_CL
WHERE FullName = '****'
OR BirthDay=18
ORDER BY FullName ASC
調校後的執行計畫中可以看到，查詢最佳化程式有效的利用我們所建立的非叢集索引(索引搜尋，而非Full Table Scan)，
整體的執行計畫成本降低為0.0354944(原本0.155775)。
在開發過程中，因為使用者的需求或資料表設計因素，導致開發人員可能無法如願寫出符合SARG格式，
但無論如何請盡量將非SARG改成SARG格式，因為這可以讓你的查詢Statement有效的利用Index。
參考