MS Sql 70-461: Chapter 16
Les 1
The meaning of "set-based"
De term set-based wordt gebruikt om een benadering te beschrijven om query-taken af te handelen en is gebaseerd op de principes van het relationele model. Ter herinnering, het relationele model is gebaseerd op de mathematische set theorie. Set-based oplossingen gebruiken T-sql queries, welke opereren op tabellen als input, ofwel een set van rijen. Zulke oplossingen staan in contrast met iteratieve oplossingen die cursors gebruiken of andere iteratieve constructies om één rij per keer af te kunnen handelen.
Het is aan te raden om set-based constructies te gebruiken i.p.v. iteratieve constructies. Dit omdat de set-theorie de basis is van het relationele model, wat op zijn beurt weer de basis is van T-sql. Met iteratieve oplissingen wordt tegen dit principe in gegaan.
Iteratieve constructies zijn altijd veel langer omdat zij zowel de "hoe" als de "wat" (imperatief) moeten implementeren, terwijl bij set-based enkel de "wat" (declaratief) wordt opgegeven. Een andere manier waarom set-based aangeraden wordt is performance. Iteratieve oplossingen zijn erg traag. Loops in T-Sql zijn vele malen trager dan een loop in .NET. Daarnaast heeft elk record dat wordt opgehaald door het FETCH NEXT command erg veel overhead.
Pas als de query niet meer verder kan worden geoptimaliseerd door de Query Optimizer en de query zelf kan niet optimaler geschreven worden, dan pas kan er gekeken worden naar een iteratieve oplossing.
Iterations for operations that must be done per row
Cursor voorbeeld:
-- Variabele om de rij in op te slaan
DECLARE @curcustid AS INT;
-- FAST_FORWARD: een readonly- voorwaartse cursor
DECLARE cust_cursor CURSOR FAST_FORWARD FOR SELECT custid FROM Sales.Customers;
-- Open de cursor
OPEN cust_cursor;
-- Haal de customer id uit de cursor en zet deze in de variabele
FETCH NEXT FROM cust_cursor INTO @curcustid;
-- @@FETCH_STATUS: 0: de vorige fetch was succesvol, -1: de rij is verder dan de result set, -2 de ge-fetch-te rij ontbreekt.
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC Sales.ProcessCustomer @custid = @curcustid;
FETCH NEXT FROM cust_cursor INTO @curcustid;
END;
-- Sluit cursor
CLOSE cust_cursor;
-- Geef geheugen vrij
DEALLOCATE cust_cursor;
GO
Een andere oplossing die hetzelfde doet is de volgende query:
DECLARE @curcustid AS INT;
SET @curcustid = (SELECT TOP (1) custid
FROM Sales.Customers
ORDER BY custid);
WHILE @curcustid IS NOT NULL
BEGIN
EXEC Sales.ProcessCustomer @custid = @curcustid;
SET @curcustid = (SELECT TOP (1) custid
FROM Sales.Customers
WHERE custid > @curcustid
ORDER BY custid);
END;
GO
De laatste manier is geen set-based operatie omdat nog steeds rij voor rij behandeld wordt. Bovendien set-based oplossingen zijn niet afhankelijk van een volgorde, bovenstaande query wel.
De laatste query zal zonder een index op custId aanzienlijk trager zijn dan de CURSOR variant. Dit omdat bij elke iteratie de volledige tabel moet worden gescand.
Les 2
Met temporary tables en table variables kan data tijdelijk worden opgeslagen.
Scope
Sql Server ondersteunt twee soorten tijdelijke tabellen: local en global. Local temporary tables hebben een '#' voor hun naam staan, bijv: #T1. Deze zijn enkel zichtbaar in de sessie waarin ze zijn aangemaakt. Local temporary tables zijn zichtbaar binnen de scope waarin ze zijn aangemaakt. Child scopes hebben ook toegang tot de temp table. Als de temp table niet wordt gedropped, wordt hij automatisch verwijderd als de scope tot het einde komt.
Global temporary tables hebben twee keer een # voor hun naam staan: ##T1. Deze tables zijn zichtbaar in alle sessies. Ze worden verwijderd als de sessie die hem heeft aangemaakt afgelopen is en er geen referenties meer naar zijn.
Table variables worde gedeclareerd in plaats van aangemaakt en hebben een @ voor hun naam staan, bijv: @T1. Ze zijn enkel zichtbaar door de batch die hem heeft aangemaakt en wordt automatisch verwijderd als de batch is afgelopen.
Onderstaande query toont aan dat een local temp table zichtbaar is in meerdere batches:
CREATE TABLE #T1
(
col1 INT NOT NULL
);
INSERT INTO #T1(col1) VALUES(10);
EXEC('SELECT col1 FROM #T1;');
GO
SELECT col1 FROM #T1;
GO
DROP TABLE #T1;
GO
De volgende query zal een fout geven omdat @T1 enkel zichtbaar is binnen de batch waarin die is aangemaakt:
DECLARE @T1 AS TABLE
(
col1 INT NOT NULL
);
INSERT INTO @T1(col1) VALUES(10);
EXEC('SELECT col1 FROM @T1;');
GO
DDL en indexes
Tijdelijke tabellen worden aangemaakt in de tempdb database in het dbo schema. Het is mogelijk om temp tabellen aan te maken met dezelfde naam omdat Sql Server intern een unieke suffix toevoegd aan de naam van de tabel. Echter, als in twee sessies dezelfde naam wordt gebruikt voor een constraint, dan zal de tweede keer falen. Dit omdat namen van constraints uniek moeten zijn binnen een schema. Als geen naam wordt opgegeven bij het maken van een constraint, dan genereert Sql Server zelf een unieke naam. Om deze reden is het beter om in temp tables geen namen op te geven voor constraints.
Bij table variables is het niet mogelijk om een constraint een naam te geven. Een tabel in een table variabele kan niet meer worden aangepast (ALTER) met een constraint, bij een temp table kan dit wel.
Physical representation in tempdb
Zowel temporary tables als tabel variabelen hebben een fysieke representatie in tempdb.
In de sys.objects view staan entries van interne tabellen ie Sql Server aanmaakt in tempdb om tijdelijke tabellen en tabel variabelen te implementeren.
Een common table epression (CTE) wordt niet opgeslagen zoals een tijdelijke tabel of een tabel variabele. Als Sql Server een query optimaliseert met een CTE, dan wordt het geneste query logica weggehaald om vervolgens direct met de onderliggende tabellen te kunnen query'en.
Transactions
Temp tables en tabel variabelen gaan anders om met transacties. Temp tables gaan hetzelfde om met transacties als normale tabellen. Bij een temp table worden de veranderingen ongedaan gemaakt bij een rollback. Bij een tabelvariabele niet.
Statistics
Sql Server houdt enkel distributie statistieken bij voor temporary tables en niet voor tabelvariabelen. Algemeen betekent dit dat de plannen van een temp tabel meer geoptimaliseerd zullen zijn dan plannen van een tabelvariabele.
Het wordt aangeraden om tabelvariabelen te gebruiken als er weinig data in de tabel zit of als het execution plan zo triviaal is dat de optimizer geen histogram nodig heeft om tot deze conclusie te komen. Een voorbeeld hiervan is een range scan in een clustered index of een covering index. Zo'n plan is niet afhankelijk van de selectiviteit van het filter. Het is simpelweg altijd een betere optie dan een full scan.
Reacties
Een reactie posten