SQL Server - Finn lyckan i INNER LOOP
Förord
Microsoft SQL Server använder ibland hash och merge joins vid frågor mot stora tabeller, när okomplicerade nästlade joins skulle resultera i bättre prestanda och mindre påverkan på servern. I många sådana fall går frågetiden från många millisekunder till många sekunder på grund av att hash joins kräver att stora mängder data bearbetas och lagras temporärt, och merge joins kräver dels sortering och sedan bearbetning av likvärdigt stora mängder data.
Det här fungerar finfint för engångs frågor av mer administrativ art. "Faktafinnande" frågor där du inte behöver en optimerad fråga och är villig att vänta det antal sekunder, eller i värsta fall minuter, som det tar innan du får resultatet.
Innehåll
»»
»
»
»
»
Att tämja Joins i SQL Server:
Finn lyckan i INNER LOOP
av Michael Balloni
Däremot, för dagliga frågor från applikationer, vill du inte att databasmotorn ska beräkna ich sortera hundratusentals eller miljontals rader av data. Särskilt inte när slutresultatet består avv ett fåtal rader.
Ge SQL en vink
Vägen runt problemet med prestandaproblem sammanhörande med hash och merge joins är att använda INNER LOOP join för att vänligt men bestämt tvinga SQL att använda nästlade LOOP Joins exempelvis:-- Lista alla filer i "Michael's" konto.
SELECT Filename
FROM Accounts WITH (NOLOCK)
INNER LOOP JOIN Files WITH (NOLOCK) ON Files.AccountID =
Accounts.AccountID
WHERE Name = 'Michael'
-- OBS: Detta är inte en äkta Streamload (Streamload är det företag som författaren arbetar hos) fråga, inte heller representerar den vårt riktiga schema.
Exempel ur verkligheten
Här är några konkreta exempel som plockats ut från verkliga Streamload justeringar av frågor.Vart och ett av dessa exempel har skärmdumpar av execution plans, både före och efter. Så du kanske måste läsa på lite om hur man tyder dessa diagram (här är en MSDN länk som kanske kan vara till hjälp: http://msdn.microsoft.com/library/en-us/optimsql/odp_tun_1_1pfd.asp.)
Här kommer en snabbkurs i hur man läser execution plans: tjocka linjer är dåliga; de representerar hämtning och bearbetning av stora datamängder. Bortsett från de utmärkta execution paths som visas i varje exempel, gick varje fråga från att vara ett 1+ sekunders farthinder till att vara ett <0.1 sekunders sprinterlopp när vår INNER LOOP join var på plats.
Exempel 1
Det första exemplet är en fråga enligt modellen:
SELECT Table4.Column0
FROM Table1
JOIN Table2 -- ON conditions omitted.
JOIN Table3
JOIN Table4
Table1 är en liten tabell och Table2 är en stor. Table1 är så liten så att det borde ge kraft åt hela frågan, men lägg märke till den megre join som finns långt till höger som involverar Table1 och Table2, och hur den kräver fullständiga index scans och sorteringar för att driva denna join. Se execution plan.
Med INNER LOOP join hint på plats:
SELECT Table4.Column0
FROM Table1 INNER LOOP JOIN Table2 INNER LOOP JOIN Table3 INNER LOOP JOIN
Table4
Monstret tämjs så att en initial index scan ger fart åt en parallell exekvering av loops för att hämta resultatet. Se execution plan.
Exempel 2
Nästa exempel är en fråga enligt formatet:
SELECT Table3.Column0, COUNT(Table2.Column1)
FROM Table1 JOIN Table2 JOIN Table3
GROUP BY Table3.Column0
Än en gång, Table1 är liten nog att ge kraft åt hela frågan, men lägg märke till de tre fullständiga index scans som tar kraft pga merge och hash joins. Den som optimerat detta måste tro att titta genom index i Files.file0 kommer att kosta tillräckligt mycket för att rättfärdiga en hämtning av alla rader i Table2. Stort misstag. Se execution plan.
Med vårt hint på plats finns det bara en stor index scan, och föregående optimerare tror att en dykning ner i index för Files.file0 kommer att kosta mest, så vi har inte avvikit från det grundtänkandet, men volymen av data som hämtas är mycket mindre och så även den tid det tar för att köra frågan. Se execution plan.
Exempel 3
I det sista exemplet ser frågan ut såhär:
SELECT Table2.Column0, Table3.Column1
FROM Table1 JOIN Table2 JOIN Table3
WHERE Table1.Column2 >= @param1 AND Table1.Column2 <= @param2
Det här är en fint förpackad fråga där Table1 returnerar ganska få rader, men inte tillräckligt få för att rättfärdiga de två fullständiga index scans och den kostsamma sortering som kommer hand i hand med en merge join. Se execution plan.
Med vårt hint på plats så städas det hela upp riktigt tjusigt. Med delad kostnad mellan uppslag av rader i Table1 och loopande av träffar till index i Table2. Se execution plan.
Ett par varningens ord
Håll följande i minnet när du använder INNER JOIN hint:
1. Använd bara med frågor som returnerar få rader
Om din fråga returnerar många rader (>1000), eller behöver bearbeta många rader för att fungera korrekt, och du vet om det innan du börjar, så kommer den här metoden antagligen inte att hjälpa dig särskilt mycket med den frågan. Loops är enkla och effektiva när radantalet är litetm och du bör tvinga fram ett bra resultat genom join hint när du vet att radantalet är litet men sql inte gör det. Men när radantalet är stort, börjar djupa loops mångfaldiga den arbetsinsats som SQL måste göra. Rent kodmässigt så säljer SQL loopande enligt följande koncept:
loopande pseudo-kod för originalexempel
for each cur_accountid in accounts.where(name = "Michael").accountids
for each file in files.where(accountid = cur_accountid)
output file.filename
next
next
/looping pseudo-code
för hash kod:
hashing pseudo-code för originalexempel
files_hashtable = build_hashtable(files.accountids)
for each cur_accountid in accounts_hashtable.accountids
for each file in files_hashtable.find(cur_accountid)
output file.filename
next
next
/hashing pseudo-code
Hash-mekanismen är bra när det gäller många konton och många filer, för "files_hashtable.find(cur_accountid)" är mycket snabbare än "files.where(accountid = cur_accountid)" från loop-metoden. Det är bara själva uppbyggnaden av hash tabellen som tar mycket kraft. För merge joins är det sorteringen och datamängden som pumpas genom den för att möta en join och/eller frågekriterierna som tar kraften.
2. Skrapa och sniffa, var så god och skölj och börja om.
Ta en fråga som orsakar hash och/eller merge joins och sabbar din dag genom dåliga prestanda. Ordna till tabellernas ordning i dina joins, lägg sedan till INNER JOIN hint och se efter var det ryker någonstans genom att använda Query Analyzer, naturligtvis utan att påverka din applikation. Tro nu bara inte på vad jag säger utan prova själv och se hur tjusigt det ryker om frågorna.
Den här metoden kommer inte att ändra på korrektheten hos din fråga, men det är inte en "one size fits all" lösning. Du kommer kanske att upptäcka att du vill använda den här metoden selektivt inom vissa områden men inte använda den alls inom andra för att undvika djupa loops med stora datamängder. Det är fullkomligt vettigt att bara använda den här metoden för vissa joins i en fråga och inte i andra, om bra hashes eller merges annars faller bort i din fråga. Alla hashes och merges är inte dåliga, bara de som involverar bearbetning av stora datamängder när man egentligen bara behöver bearbeta små mängder.
3. Tabellernas ordning blir väldigt viktigt
Den största fallgropen när du använder denna metod är att den ordning i vilken du specificerar tabellerna i frågan blir den ordning som tabellerna bearbetas i av SQL, även om du bara använder metoden för en delmängd av dina joins i frågan. Detta betyder att du måste ordna tabellerna så att du har ett kriterie -- och index som stödjer dessa kriterier -- som snabbt beskär antalet rader som involveras så att SQL inte måste göra djupa loops genom stora mängder av data.
I det första "Lista alla filer i "Michael's" konto" exemplet ovan , antas det att jag har ett index på kolumnen Name för alla konton -- förhoppningsvis ett som även omfattar kolumnen AccountID för att undvika kostsamma uppslag av bokmärke -- och att endast ett AccountID skall genereras för att fråga in i Files, där jag - förmodar vi - också har ett index på AccountID. Om jag hade vänt på ordningen av tabellerna i min join, hade frågan exekverats otroligt dåligt. SQL skulle ha rusat igenom alla filer, bearbetat deras inte-så-unika AccountID'n, och sen loopat allt det mot tabellen accounts, och kontrollerat namnen på dessa konton mot WHERE villkoret. Väldigt illa. I grund och botten, när det gäller execution path och relationerna mellan dina kriterier och indexen du har tillgängliga, behöver du veta vad du pysslar med. För det ligger helt i dina händer med den här metoden.
4. IN Frågor fattar inte vinken..
Jag har inte hittat något sätt att använda den här metoden för IN frågor (som "SELECT * FROM Foo WHERE FooID IN (SELECT FooID FROM Bar)"), vilket är synd och skam för IN frågor har samma karakteristika -- och risker för oönskade hash och merge joins -- som rena joins. Det här har krävt en ansenlig arbetsinsats från min sida för att antingen handskas med extra join rader, eller This has required considerable effort on my part to either deal with extra join rows, eller att skriva om frågelogiken till en cursor alternativt ADO kod.
5. Det är komplicerat
Det vill säga SQL frågeoptimering. Det kan mycket väl vara så att det jag har observerat på Streamload's database servrar beror på att våra I/O och/eller minnessystem är tillräckligt effektiva för att göra de extra indexuppslag som orsakas av loopandet mindre kostsamma än skapande av hash tabeller eller körningar av merges. Eller kanske det är tvärtom. Kanske skapandet av hash tabeller kräver så mycket minne och/eller temporär databas I/O så att det går fortare för våra system att köra alla loopade indexuppslag. SQL's query analyzer tror att den kan lägga kostnader på olika typer av operationer, och den har mängder med olika statistisk information tillgänglig, och det kanske oftast stämmer... men å andra sidan, om den nu vet så mycket, varför var jag tvungen att skriva den här artikeln?
Ironi & Tragedi
Om du läser dokumentationen för hash och merge joins, kommer du att se den sofistikerade nivå som Microsoft försöker lägga in i försöken att få dessa metoder att fungera för väldigt stora databaser. Ironin i detta är att jag har en väldigt stor databas, och jag vill inte ha något att göra med hash eller merge joins för mina högtrafik-frågor, förutom de tidigare nämnda engångs administrativa frågorna, eller när det omfattar många rader.Du kan eventuellt få tragiskt hash eller merge uppförande även när du har bra index på plats. Jag är inte säker på varför detta händer: kanske dess statistik talar om för den att dess index inte är tillräckligt specifika, men det visar sig att du har så stor kännedom om din fråga så att du vet att indexen är tillräckligt bra, eller kanske har frågeoptimeraren felande logik när det kommer till kritan med djupt loopande strukturer.
Slutsats
Om du har frågor som brukade vara blixtsnabba och som inte returnerar eller bearbetar en stor mängd rader, och nu har börjat släpa sig fram, och det dyker upp hash och merge joins i dess execution paths, ge INNER LOOP join hint ett försök. Det kan spela dig ett spratt.. eller så visar det dig vägen tillbaka till den blixtsnabba respons som du hade tidigare.
Fräck reklam
Dessa och många andra avancerade metoder ger kraft åt www.streamload.com, en weblagrings och leverans sajt som erbjuder obegränsad och gratis lagring av filer online. Ladda upp alla dina filer från din dator för att sedan nå dem från varhelst du befinner dig.Finesserna omfattar slideshows av bilder, audio och video streaming och playlists, och strömlinjeformad administration av nedladdning. Plus att du kan dela och sälja dina digitala alster. Det här är inte en ny online drive sajt: Detta är din digitala underhållning, online.
Michael Balloni
Streamload Development
http://www.streamload.com
0 Kommentarer