Förord

Du kan skriva ett SELECT uttryck på flera olika sätt för att få ut samma resultat, men vissa av de olika variationerna presterar bättre än andra. I den här artikeln ska vi titta på hur vi kan dra fördel av det.

Innehåll

  » Härledda tabeller
  » Avslutning

Tips för snabbare SELECT kommandon i SQL Server

av Neil Boyle

Här följer en SQL-sats som jag klippt-och-klistrat direkt från ”SQL 7 Books online”. SQL-satsen körs mot från en Northwind databas och är designad till att ta ut det maximala enhetspriset för varje order i databasen.


SELECT Ord.OrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice)
FROM Northwind.dbo.[Order Details] AS OrdDet
WHERE Ord.OrderID = OrdDet.OrderID) AS MaxUnitPrice
FROM Northwind.dbo.Orders AS Ord


Den här typen av SQL-satser kallas “Correlated Subquery” (Länkade Subsatser) – du kan se att den består av två SELECT uttryck. Det första ”huvud” uttrycket (SELECT ord.OrderID, ord.OrderDate) plockar ut order ID och datum från ordertabellen, medan ”sub” uttrycket plockar ut det maximala enhetspriset för varje order.

Subuttrycket körs en gång för varje post som huvuduttrycket släpper igenom, och denna repeterande processen att komma åt [Order Details] tabellen kan visa sig vara relativt ineffektiv.

Books Online nämner vidare att SQL-satser som den ovan kan skriva om till simpla JOINs. Exemplet nedan använder en INNER JOIN mellan Orders- och [Order Details] tabellerna i samband med en MAX() funktion, och får fram samma resultat som exemplet ovan – bara mer effektivt:


SELECT Ord.OrderID, Ord.OrderDate,
MAX(OrdDet.UnitPrice) AS maxUnitPrice
FROM Northwind.dbo.[Order Details] AS OrdDet
INNER JOIN
Northwind.dbo.Orders AS Ord
ON Ord.OrderID = OrdDet.OrderID
GROUP BY Ord.OrderID, Ord.OrderDate


Trots att båda SQL-satserna returnerar samma data, så indikerar Query Analyzer på att det andra exemplet kräver 40 % mindre av SQL Servers resurser än det första exemplet. Så du får inget pris om du gissar vilken av dem som du bör använda. I vissa fall finns det dock ett tredje alternativ att tillgå för att förbättra prestandan ytterligare.

Härledda tabeller

Att använda härledda tabeller är lika effektivt som att använda temporära tabeller, dock utan allt krångel med att specifikt skapa den och hänvisa till den. Jag har gjort om ovanstående BOL sats till att använda härledda tabeller:


SELECT Ord.OrderID, Ord.OrderDate, maxUnitPrice
FROM Northwind.dbo.Orders AS Ord INNER JOIN
(
SELECT orderID,
MAX(UnitPrice) AS maxUnitPrice
FROM Northwind.dbo.[Order Details]
GROUP BY OrderID
) AS OrdDet
ON ordDet.orderID = Ord.orderID
ORDER BY Ord.OrderID DESC, Ord.OrderDate, maxUnitPrice


Koden inom parenteserna får SQL Serverns att generera en teoretisk (eller härledd) tabell kallad OrdDet som hålls kvar medan SQL-satsen körs. Den härledda tabellen tar teoretiskt sett upp mindre plats än den vanliga [Order Details] tabellen, eftersom den bara innefattar två kolumner samt en detalj post för varje order. Genom det här borde min härledda-tabell-version av SQL-satserna gå snabbare än min INNER JOIN-version. När jag jämförde exekveringsplanen för min härledda-tabell-version mot planen för min INNER JOIN-version för att se hur mycket prestanda jag tjänade, så fick jag fram att resultaten var… exakt likadana!



Fanken!
Jag fick inga förbättringar där alls! Båda SQL-satserna genererade samma exekveringsplan och de krävde lika mycket resurser från SQL Servern för att returnera data. Så har min teori flugit all världens väg? Inte riktigt…

Nyckeln till att kunna förstå hur du med härledda tabeller kan – eller inte kan – skapa effektivare resultat, ligger i att förstå din Query Optimizer.

Query Optimizer analyserar alla SQL-satser, huvudsakligen genom att analysera Index statistiken, för att på så sätt finna ett effektivare sätt till att komma åt tabellen som SQL-satsen använder sig utav. Trots att jag, genom att ändra i min SQL-kod, har gett SQL Server andra instruktioner för hur den ska hämta ut mina önskade data, så har SQL Server i båda fallen bestämt att denna metod – eller exekveringsplan – är den optimala. Så är det dock inte alltid.

Genom att följa exakt samma princip så följer nu en GROUP BY sats och en sats med härledda tabeller, av vilka båda returnerar samma data men som genererar helt olika exekveringsplaner. Återigen så används båda satserna mot en Northwind databas.


SELECT companyName,
MAX(orderDate)
FROM orders o INNER JOIN customers c
ON o.customerID = c.customerID
GROUP BY companyName


SELECT companyName, MAX(orderDate)
FROM customers c INNER JOIN (
SELECT customerID, MAX(orderDate) AS orderDate
FROM orders
GROUP BY customerID) AS o
ON o.customerID = c.customerID
GROUP BY companyName


Den här gången väljer Optimizern att använda olika exekveringsplaner för SQL-satserna, och den satsen som hanterar härledda tabeller visar en förbättring på ca 30 % vad det gäller resurser som krävs för att köra den.

Avslutning

Som du kan se i exemplen, så kan Optimizern ibland behöva lite hjälp på traven då den ska välja det effektivaste sättet att köra en SQL-sats på. Det kan ibland vara värt att skapa flera varianter av de mest kritiska satserna, och sedan jämföra deras karaktäristiska prestanda för att finna det effektivaste sättet att utföra något på.

0 Kommentarer

Skriv en kommentar på artikeln

Ditt betyg på artikeln

UtmärktLäsvärdIntressantMindre braDålig

Kommentar: