Idag fick jag tillfället att hjälpa en kollega med att optimera en fråga för en kunds räkning. Originalfrågan såg i princip ut så här
— Original
SELECT t.*
FROM dbo.Table1 AS t
CROSS JOIN (
SELECT ChildID
FROM dbo.Table1
) AS w
WHERE (
t.ParentID = w.ChildID AND t.ParentID <> t.ChildID
OR
t.ChildID = t.ParentID AND t.ChildID = w.ChildID
)
AND t.CategoryID IN (’11’, ’22’, ’33’)
Det som jag reagerade först på var användandet av OR i WHERE-satsen. Det gör det mycket svårare för Query Optimizer att välja ett bra index och att skapa en bra exekveringsplan. När jag sedan tittat lite närmare på den för att se vad som egentligen ska hända, vilken affärsregel som ska uppfyllas, ser jag att uttrycket är överflödigt.
Låt oss börja med uttrycket efter OR.
t.ChildID = t.ParentID
t.ChildID = w.ChildID
Av detta kan vi sluta oss till att även uttrycket t.ParentID = w.ChildID är sant. Dessutom är det sant att om t.ChildID = t.ParentID så är det dessutom sant att t.ParentID = t.ChildID. Låt oss skriva om uttrycket med vår härledning och dessutom byta plats på uttrycket efter OR.
— Härledning
SELECT t.*
FROM dbo.Table1 AS t
CROSS JOIN (
SELECT ChildID
FROM dbo.Table1
) AS w
WHERE (
t.ParentID = w.ChildID AND t.ParentID <> t.ChildID
OR
t.ParentID = w.ChildID AND t.ParentID = t.ChildID
)
AND t.CategoriID IN (’11’, ’22’, ’33’)
Vad vi nu kan se är att vi har två exklusiva uttryck i vår OR. I det ena fallet ska t.ParentID = t.ChildID och i det andra utttycket ska t.ParentID <> t.ChildID.
Av detta vet vi att alla rader ska med, för antingen så har de två kolumnerna lika värde eller så har de det inte. Vi behöver heller inte heller ta hänsyn till NULL här då de båda kolumnerna inte tillåter NULL. Om vi plockar bort det onödiga uttrycket från frågan får vi följande query.
— Bortplockning
SELECT t.*
FROM dbo.Table1 AS t
CROSS JOIN (
SELECT ChildID
FROM dbo.Table1
) AS w
WHERE (
t.ParentID = w.ChilID
OR
t.ParentID = w.ChildID
)
AND t.CategoryID IN (’11’, ’22’, ’33’)
Nu börjar det likna något! Ett uttryck ska vara sant eller så ska samma uttryck igen vara sant. Det är en överflödig kontroll, så det slutade här med att jag skrev om frågan så den såg ut så här istället.
— Optimerad
SELECT t.*
FROM dbo.Table1 AS t
INNER JOIN dbo.Table1 AS w ON w.ChildID = t.ParentID
WHERE t.CategoryID IN (’11’, ’22’, ’33’)
Vad gjorde det då för prestandan? Med hjälp av SET STATISTICS TIME, IO ON fick jag följande resultat.
— Original
(391 row(s) affected)
Table ‘Worktable’. Scan count 8, logical reads 151599, physical reads 0.
Table ‘Table1’. Scan count 17, logical reads 1466, physical reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0.
SQL Server Execution Times:
CPU time = 11389 ms, elapsed time = 1873 ms.
— Omskrivning
(391 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0.
Table ‘Table1’. Scan count 2, logical reads 1256, physical reads 0.
SQL Server Execution Times:
CPU time = 733 ms, elapsed time = 732 ms.
Detta lilla matematiska knep gjorde att omskrivningen använder 15 ggr färre CPU samt slutförs 2,5 ggr snabbare än tidigare. När jag tittade närmare i exekveringsplanen såg jag att omskrivningen bara behöver 1 core medan originalfrågan la beslag på alla 16 cores.
Om det nu varit så att kolumnerna tillåtit NULL, hade jag varit tvungen att lägga till en extra kontroll som den här för att efterlikna originalfrågan till 100%.
— Optimerad med NULL
SELECT t.*
FROM dbo.Table1 AS t
INNER JOIN dbo.Table1 AS w ON w.ChildID = t.ParentID
WHERE t.CategoryID IN (’11’, ’22’, ’33’)
AND t.ChildID IS NOT NULL
