Undvik temporära tabeller för prestandan skull
Förord
Allteftersom SQL-satserna blir mer invecklade så ökar även användandet av temporära tabeller. Trots att det ibland kan vara oundvikligt att använda temporära tabeller, så kan det gå att komma runt det genom att istället använda sig utav härledda tabeller. I korta ordalag så kan du skapa en härledd tabell genom att i FROM klausulen i en SELECT-sats lägga in ytterligare en SELECT-sats. Genom att använda härledda tabeller istället för temporära tabeller så kan vi höja applikationens prestanda. Låt oss titta närmare på det.
Undvik temporära tabeller och tjäna prestanda
by Justin Gunther
Hur temporära tabeller påverkar prestanda
Temporära tabeller sänker applikationens prestanda dramatiskt. Problemet med temporära tabeller är bland annat mängden arbetslasten som den drar till sig. För att kunna skapa så snabba SQL-satser som möjligt så måste du få dem att utföra så lite som möjligt. Ta t ex en SELECT-sats; SQL Servern läser data från tabellen och returnerar de valda posterna. Temporära tabeller kräver dock att systemet ska göra mycket mer än så. En del från t ex en Transact-SQL kod kommer vanligtvis med hjälp av temporära tabeller att:
1) CREATE (skapa) en temporär tabell
2) INSERT (lägga in) data i den nyligen skapade tabellen
3) SELECT (välja ut) data från tabellen (oftast genom att med JOIN koppla den till andra fysiska tabeller) medan den låser den temporära databasen tills transaktionen är klar.
4) DROP (släpper) den temporära tabellen
Detta kräver en hel del hårddiskaktivitet samt skapar potentiella tvistproblem. Och allt det här resulterar i sämre prestanda.
Eliminera ett par steg!
Den största fördelen med att använda härledda tabeller istället för temporära tabeller är att de kräver färre steg i utförandet, och allting sker i minnet istället för både i minnet och på hårddisken. Ju färre steg det behöver gå igenom, vilket också kräver mindre I/O, desto snabbare fungerar prestandan.Dessa steg måste genomföras då du använder temporära tabeller:
1) Lås den temporära databasen
2) Skapa den nya temporära tabellen (skrivaktivitet)
3) Välj data och/eller lägg in data (läs- och skrivaktivitet)
4) Välj data från både den temporära tabellen och de(n) permanenta tabellen/tabellerna (läsaktivitet)
5) Släpp den temporära tabellen (skrivaktivitet)
6) Lås upp den temporära databasen
Jämför ovanstående med det antal steg det krävs för en härledda tabell:
1) Skapa lås, så till vida att du inte använder isoleringen ”read uncommitted”
2) Välj data (läsaktivitet)
3) Lås upp
Som exemplet påvisar så är det ganska tydligt att det kräver mindre I/O last och höjer prestandan då du använder härledda tabeller i stället för temporära tabeller. Låt oss se hur.
Hur du använder härledda tabeller
Härledda tabeller är i stort sett bara SELECT-satser inom SELECT-satser. Låt oss titta på ett mycket enkelt exempel.Titta på den här simpla SQL-satsen där vi hämtar ut data ur en tabell:
USE northwind
GO
SELECT * FROM categories
Nu ska vi istället för att ta ut data från tabellen ”categories”, ta ut data från en härledd tabell:
USE northwind
GO
SELECT * FROM (SELECT * FROM categories) dt_categories
Det är allt som gäller när man hanterar härledda tabeller. Kom ihåg att härledda tabeller endast är resultatet av att använda en SELECT-sats i WHERE klausulen av en annan SELECT-sats. Sätt bara den extra koden i parentes och lägg till ett tabellnamn efter parentesen.
Båda av de ovanstående SQL-satserna utför samma sak, jag ville bara visa på vad en härledd tabell var för något och hur lätta dem är att skapa. I verkligheten om du vill utföra något så enkelt som ovanstående så bör du självklart använda den enklare av satserna. Men om din SQL-sats är väldigt komplicerad så bör du hellre använda härledda tabeller än temporära tabeller, vilket du kommer att märka i följande – mer realistiska – exempel.
Ett exempel: Återskapa en lagrad procedur med temporära tabeller
Till det här exemplet kommer vi att använda Northwind databasen. Problemet vi vill lösa består i att vi behöver en lista på kategorier och produkter från Northwind databasen, med en extra kolumn som talar om hur många produkter i varje kategori det finns. Exemplet är baserat på en verklig händelse, då jag en gång själv behövde få reda på hur många produkter det fanns inom en kategori. För er som är nyfikna kan jag berätta att anledningen till att jag ville veta det, var så att jag kunde skapa en JavaScript som dynamiskt fyllde en andra listbox.Det önskade resultatet ska se ut ungefär som följande:
Kategorinamn | Produktnamn | Antalet produkter/kategori
Beverages | Outback Lager | 2
Beverages | Chang | 2
Condiments | Aniseed Syrup | 3
Condiments | Cajun Seasoning | 3
Condiments | Gumbo Mix | 3
Resultatet visar på att det finns “2” produkter inom kategorin “Beverages”.
Det första exemplet som ska utföra ovanstående uppgift är väldigt långsam och använder sig utav en temporär tabell.
SELECT GETDATE()
GO
-- Skapa våran temporära tabell
CREATE TABLE #Temp_Example (
[CategoryID] INT NOT NULL,
[Category_Count] INT NOT NULL
)
-- Lägg in dem värden som vi kan behöva senare i den temporära tabellen
INSERT INTO #Temp_Example (CategoryID, Category_Count)
SELECT C.CategoryID, COUNT(*) AS Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryID, C.CATEGORYNAME
-- Skapa en relation på den temporära tabellen för att få ut våra värden
SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice,
#Temp_Example.Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
INNER JOIN #Temp_Example ON C.CategoryID = #Temp_Example.CategoryID
ORDER BY C.CategoryName
-- Släpp den temporära tabellen
DROP TABLE #Temp_Example
GO
SELECT GETDATE()
Låt oss nu titta på en annan SQL-sats som utför samma sak, men som använder en härledda tabell istället för en temporär tabell.
SELECT GETDATE()
GO
-- Lägg märke till hur lätt vi kopplar tabellen som skapades i minnet med CATEGORY ID
SELECT C.CategoryID, C.CategoryName, P.ProductName, P.UnitPrice, CT.Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
INNER JOIN (
SELECT C.CategoryID, COUNT(*) AS Category_Count
FROM Categories C
INNER JOIN Products P ON C.CategoryID = P.CategoryID
GROUP BY C.CategoryID, C.CategoryName
)
CT ON C.CategoryID = CT.CategoryID
ORDER BY C.CategoryName
GO
SELECT GETDATE()
Den första SQL-satsens prestanda kan öka dramatiskt om man bara lägger ner sig lite genom att använda en härledda tabell, som visas i den andra SQL-satsen.
Här följer de åtgärder vi använde för att höja satsens prestanda:
1. Vi tog SELECT-satsen från SELECT INTO-satsen och placerade den koden inom parenteser, följt av ett tabellnamn.
2. Vi kopplade tabellen mot minnet istället för mot en temporär tabell
3. Vi ändrade namnet på kolumnen som vi valde i vår SELECT-sats till namnet på kolumnen som vi hade i vår avskalade sats.
Som du ser så kan du enkelt lösa många komplicerade problem genom att använda härledda tabeller.
0 Kommentarer