Undvik flaskhalsar med temporära tabeller
Förord
Vet du om att genom att fylla en SQL-servers temporära tabell så kan du få stora prestanda problem med sk flaskhalsar på din server? Problemen kan uppstå både på SQL server 6.5 och 7.0 på olika sätt, i den här artikeln ska jag visa hur man bäst undviker dem.
Flaskhalsar i SQL Server 6.5
Många använder en SELECT ... INTO fråga för att skapa en temporär tabell, liknande det här:
SELECT *
INTO #TempTable
FROM SourceTable
När det här körs så skapar det låsningar mot den temporära tabellen under SELECT frågans körning (kan ta en tid om du loopar genom mycket data i källtabellen och ännu längre om SELECT....INTO är starten på en längre explicit körning). När låsningen väl är så kan ingen annan användare skapa en temporär tabell. Den riktigt placeringen av flaskhalsen är en låsning på tempdb system tabell. I senare versioner av SQL server är den här låsningsmodellen ändrad och problemet undviks därmed.
Om ett antal konkurrerande processer försöker att ladda en temporär tabell på det här sättet, speciellt stora om kvantiteter data är med, då är en flaskhals ofrånkomlig. Tricket är att frigöra tempdb för att säkerställa att "skapa temporär tabell" delen av operationen är skapad så fort som möjligt. För att göra detta, koda om ovanstående fråga till det här:
CREATE TABLE #temp
(
......
)
INSERT #temp
SELECT *
FROM SourceTable
På det här sättet skapar vi vår temporära tabell och frigör sysobjekt eller schema låsningar så fort det bara går, reducerar påtagligt låsning och höjer prestandan.
Genväg till en lösning
Om du vill undvika att koda INSERT...INTO frågan, eller om du skriver en generell kod och om du inte kommer att veta den exakta tabell definitionen före körning, då kan du återgå till detta trick.
SELECT *
INTO #temp
FROM SourceTable
WHERE 1 = 0
INSERT #temp
SELECT *
FROM SourceTable
Självklart kommer WHERE 1=0 aldrig att vara sann. Oavsett hur mycket data som finns i källtabellen så är SQL serverns optimerare smart nog att inse att eftersom ett aldrig är lika med noll så är det ingen ide att söka igenom källtabellen.(Om jag kan så kollar jag alltid frågekörnings delen just för att vara säker, men än har det inte hänt.) Även om SQL servern inte kommer att gå igenom källtabellen, #temp tabellen kommer fortfarande att skapas, baserad på formatet på datat i SELECT frågan, men den kommer inte att innehålla några rader. Du kan sen köra INSERT...SELECT frågan säker på att du inte blockerar andra processers försök att accessa tempdb.
Flaskhalsar i SQL Server 6.5 och 7.0
För det mesta, problemen beskrivna ovanför stämmer inte på SQL server 7.0, men det finns fortfarande en instans där du oavsiktligt kan skapa de här flaskhalsarna under varje version av SQL server.Problemet uppstår när du använder en INSERT...EXEC fråga för att fylla en temporär tabell, eftesom den lagrade proceduren självt skapar temporära tabeller, och du fastnar med en låsning på tempdb liknande det som beskrevs ovanför. Den föreslagna lösningen är antingen "gör det inte i första taget", vilket är obekvämt om du inte vill hålla på med arv frågor eller kod som du inte kontrollerar eller annars köra en lagrad procedur som en remote lagrad procedur (t ex."INSERT #temp EXEC server.database.owner.proc") vilket igen inte är är ett ideal under alla omständigheter.
0 Kommentarer