Prestandaoptimera SQL Server JOINs
Förord
Om du regelbundet utför JOINs mellan två eller flera tabeller i dina SQL-satser så kan du optimera prestandan genom att låta var och en av de relaterade kolumnerna ha sina egna Index. Det inkluderar att lägga till Index på de kolumner i varje tabell som används för att JOINa samman tabellerna. [SQL Server 6.5, 7.0, 2000]
Innehåll
Prestandaoptimera SQL Server JOINs
När du använder Transact-SQL för att skapa JOINs så har du möjlighet att välja mellan två olika syntaxtyper: antingen ANSI eller Microsoft. ANSI refererar till den ANSI standard som används för att skriva JOINs, och Microsoft refererar till den gamla Microsoft stilen att skriva JOINs på. Se exempel:
ANSI JOIN syntax:
SELECT fname, lname, department
FROM names INNER JOIN departments
ON names.employeeid = departments.employeeid
Den gamla Microsoft JOIN syntaxen:
SELECT fname, lname, department
FROM names, departments
WHERE names.employeeid = departments.employeeid
Om de skrivs korrekt så kommer båda metoderna att producera identiska resultat. Men det är ett stort OM. Det händer lätt att man gör misstag med den äldre Microsoft JOIN syntaxen, eftersom det är lätt att göra misstag när man inte är medveten om det. ANSI syntaxet däremot är väldigt explicit, så det finns inga möjligheter att göra misstag där.
Jag stötte t ex nyligen på en långsamt presterande SQL-sats från ett ERP program. Efter att kollat igenom koden, vilken använde Microsofts JOIN syntax, så märkte jag att istället för att ha skapat en LEFT JOIN så hade utvecklaren av misstag skapat en CROSS JOIN istället. I just det här fallet så skulle färre än 10 000 poster ha returnerats med LEFT JOIN, men eftersom det var en CROSS JOIN som användes så returnerades istället över 11 miljoner poster. Sedan använde utvecklaren en SELECT DISTINCT för att sålla bland alla de onödiga poster som returnerades med CROSS JOIN. Som du kan förstå så blev det en väldigt långvarig SQL-sats. Tyvärr så var allt jag kunde göra att meddela försäljarens supportavdelning om detta.
Sensmoralen med den här historien är att du helst bör använda ANSI syntaxen, och inte den äldre Microsoft syntaxen. Förutom att det reducerar oddsen för att göra dumma misstag så är den här koden mer portabel mellan databaser. Och jag hoppas att Microsoft snart slutar att stödja det gamla formatet, och istället gör ANSI syntaxen till den enda metoden. [SQL Server 6.5, 7.0, 2000] Inlagd 02-05-03.
*****
Kom ihåg att det inte automatiskt skapas något Index samtidigt som du skapar externa nycklar. Om du någonsin planerar att JOINa en tabell till en tabell med den externa nyckeln, och då du tänker använda de externa nyckeln som den relaterade kolumnen, så bör du överväga att lägga till ett Index på kolumnen med den externa nyckeln. Ett Index på en kolumn med en extern nyckel kan höja prestandan betydligt på många JOINs. [SQL Server 6.5, 7.0, 2000] Inlagd 02-02-08.
*****
För maximal prestanda då du relaterar två eller fler kolumner så bör de relaterade kolumnerna ha samma datatyper. Det innebär också att du inte bör blanda icke-Unicode med Unicode datatyper då du använder SQL Server 7.0 eller senare. (Inte heller VARCHAR med NVARCHAR). Om SQL Server tvunget måste konvertera datatyperna för att kunna utföra en JOIN så saktar det inte bara ner JOINing processen, utan det kan också innebära att SQL Server gör en tabellscan istället för att använda sig av något tillgängligt Index. [SQL Server 6.5, 7.0, 2000] Uppdaterad 01-01-02.
*****
För bästa JOIN prestanda så bör Indexen på de kolumner som ska JOINas vara numeriska datatyper, inte CHAR eller VARCHAR. Då blir arbetslasten lägre och JOIN prestandan snabbare. [SQL Server 6.5, 7.0, 2000]
*****
Om du ofta och regelbundet måste JOINa fyra eller fler kolumner för att få det recordset du behöver, så bör du överväga att denormalisera tabellerna så att antalet relaterade tabeller reduceras. Du kan genom att lägga över en eller ett par kolumner från en tabell till en annan, ofta reducera antalet JOINs. [SQL Server 6.5, 7.0, 2000]
*****
Om din JOIN är långsam och för tillfället inkluderar ’hints’, så kan du ta bort dessa hints för att se ifall Optimizern bättre klarar av att optimera dina JOINs än vad du kunde. Det är särskilt viktigt då du har uppgraderat din applikation från 6.5 till 7.0, eller från 7.0 till 2000. [SQL Server 6.5, 7.0, 2000]
*****
Du bör inte använda * (asterisk) i SELECT uttrycket som skapar din JOIN, för att returnera alla kolumner i båda tabellerna. Det är inte bra på grund av följande två anledningar. För det första så bör du bara returnera de kolumner som du behöver, eftersom din SQL-sats kommer att prestera snabbare, ju mindre data du returnerar. Det skulle vara mycket sällan som du egentligen behöver alla kolumner från alla tabeller som du har JOINat ihop. För det andra så kommer du att returnera två av varje kolumn som du har angivit i JOIN villkoret. Det leder till att du returnerar mycket mer data än vad du faktiskt behöver, och det skadar prestandan.
Ta en titt på följande två SQL-satser:
USE NorthWind
SELECT *
FROM Orders
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
USE NorthWind
SELECT Orders.OrderID, Orders.OrderDate,
[Order Details].UnitPrice, [Order Details].Quantity,
[Order Details].Discount
FROM Orders
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
Båda av dessa SQL-satser utför egentligen samma sak. Problemet med den första SQL-satsen är dock att den inte bara returnerar för många kolumner (alla behövs inte i den här applikationen), utan den returnerar också OrderID kolumnen två gånger, vilket inte ger några användbara fördelar. Båda dessa problem bidrar till onödig arbetslast för Servern, vilket skadar prestandan. Sensmoralen här är att aldrig använda * i dina JOINs. [SQL Server 6.5, 7.0, 2000] Inlagd 01-01-19.
*****
Undvik att JOINa tabeller som är baserade på kolumner med få unika värden. Om de kolumner som används till relationerna inte för det mesta är unika så kommer SQL Server att utföra en tabellscan för relationen, trots att det kanske finns Index på kolumnerna. Det idealiska för prestandan vore om relationerna görs på kolumner med unika Index. [SQL Server 6.5, 7.0, 2000] Uppdaterad 00-08-18.
*****
Trots att hög Indexselektivitet generellt sett är en viktig faktor för att din Query Optimizer ska kunna se huruvida en SQL-sats är användbar eller inte, så finns det faktiskt ett speciellt fall där Index av låg selektivitet kan vara användbart för att höja hasigheten på SQL Server. Det handlar om Index med externa nycklar. Vare sig ett Index på en extern nyckel har en hög eller låg selektivitet, så kan ett Index på en extern nyckel användas av Query Optimizer för att utföra en Merge Join på tabellerna i fråga. En Merge Join uppstår då man tar en post från varje tabell och jämförs för att se om de matchar den specifika Join kriterian. Så länge som de JOINade tabellerna har de lämpliga Indexen (oavsett selektivitet) så kan en Merge Join utföras, vilket generellt sett är mycket snabbare än att JOINa tabeller med externa nycklar som inte något Index. [SQL Server 7.0, 2000] Inlagd 01-04-09.
*****
För väldigt stora JOINs så bör du överväga att placera de kolumner som ska JOINas i separata fysiska filer i samma filgrupp. Det låter SQL Server att skapa separata trådar för varje fil som accessas, vilket höjer prestandan. [SQL Server 6.5, 7.0, 2000]
*****
Om du har två eller fler tabeller som JOINas ihop väldigt ofta, så bör de kolumner som används för JOINerna ha lämpliga Index. Om de kolumner som används i JOINerna inte är naturligt kompakta så bör du överväga att lägga till surrogatnycklar till de tabeller som är kompakta. Det bör du göra för att kunna reducera storleken på nycklarna och därmed minska den I/O last som krävs under JOIN processerna, vilket höjer prestandan. [SQL Server 6.5, 7.0, 2000]
*****
Använd inte CROSS JOINs, så till vida att det inte är det enda sättet att uppnå ditt mål på. Det många oerfarna utvecklare gör är att de binder samman två tabeller med CROSS JOIN, för att sedan använda DISTINCT eller GROUP BY klausulerna för att ”städa upp” efter den sörja de har skapat. Det här kan som du förstår bli ett väldigt slöseri på SQL Serverns resurser. [SQL Server 6.5, 7.0, 2000] Inlagd 00-11-28.
*****
Om du har möjligheten att välja mellan en JOIN och en subsats för att utföra samma sak, så är generellt sett JOIN (ofta en OUTER JOIN) snabbare. Men inte alltid. Om du t ex ska returnera små mängder data, eller om det inte finns några Index på de relaterade kolumnerna, så kan en subsats definitivt vara snabbare.
Det enda sättet att veta säkert är genom att pröva båda metoderna och sedan granska deras Query Plans. Om den här operationen körs ofta så bör du allvarligt överväga att skriva en kod för varje metod, och sedan välja den kod som är mest effektiv. [SQL Server 6.5, 7.0, 2000] Uppdaterad 01-09-12.
*****
Vi har en SQL-sats som innehåller två subselects, som i sin tur innehåller en ”förminsknings” funktion (SUM, COUNT, osv) i SELECT delen. Den SQL-satsen presterade väldigt långsamt. Men vi lyckades lokalisera problemet ner till förminskningsfunktionen i subselecten.
För att lösa problemet så reorganiserade vi SQL-satsen så att det fortfarande fanns en förminskningsfunktion i SELECT delen, men vi bytte ut subselecten mot en serie av JOINS. SQL-satsen exekverades sedan mycket snabbare.
Så om ni har det så här; Utvecklare; Ni bör som regel använda JOINs istället för subselects, då subselecten innehåller förminskningsfunktioner. [SQL Server 7.0, 2000] Tipset erhölls av
*****
Om du har en SQL-sats med många JOINs så finns det ett alternativ att denormalisera tabellen för att höja prestandan, och det är genom att använda en Indexerad vy för att för-JOINa tabellerna. En Indexerad vy, vilken bara är tillgänglig från SQL Server 2000 Enterprise Edition, låter dig skapa en vy som faktiskt är ett fysiskt objekt med ett eget Clustrat Index. När som helst som bastabellen i den Indexerade vyn uppdateras så uppdateras även den Indexerade vyn. Som du kanske kan förstå så kan detta även reducera INSERT-, UPDATE- och DELETE prestanda på bastabellen. Du måste förstås utföra tester och jämföra prestandans för- och nackdelar, för att kunna se om prestandakostnadenen är värt besväret då du använder Indexerade vyer istället för JOINs. [SQL Server 2000] Inlagd 02-02-11.
*****
Om du har en SQL-sats som använder en LEFT OUTER JOIN så bör du överväga den noggrant för att se om det är just den typen av JOIN som du egentligen vill använda. Som du kanske vet så används en LEFT OUTER JOIN till att skapa ett resulterande set som inkluderar alla poster från den vänstra kolumnen som har specificerats i klausulen, och inte bara dem som matchas i de relaterade kolumnerna. Om en post från den vänstra tabellen dessutom inte har några matchande poster i den högra kolumnen, så kommer det resulterande setet innehålla NULL värden för alla valda kolumner som kommer från den högra tabellen. Om det är så du vill ha det så kan du använda den här typen av JOINs.
Problemet är att i verkligheten så behövs sällan en LEFT OUTER JOIN, och många utvecklare använder de av misstag. Trots att du kanske får ut just de data du vill ha så kan du även få ut mer data än vad du behöver, vilket bidrar till onödig arbetslast och sämre prestanda. På grund av det så bör du alltid fundera på varför du använder LEFT OUTER JOIN i en SQL-sats, och att du bara använder dem då det är exakt vad du behöver. Annars bör du använda en JOIN som är mer lämpad till dina behov. [SQL Server 6.5, 7.0, 2000] Inlagd 02-03-27.
*****
Om du har problem med att optimera prestandan på en dåligt presterande SQL-sats med en eller flera JOINs så kan du se ifall den Query Plan som skapades av Query Optimizer använder sig utav en Hash JOIN. Då Query Optimizer ombes att skapa en JOIN mellan två tabeller som inte använder sig lämpliga Index, så utför den ofta en Hash JOIN.
En Hash JOIN är resurskrävande och kan sakta ner prestandan på din JOIN. Om SQL-satsen i fråga körs ofta så bör du då överväga att lägga till lämpliga Index. Om du t ex JOINar kolumn1 i tabell1 med kolumn5 i tabell2, så behöver kolumn1 i tabell1 samt kolumn5 i tabell2 ha Index.
När du väl har lagt till Index till de kolumner som ska JOINas i din SQL-sats, så är det högst troligt att Query Optimizer kan använda sig utav de Indexen och istället för att utföra en Hash JOIN så kommer den då att utföra en nested-loop JOIN – vilket höjer prestandan. [SQL Server 7.0, 2000] Inlagd 02-04-09.
0 Kommentarer