Trixa med SQL - Uppföljning
Förord
Denna artikel är en uppföljning till den tidigare artikeln Uppgift: Trixa med SQL, där jag presenterade en uppgift som skulle lösas i SQL. Här sammanfattar jag de svar jag fick in, samt mina egna förslag på lösningar.Innehåll
»»
»
»
»
»
»
»
»
Relaterade artiklar
» Trixa med SQL - Uppgift» Verktyg för att mäta prestanda i SQL Server
Uppgiften som skulle lösas
Uppgiften var ganska enkel, givet tabelldefinitionen nedan skulle man få fram vilken chef som har flest anställda. Tabellen fylldes med 9960 rader för att få lite att testa med. Dels så ville jag ha lösningar som är optimerade för bästa prestanda, men samtidigt så var jag intresserad av vilka olika sätt problemet går att lösa. För det var egentligen huvudmeningen, att visa hur många och olika sätt det finns att lösa en uppgift med SQL. Alla resultat som presenteras nedan är hämtade från när jag körde frågorna på min dator, en P4 2.2 GHz med 512 MB RAM. Även om det är tiderna relativt varandra som är viktigt så kan det vara intressant att veta hur fort SQL-satser presterar. Hur jag fick fram värdena kan ni läsa om i artikeln som finns i relaterade artiklar ovan. För att ha som jämförelse har jag även inkluderat en SQL-sats som hämtar hela tabellen, och hur mycket resurser det tog.
CREATE TABLE EMPLOYEESBOSSES
(
employee varchar(50) NOT NULL
, boss varchar(50) NULL
)
CREATE CLUSTERED INDEX ixcemployeesbosses ON EMPLOYEESBOSSES (boss,employee)
SELECT * FROM EMPLOYEESBOSSES
-- Scan count 1, logical reads 60
-- CPU time = 0 ms, elapsed time = 1309 ms.
TOP i SQL Server
Det vanligaste förslaget och även det första jag tänkte på var att använda nyckelordet TOP i SQL Server för att välja den med flest anställda. Det blev lite olika förslag på hur en SQL-fråga med TOP kan se ut, här är några av dem:
-- Förslag #1A
SELECT TOP 1 COUNT(boss) AS Expr1, boss
FROM dbo.EMPLOYEESBOSSES
GROUP BY boss
ORDER BY COUNT(boss) DESC
-- Scan count 1, logical reads 60
-- CPU time = 15 ms, elapsed time = 16 ms.
-- Förslag #1B
SELECT TOP 1 boss, COUNT(*)
FROM EMPLOYEESBOSSES
WHERE boss IS NOT NULL
GROUP BY boss
ORDER BY COUNT(*) DESC
-- Scan count 1, logical reads 59
-- CPU time = 14 ms, elapsed time = 14 ms.
-- Förslag #2
SELECT TOP 1 employee,
(SELECT COUNT(employee) FROM EMPLOYEESBOSSES e2 WHERE e2.boss = EMPLOYEESBOSSES.employee) AS Antal
FROM EMPLOYEESBOSSES
GROUP BY employee, boss
HAVING BOSS IS NULL
ORDER BY Antal DESC
-- Scan count 523, logical reads 1150
-- CPU time = 28 ms, elapsed time = 28 ms.
Det första förslaget (#1A) skickades in av Magnus, det andra (#1B) är min egen variant på samma tillvägagångssätt (JohanD skickade också in ett förslag som var nästan identiskt med detta) och det tredje (#2) står Trash och budda tillsammans bakom. Alla tre använder alltså TOP på ett eller annat sätt, men det finns ändå vissa skillnader. Dels så använder #1A COUNT(boss) för att räkna antalet rader. #1B däremot använder COUNT(*) (läs mer om dessa skillnader i min artikel om COUNT()), men eftersom samma index används i bägge fallen för att räkna så gör det ingen skillnad just här. En annan skillnad mellan frågorna är användandet av WHERE-klausulen i #1B för att endast hämta de rader där boss inte är NULL. Även detta gör dock ingen större skillnad här, eftersom det enda det påverkar är att SQL Server behöver läsa en sida mindre för att räkna genom raderna. Skulle det vara väldigt många med NULL i kolumnen boss så skulle det kanske bli ett par sidor mer. Det viktiga är iaf att notera och vara medveten om att det blir skillnad.
Förslag #2 däremot har ett lite annorlunda sätt att lösa det. Först grupperar den alla anställda och deras chef, sedan slänger den alla som har någon chef (HAVING boss IS NULL). För dessa anställda (522 st) gör den sedan en COUNT() för att se hur många som har dem som chef. Som synes resulterar detta i en väldig massa läsningar, en scan för varje chef, och varje scan blir ett antal sidor som ska scannas genom. Visst, det tog bara 14 ms längre, men det är dock dubbelt så lång tid, och framförallt så är det en väldig skillnad i hur mycket resurser som går åt.
Kör man SQL Server och det är OK att binda sig till det, då är förslag #1A eller #1B utmärkta sätt att lösa denna uppgift.
Standard SQL med en vy
Om ni testade att skapa en lösning i standard SQL (dvs som håller sig till Core SQL-99) så märkte ni nog att TOP inte fungerar där. Detta nyckelord är specifkt för SQL Server, och andra DBMS varianter på det (t ex LIMIT i MySQL) är även de implementationsspecifika. Det finns inget sätt i SQL-99 att ange TOP, eftersom det egentligen inte tillhör SELECT-satsen utan snarare den cursor som SELECT-satsen genererar för att hämta resultatet. Nog om det, nu ska vi se två sätt man kan lösa uppgiften på i SQL-99.
-- Förslag #3
CREATE VIEW BOSSEMPS (boss, emps)
AS SELECT boss, COUNT(*)
FROM EMPLOYEESBOSSES
WHERE boss IS NOT NULL
GROUP BY boss
SELECT boss, emps
FROM BOSSEMPS
WHERE emps = (SELECT MAX(emps) FROM BOSSEMPS)
-- Scan count 1, logical reads 59
-- CPU time = 13 ms, elapsed time = 13 ms.
Ett sätt att lösa specifika problem i SQL är att ta det i flera steg, genom att skapa en vy som löser en del av problemet. Det är ett tillvägagångssätt som oerfarna SQL programmerare ofta inte tänker på, men det är alltså något jag klart rekommenderar. Hanterandet av vyer är oftast väldigt optimerat i de flesta databashanterare, och komplexa problem kan ibland knappt lösas utan dem. Vad vi gör i detta fall är att vi skapar en vy över alla chefer samt hur många anställda de har, och sedan hämtar vi den chef som har flest anställda. Låter det som vad nyckelordet TOP används till? Helt rätt, det är ungefär samma tankar och logik där. Tittar jag i exekveringsplanen för SELECT-frågan mot vyn så ser jag dessutom en TOP, dvs SQL Server har valt att exekvera min standard SQL-sats med metoder som är specifikt för den, men det är inget fel i det, bara min fråga går att köras på alla DBMS som stöder Core SQL-99. Även om exekveringsplanen inte är exakt likadan för förslag #3 som den för #1B så ser ni att resursanvändandet och tidsåtgången är nästintill identiskt. Detta är alltså en utomordentlig lösning på problemet.
Standard SQL utan vy
Ibland vill man kanske inte använda sig av vyer eller andra 'extra' objekt för att lösa en uppgift i SQL.
-- Förslag #4
SELECT boss, COUNT(*)
FROM EMPLOYEESBOSSES
WHERE boss IS NOT NULL
GROUP BY boss
HAVING COUNT(*) + 1 > ALL (SELECT DISTINCT COUNT(*)
FROM EMPLOYEESBOSSES
WHERE boss IS NOT NULL
GROUP BY boss)
-- Scan count 4, logical reads 180
-- CPU time = 39 ms, elapsed time = 39 ms.
Som synes är denna lösning inte lika optimal som varken de SQL Server-specifika TOP-varianterna eller vylösningen, men den håller sig till standard SQL och den är en enskild SELECT-sats. Det kan ju finnas t ex säkerhetsskäl som gör att man inte har rättighet att ex. skapa en vy.
En Oracle-variant
Ulf M skickade in en lösning som fungerar i Oracle.
-- Förslag #5
SELECT eb1.boss, COUNT(*) AS Antal
FROM EMPLOYEESBOSSES eb1
WHERE eb1.boss IS NOT NULL
GROUP BY eb1.boss
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM EMPLOYEESBOSSES eb2
WHERE eb2.boss IS NOT NULL
GROUP BY eb2.boss)
Tyvärr har jag inte några siffror på hur bra prestanda den har då jag inte haft möjlighet att testa den i Oracle (och siffrorna hade ändå inte varit riktigt samma typ av siffror som i SQL Server), och då den inte fungerar i SQL Server har jag inte kunnat testa den där heller. En gissning skulle dock vara att den får ungefär samma siffror som förslag #4 då de är mycket lika varandra. Vad som är mer intressant är att den faktiskt inte följer standard SQL, tvärtom så bryter den t.o.m mot en regel i standarden! Validatorn jag länkade till för att testa om en viss SQL-sats är standard säger dock inget om detta, men det beror på att den ej kan se semantiska fel i texten. Den regel frågan bryter mot är att man inte får ha en aggregerad funktion (eller set functions som de kallas i standarden) inuti en annan, dvs MAX(COUNT(*)) i exemplet ovan. Varför Oracle har valt att tillåta detta vet jag inte, men egentligen är det ju faktiskt inte något annat än en omskrivning av förslag #4.
Andra förslag
Andra förslag jag fick in inkluderade ett antal varianter på TOP, samt nedanstående förslag som använder sig av en temporär tabell, inskickat av JohanD.
-- Förslag #6
CREATE TABLE #test (boss varchar(50) NOT NULL, antal int)
INSERT INTO #test (boss, antal)
SELECT boss, COUNT(*)
FROM EMPLOYEESBOSSES
WHERE boss IS NOT NULL
GROUP BY boss
DECLARE @maxantal int
SELECT @maxantal = MAX(antal) FROM #test
SELECT boss FROM #test WHERE antal = @maxantal
DROP TABLE #test
Som ni kanske noterar så är lösningen mycket lik den i förslag #3 med en vy, men prestandamässigt blir det vissa skillnader. Det beror på att i förslag #6 så måste alla raderna verkligen kopieras in i temptabellen innan man kan hämta det högsta värdet från den, medan i vyn (som ju endast är en virtuell tabell) kan optimeraren i SQL Server lösa detta på bättre vis. Hur mycket prestanda och resurser som gick åt för förslag #6 är inte helt enkelt att skriva eftersom det sker i flera steg, men den totala exekveringstiden blev åtminstone 46 ms.
SQL Server - snabbaste lösningen
Slutligen tänkte jag avsluta med det snabbaste sättet det går att lösa detta på i SQL Server 2000. Notera att detta är dels SQL Server specifikt iom användandet av TOP, och användandet av en indexerad vy gör att det enbart fungerar i SQL Server 2000.OBS! Notera att värdena för exekveringstid och IO-utnyttjande i både detta exempel och förslag #3 EJ inkluderar tiden att skapa vyn (och indexet i detta fallet), utan bara gäller själva SELECT-satsen mot vyn.
-- Förslag #7
CREATE VIEW BOSSEMPS2 (boss, emps)
WITH SCHEMABINDING
AS SELECT boss, COUNT_BIG(*)
FROM dbo.EMPLOYEESBOSSES
WHERE boss IS NOT NULL
GROUP BY boss
GO
CREATE UNIQUE CLUSTERED INDEX ixvbossemps2 ON BOSSEMPS2 (boss)
GO
SELECT boss, emps
FROM BOSSEMPS2
WHERE emps = (SELECT MAX(emps) FROM BOSSEMPS)
-- Scan count 2, logical reads 8
-- CPU time = 0 ms, elapsed time = 1 ms.
En indexerad vy är som namnet antyder en vy med ett index på den, vilket gör att vyn (som normalt är helt virtuell, dvs endast skapas vid frågor mot den) nu lagras på disk i ett index. Ställer vi sedan frågan mot den så får vi ypperlig prestanda och resursutnyttjande, men nu har vi alltså bundit oss till en väldigt specifik DBMS. En intressant sak att notera är att om denna vy och indexet på den finns skapad under körningen av vissa av de andra förslagen kommer optimeraren att se detta och inse att denna fungerar bäst, och därmed välja den istället för de exekveringsplaner som valts vid körningarna jag gjort ovan.
0 Kommentarer