SQL – sökargument och index

19 februari 2016

Väldigt många kan SQL och väldigt många av de många som kan SQL tycker att de är ganska vassa på SQL. Men när man börjar bli vass på SQL på riktigt så börjar man inse att man kanske inte var så jättevass på SQL som man tyckte att man var när man tyckte att man var ganska vass men inte var det egentligen.

Förvånansvärt ofta stöter jag på SQL-kod där index har satts ur spel på grund av att man inte känner till att index bara funkar med sökargument (fritt översatt från ”search argument”). Genom att känna till vad ett sökargument är och hur det lirar med index kan man med små medel få många frågor att gå betydligt snabbare och detta tänkte jag illustrera med ett enkelt exempel. Mitt exempel gäller TSQL och SQL Server.

Vi har en tabell, ”FactSales”, som innehåller fakturor. En faktura är lika med en rad i tabellen. Vi är nyfikna på hur många fakturor vi har dragit iväg hittills i år och klämmer till med en count(*) där år är lika med 2016. year(InvoiceDate) = 2016 känns fräscht och snyggt så vi kör på det:

Väldigt många fakturor, kul! Men frågan tog 28 sekunder och det skulle vi vilja snabba upp. Vi är BI-utvecklare och experter så därför vet vi att index är grymt bra för att få upp prestanda. Vi slänger på ett klustrat index på InvoiceDate och kör igen:

Ännu långsammare, 41 sekunder. Hur kan det komma sig? Vi provar att skriva om where-satsen och ersätter year(InvoiceDate) med en lite längre variant :

Mindre än 1 sekund. Tjoho! Men varför? Vi knäpper igång exekveringsplanen och kör frågorna igen och kollar vad som händer i bakgrunden.

Första frågan, den som körde helt utan index, gör en ”Table Scan”. Det betyder att frågan går igenom hela tabellen för att kolla om en faktura tillhör 2016 eller inte och det är ju inte så konstigt att det blir långsamt:

Andra frågan, den som körde med index på InvoiceDate men ändå var långsam, gör en ”Clustered Index Scan”. Det betyder att frågan skannar hela indexet för att kolla om en faktura tillhör 2016 eller inte och det är ju inte heller så effektivt:

Sista frågan, den som tog mindre än 1 sekund, gör en ”Clustered Index Seek”, vilket betyder att den tar hjälp av indexet och bara tar ut de fakturor som tillhör 2016 utan att bry sig om resten av tabellen. Precis det som vi ville uppnå med indexet:

De olika svarstiderna beror på att om en kolumn ingår i ett index och kolumnen används i where-satsen (eller i en join) så funkar indexet bara om villkoret utformas som ett sökargument. Med andra ord, utforma alltid villkor som sökargument om index är inblandade så utnyttjas indexet maximalt.

För att lära sig vad ett sökargument är så är det lättaste att lära sig vad ett sökargument inte är. En enkel tumregel är att om man hittar på nått bus med kolumnen, i vårt exempel InvoiceDate, så är det inte ett sökargument.

year(InvoiceDate) = 2016 är inte ett sökargument.
InvoiceDate >= ’2016-01-01’ är ett sökargument.

coalesce(InvoiceDate,”) = coalesce(@date,”) är inte ett sökargument.
InvoiceDate = @date or InvoiceDate IS NULL är ett sökargument.

Så nästa gång du undrar varför en fråga är långsam, börja med att kontrollera att det finns index på tabellen och att villkoren i where-satsen är utformade som sökargument.