szeptember 2014 havi bejegyzések

Optimalizáció check constraint-el

A check constraint fő feladata, hogy az általunk meghatározott szabályoknak megfelelő adatokat (itt is figyelni kell a NULL-ra!) engedi be adott mezőbe, egyfajta validáció. Ez által az insert és update műveletek kicsit lassulnak a check constraint-el bíró mezőkön, hiszen a validálás idő.

Ha a check constraint-et a “with check” opcióval hozzuk létre, akkor a már meglévő adatokat is ellenőrzi, hogy megfelelnek e az általunk definiált feltétel(ek)nek. Az ezzel az opcióval létrehozott constraint egy nagyon erős teljesítménynövelő hatást is kiválthat. Hogyan?

Pl: Ha van egy státusz mezőnk, ami tinyint típusú, check contraint-al biztosítjuk, hogy 1-10-ig mehetnek bele adatok, a query-nk pedig így néz ki: SELECT * FROM tábla WHERE státusz = 11, akkor az optimalizer fogja tudni, hogy 11-es érték biztosan nincs az oszlopban, így eredmény sem lesz, ezért egy page-et sem fog felolvasni a táblából. Ebben az esetben sokkal hatékonyabb, mint bármelyik index!

Ez pedig arról jutott eszembe, hogy mostanában volt egy esetem. Olyan lekérdezésre bukkantam, amit már sehol nem használnak, de másodpercente sokkal többször hívták a kelleténél, toronymagasan vezette az egységnyi idő alatti CPU és diszk használatot. Ez a query így nézett ki (a query csak illusztráció 🙂 :

SELECT p.[user_id] p.[transaction_amount]
FROM [#tmp_user_property] p
JOIN [#tmp_user] u ON u.id = p.[user_id]
WHERE u.[name] = 'User 5'
    AND p.[transaction_date] > '2014-01-01 00:00:00.000'
GO

Csináljuk a lekérdezéshez adatokat:

USE [tempdb]
GO

IF object_id('tempdb..#tmp_user') IS NOT NULL
    DROP TABLE #tmp_user
GO

IF object_id('tempdb..#tmp_user_property') IS NOT NULL
    DROP TABLE #tmp_user_property
GO

CREATE TABLE #tmp_user (
    id INT NOT NULL PRIMARY KEY CLUSTERED
    ,NAME VARCHAR(128) NOT NULL
    )
GO

CREATE TABLE #tmp_user_property (
    id INT NOT NULL PRIMARY KEY CLUSTERED
    ,[user_id] INT NOT NULL
    ,transaction_amount INT NOT NULL
    ,transaction_date DATETIME NOT NULL
    )
GO

CREATE INDEX ix_user_id ON [#tmp_user_property] ([user_id])
GO

INSERT INTO [#tmp_user] ([id],[name])
SELECT TOP 10000
    row_number() OVER (ORDER BY (SELECT NULL)) AS id
    ,CONCAT ('User ',row_number() OVER (ORDER BY (SELECT NULL))) AS NAME
FROM sys.[columns] c1
CROSS JOIN sys.[columns] c2
GO

INSERT INTO [#tmp_user_property] (
    [id]
    ,[user_id]
    ,[transaction_amount]
    ,[transaction_date]
)
SELECT TOP 1000 t.id
    ,t.id AS [user_id]
    ,left(abs(checksum(newid())), 4) AS transaction_amount
    ,dateadd(day, row_number() OVER (ORDER BY (SELECT NULL)), '2010-01-01')
FROM [#tmp_user] t
ORDER BY t.[id]
GO

Ha megnézzük az adatokat, akkor láthatjuk, hogy 2014 évnél korábbiak vannak benne, tehát a fenti query soha nem ad eredményt.

Kapcsoljuk be az IO statisztikát:

set statistics io on;

Kapcsoljuk be az “Include Actual Execution Plan (Ctrl + M)” funkciót a management studióban, majd futtassuk a lekérdezést.

Ezeket kaptam:
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#tmp_user_property__000000000006’. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

A plan pedig így néz ki:
050_result_01

Itt szépen látszik, hogy 6 darab 8 kb-os lapot érintett a lekérdezés és két táblát. Bár a végrehajtási terveben van egy Clustered Index Scan és egy jó index-el ezen lehet javítani, de nekünk most ennél több kell!

Rakjuk fel a check constraint-et:

ALTER TABLE [#tmp_user_property]
	WITH CHECK ADD CONSTRAINT CH_user_property_transaction_date CHECK (transaction_date < '2013-01-01')

Futtassuk ismét a lekérdezést, majd lássuk mit kapunk:
A Messages fülön: (0 row(s) affected)
A plan pedig ez:
050_result_02

A messages fülön láthatjuk, hogy egyetlen page-et sem érintettünk (a táblában szereplő adatokból), a végrehajtási tervből pedig látszik, hogy a táblához nem is nyúlt az optimalizer.

A fenti példánál mégegyszerű volt megoldani a problémát, mert megkértem a fejlesztőt, hogy nézzen már utána, hogy mire van használva a query. Viszont vannak esetek, amikor nincs fejlesztő, akivel lehet ezt le lehet kommunikálni, ilyen esetekben ez a check constraint nagyon jól jöhet.

A check constraint-ek egy másik, sokkal jobb felhasználási területe a particionált view. Akit érdekel a téma keressen rá, mert lehet, hogy holnap már hasznát tudja venni 🙂

 

Reklámok