forward scan vs. backward scan

Évekkel ezelőtt volt egy érdekes felvetése egy "menő" tanácsadó cégnek (nem, nincs név :)), miszerint mindegy, hogy forward vagy backward scan van egy indexen, mert ugyan olyan jó. Ezzel én vitába szálltam, mert nem feltétlen igaz ez, főleg nem ott, ahol számít a teljesítmény. Részletekbe nem feltétlen megyek bele, de a lényeget megmutatom.

Demó környezet

A példához szükségem van egy relatív nagy táblára, amit most a tempdb-ben hozok létre:

 1USE [tempdb]
 2GO
 3 
 4DROP TABLE IF EXISTS [dbo].[T1]
 5GO
 6CREATE TABLE [dbo].[T1]
 7(
 8    [col1] INT IDENTITY PRIMARY KEY NONCLUSTERED,
 9    [col2] INT,
10    [col3] DATETIME
11)
12GO
13 
14CREATE CLUSTERED INDEX [CI_T1] ON [dbo].[T1] ([col3])
15GO

Ebbe a táblába létrehozok 5 millió sort (meg egy kicsit).

 1INSERT INTO [dbo].[T1]
 2(
 3    [col2],
 4    [col3]
 5)
 6SELECT
 7    CHECKSUM(NEWID()),
 8    DATEADD(ms,CHECKSUM(NEWID()), CURRENT_TIMESTAMP)
 9FROM
10    master.sys.objects o
11CROSS JOIN
12    master.sys.objects o1
13GO
14 
15INSERT INTO [dbo].[T1]
16(
17    [col2],
18    [col3]
19)
20SELECT TOP 5000000
21    CHECKSUM(NEWID()),
22        DATEADD(ms,CHECKSUM(NEWID()), CURRENT_TIMESTAMP)
23FROM
24    [dbo].[T1] a
25CROSS JOIN
26    [dbo].[T1] b
27GO

Forward scan

Lássunk egy egyszerű lekérdezést a csodálatos T1 táblára:

1SELECT *
2FROM  
3    [dbo].[T1]
4WHERE 
5    [col3] BETWEEN '20170101' AND '20180101'
6AND
7    [col2] BETWEEN 300000000 AND 699999999
8ORDER BY [col3] ASC;

Ugye nem is bonyolult. Vajon mi a futási terv?

Backward scan

Most nézzük meg ugyan ezt a lekérdezést egy picit másként:

1SELECT *
2FROM  
3    [dbo].[T1]
4WHERE 
5    [col3] BETWEEN '20170101' AND '20180101'
6AND
7    [col2] BETWEEN 300000000 AND 699999999
8ORDER BY [col3] DESC;

Amiben különbözik, az az ORDER BY rész. E miatt most másik futási tervünk van:

Lássuk tudok e ezen változtatni.

1SELECT *
2FROM  
3    [dbo].[T1]
4WHERE 
5    [col3] BETWEEN '20170101' AND '20180101'
6AND
7    [col2] BETWEEN 300000000 AND 699999999
8ORDER BY [col3] DESC
9OPTION (MAXDOP 4)

Sajnos nem, ami látszik is a futási tervben is:

Esetleg más módon? Pl. egy nem dokumentált query hint segítségével? :)

1SELECT *
2FROM  
3    [dbo].[T1]
4WHERE 
5    [col3] BETWEEN '20170101' AND '20180101'
6AND
7    [col2] BETWEEN 300000000 AND 699999999
8ORDER BY [col3] DESC
9OPTION(USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'))

Aha, itt már jó lesz ez, de a futási tervben megjelent egy Sort operátor is. A költsége is jóval magasabb lett, mint az eddigi összes lekérdezésnek. Az ENABLE_PARALLEL_PLAN_REFERENCE hint SQL Server 2016 Sp1 CU2-től elérhető, de jelenleg nem dokumentált, azaz mindenki csak saját felelősségre használja. Ennek lehet alternatívája egy table hint, a FORCESCAN, ami esetében ugyan ezt a futási tervet kapom, illetve a költsége is ugyan olyan magas lesz.

Konklúzió

Alapvetően mindegy is, hogy backward vagy forward scan van, de akkor már nem, ha parallel futási tervvel (legalábbis némelyik operátor hasznot tud húzni a párhuzamos futásból) gyorsabban ki tudom szolgálni a lekérdezést. Backward scan esetén nem lehetséges mindenhol a parallel futás egyes operátoroknál, így ez egy szálon fog menni, ahogy a példában is látszik. Szóval a lényeg, hogy erre is érdemes figyelni. Aki kicsit mélyebbre megy a kódban, pár furcsa dolgot is észrevehet, pl az index sorrend és az ORDER BY.

A query és table hint használatát is csak akkor javaslom, amikor a "hagyományos" optimalizálással már nem érünk célt, ez legyen az utolsó, amit bevetünk a lekérdezések "gyorsításához"

Akit mélyebben érdekel a téma, ajánlom a windbg használatát és ott meg lehet nézni a call stack-et is :)