BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

Transact SQL ska användas för att skriva queries. Transact SQL ska INTE användas för att skriva loop-konstruktioner. Tyvärr är det så att det finns massor med verksam kod där Transact SQL används för att skriva loopar eller andra konstruktioner som skulle kunna lösas med mängdfunktioner.

Ett ganska vanligt scenario är kalender-problem. En tabell innehåller transaktioner och en datumkolumn. Som exempel använder jag en evenemangskalender:

CREATE TABLE Evenemang( ID int IDENTITY(1,1) PRIMARY KEY,
Beskrivning nvarchar(MAX), TidOchDatum datetime)

Någon vill skapa en rapport, där alla dagar i februari finns med som rubriker, och om det finns evenemang ska info om dessa också finnas med.

Många skulle välja att lösa det i applikationskoden, i ASP.NET eller annat språk. Det skulle bli ineffektivt och troligen skulle en query för varje dag köras.

Jag skulle lösa det ungefär såhär:

SELECT TOP 11000 IDENTITY(int,1,1) as N INTO dbo.Nummer

FROM sys.all_columns c1
CROSS JOIN sys.all_columns c2

ALTER TABLE dbo.Nummer
ADD CONSTRAINT PK_Nummer
PRIMARY KEY(N)

GO
CREATE TABLE Evenemang( ID int IDENTITY(1,1) PRIMARY KEY,
Beskrivning nvarchar(MAX), TidOchDatum datetime)
GO
INSERT INTO Evenemang(Beskrivning,TidOchDatum)
SELECT 'Här är lite evenemangsinfo','20110205 00:00:30.000'

SELECT e.Beskrivning,
CONVERT(varchar(10),DATEADD(day,N-1,'20110201 00:00:00.000'),120) 
FROM dbo.Nummer n
LEFT JOIN dbo.Evenemang e
ON 
CONVERT(varchar(10),DATEADD(day,N-1,'2011-02-01 00:00:00.000'),120) = 
CONVERT(varchar(10),TidOchDatum,120)
WHERE DATEDIFF(month,'20110201 00:00:00.000',DATEADD(day,N-1,'20110201 00:00:00.000'))=0
order by CONVERT(varchar(10),DATEADD(day,N-1,'2011-02-01 00:00:00.000'),120) 

Med en nummertabell alltså, mot vilken jag joinar evenemangstabellen. Jag använder en rad tal i ordningsföljd för att - för varje rad - skapa ett datum-fält. Jag joinar mot Evenemangstabellen. Med en LEFT JOIN löser jag detta att alla dagar inte har evenemang, men jag vill returnera alla dagar.

Prestandan med att använda en Nummertabell för att sådant scenario som ovan kan omöjligt slås med loopar, varken i T-SQL-koden eller i .NET.

Att Nummer-tabellen skapades med just 11.000 rader beror på att ungefär så många dagar har förflutit sedan 1900-01-01, som är SQL Servers första giltiga datum för datum-datatyperna det motsvarar ungefär 30 års datum.

Det här var det första inlägget om Nummer-tabellen. Den kan användas till en massa saker och jag ser alltid till att skapa en Nummer-tabell det första jag gör när jag installerat en ny SQL Server-instans. Stort tack till Jeff Moden, som redan 2008 i ett pedagogiskt inlägg på SQL Server Central skrev om Nummertabellen (Tally table som han kallar den). Det var inte Jeff Moden som "uppfann" konceptet, men han har beskrivit det mycket pedagogiskt. Jag ska inte göra det så enkelt för mig som att jag kopierar hans exempel och svenskifierar dem. Istället kommer jag att ge andra exempel på Nummertabellens användningsområden.

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

I mitt förra blogginlägg skrev jag bland annat om spDeleteBackupRestoreHistory, som är en Stored Procedure som rensar backup- och restore-historik i MSDB. Den gick VERKLIGEN långsamt i sitt ursprungsutförande. Därför började jag kolla indexeringen i MSDB och upptäckte att den är minst sagt sparsam. Det kan säkert göras massor för att snabba upp både det ena och det andra i MSDB. Men jag hade två specifika problem:

1) Tabellen backupset har ett enda index – backupsetuuid. Men det finns en Foreign Key-constraint som refererar kolumnen media_set_id till tabellen backupmediaset. I SPn spDeleteBackupRestoreHistory görs en sökning i just den kolumnen, vilket innebär en table-scan. Eftersom den sökningen görs väldigt många gånger innebär det väldigt många tablescans och därmed väldigt dålig prestanda.

2) I spDeleteBackupRestoreHistory läses kolumnerna backup_set_id och media_set_id i tabellen backupset. Det finns ett index (primärnyckel, klustrad) på backup_set_id, men inget index på media_set_id.

För att slå två flugor i en smäll skapade jag ett sammansatt index över kolumnerna media_set_id och backup_set_id. Då fick jag dels sökbarhet på media_set_id vilket gör att table_scan undviks, dels fick jag ett täckande index över de två kolumner i tabellen backupset som spDeleteBackupRestoreHistory använder.

Alltså:

CREATE 
  INDEX [backupset_mediaset_id]
ON
  [dbo].[backupset]([media_set_id],
  [backup_set_id])

Jag testkörde genom att:

• Först göra exec spDeleteBackupRestoreHistory 100 utan att mäta tiden.
• Sedan gjorde jag exec spDeleteBackupRestoreHistory 90 med tidsmätning innan jag skapat mitt nya index. Resultatet: 12 minuters körning.
• Sedan skapade jag indexet och gjorde exec spDeleteBackupRestoreHistory 90. Resultat: 5 sekunders körning.

Sensmoral: Ha alltid index på kolumner som refereras i främmande nycklar. Försök att skapa täckande index.

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

Nyligen stötte jag på en fråga som lyder:

Hur skriver jag en stored procedure för att göra en insert bara om det inte redan finns en rad med de värden jag försöker göra insert med.

Mitt svar är:

Använd IF NOT EXISTS(SELECT * FROM tabellen where kolumn1 = @kolumn1 AND kolumn2 = @kolumn2 [..] AND kolumnN = @kolumnN

Den som ställde frågan stötte på syntaktiska problem, och återkom med beskedet:

Jag har löst det. Jag skapade ett icke-klustrat unikt index med alternativet WITH IGNORE_DUP_KEY

Såhär alltså:

CREATE UNIQUE NONCLUSTERED INDEX UQI_tabellen_all ON tabellen(kolumn1, kolumn2, [..], kolumnN) WITH IGNORE_DUP_KEY

Fiffigt. Då behöver man inte programmera sin kontroll. Man kan bara göra en insert, och om en identisk rad redan finns så händer ingenting. Eller nästan ingenting iallafall. Sanningen är att SQL Server kastar ifrån sig en varning: "Duplicate key was ignored". Men det är ingenting jag i de flesta fall märker i min ADO.NET-kod. Där tror jag att allt gått som det ska, och kör vidare till nästa rad.

Frågan är om det verkligen är vad jag vill. Eller rättare sagt. Det här är INTE vad jag vill. Jag vill verkligen inte att SQL Server ska låtsas som att den har gjort en INSERT när den inte har gjort det. Försöker jag göra insert i en tabell där det finns en unique-constraint så vill jag veta att jag bryter mot databas-regler. Jag vill ha ett stort fett ERROR kastat när jag försöker bryta mot en unique-constraint.

Ett STORT problem med lösningen är också att den som frågade hade gjort ett ICKEKLUSTRAT index istället för ett KLUSTRAT. Det innebär att allt data dubbellagras, dels i tabellen själv, dels i indexträd för tabellen. Ska man använda sig av heltäckande index så ska de vara klustrade!

Jag vill alltså göra kontrollen med EXISTS-funktionen som jag beskriver ovan. Till min STORA glädje upptäcker jag också att det är mycket effektivare att göra så. Exakt varför vet jag inte, men troligen är det så att det snor en del resurser från SQL Server att konstatera att ett brott mot en constraint håller på att begås, och därför ge ifrån sig en varning och ignorera den fysiska inserten, istället för att "bara" göra en EXISTS-kontroll (som ju SQL Server ändå måste göra för att upprätthålla UNIQUE-constrainten).

Jag testade genom att först skapa en tabell:

create table t (id int identity(1,1) primary key nonclustered, c1 varchar(10), c2 varchar(10), c3 varchar(10))

Sedan skapade jag ett klustrat unique-index med WITH IGNORE_DUP_KEY:

create unique clustered index uqi_t_all ON t(c1,c2,c3) WITH IGNORE_DUP_KEY
go

Slutligen ett script som loopar 100000 gånger för att göra en INSERT:

declare @c1 varchar(10), @c2 varchar(10), @c3 varchar(10)
set @c1='1'
set @c2='2'
set @c3='3'
declare @i int
set @i=0
while @i<100000
begin
insert into t(c1,c2,c3) values(@c1,@c2,@c3)
set @i=@i+1
end

Scriptet körs på 12 sekunder

Jag testar igen, denna gång med ett unique index UTAN WITH IGNORE_DUP_KEY

drop index t.uqi_t_all
go
create unique clustered index uqi_t_all ON t(c1,c2,c3)
go

Jag kör sedan mitt insert-script igen, den här gången med en EXISTS-kontroll:

declare @c1 varchar(10), @c2 varchar(10), @c3 varchar(10)
set @c1='1'
set @c2='2'
set @c3='3'
declare @i int
set @i=0
while @i<100000
begin
IF NOT EXISTS(SELECT * FROM t where c1=@c1 AND c2=@c2 AND c3=@c3)
insert into t(c1,c2,c3) values(@c1,@c2,@c3)
set @i=@i+1
end

Scriptet körs på 1-2 sekunder

6-12 gånger snabbare alltså att göra kontroll i T-SQL istället för att låta SQL Server ignorera en insert genom IGNORE_DUP_KEY. Dessutom betydligt tydligare - alla kan se i SQL-koden att ingen insert kommer att göras om det skulle innebära skapande av dubletter. Med IGNORE_DUP_KEY är det betydligt mer otydligt.

Jag är nästan lite småsur över att IGNORE_DUP_KEY överhuvudtaget finns som ett alternativ för unique index. Varför gör man så? Det skapar risk för missförstånd och förvirring, och det är långsammare än att göra kontrollen i Transact-SQL.

För mer information om klustrade och icke-klustrade index: Vaddå klusterindex?

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

Via en bloggpost från Adam Machanic har jag deltagit i en tävling om ett exemplar av Expert SQL Server 2005 Development. Frågan från Adam är:

Givet att du har två tabeller, som skapats med scriptet nedan:

USE TempDB
GO

CREATE TABLE b1 (blat1 CHAR(5) NOT NULL)
CREATE TABLE b2 (blat2 VARCHAR(200) NOT NULL)
GO

INSERT b1
SELECT LEFT(AddressLine1, 5) AS blat1
FROM AdventureWorks.Person.Address

INSERT b2
SELECT AddressLine1 AS blat2
FROM AdventureWorks.Person.Address
GO

Hur kan man skriva om nedanstående fråga så att den blir snabbare (den tar ungefär sex minuter på min PC):

SELECT *
FROM b1
JOIN b2 ON
    b2.blat2 LIKE b1.blat1 + '%'

Inga objekt-ändringar är tillåtna, dvs ingen indexering av tabellerna får göras.

Lösningen är att göra exakta jämförelser istället för ungefärliga. Dvs att använda "="-operatorn istället för "LIKE"-operatorn. Varför? Jo, därför att "LIKE"-operatorn gör att vi måste använda oss av en "NESTED LOOP" för att jämföra raderna. Dvs SQL Servers Query Processor (QP) måste titta på varje rad i den yttre tabellen i JOIN-operationen, och för varje rad i den yttre tabellen måste QP titta på varje rad i den inre tabellen för att jämföra data. Både b1 och b2 innehåller 19614 rader. Det är inga extrema datamängder, långt därifrån. För att köra ovanstående fråga måste QP titta på 19614 i b1, och för varje av dessa rader måste 19614 rader i b2 undersökas. Dvs 384 708 996 rader måste undersökas.

Lyckas vi istället använda en "="-operator kan QP använda en "MERGE" eller "HASH MATCH", vilket väsentligen reducerar antalet rader att titta på.

Min omskrivna fråga blir:

SELECT *
FROM b1
JOIN b2 ON
LEFT(b2.blat2,5) = b1.blat1

Det funkar därför att vi vet att b1.blat2 innehåller de första fem tecknen från b2.blat2. Tack vare den vetskapen kan frågan skrivas om, och köras på två sekunder. QP utför en "HASH MATCH" och antalet rader som måste undersökas för att utföra JOIN-operationen reduceras väsentligt.

Nedan ser du bilder av de estimerade planerna för de båda frågorna, och kan själv se skillnaden i estimerat antal rader för "NESTED LOOP" jämfört med "HASH MATCH" (Jämför "Estimated Number of Rows")

 
SELECT * FROM b1 JOIN b2 ON b2.blat2 like b1.blat1 +'%'

SELECT * FROM b1 JOIN b2 ON left(b2.blat2,5) = b1.blat1

BLOGGEN HAR FLYTTAT TILL http://sql.underlandet.com

Fler än en gång har jag försökt att grundläggande förklara lite grann om index i SQL Server (och egentligen alla andra databaser, det funkar ju faktiskt rätt lika). Det som flest har svårt att förstå är det här med klustrade och icke-klustrade index. Många undrar vad som är skillnaden, och många undrar vilket index som är bästa kandidaten att använda som klustrat index. Här tänkte jag försöka sammanfatta vad jag brukar svara.

Vad är det?
Ett klustrat index är ett index som lagras tillsammans med datat för en tabell. Det gör att datat självt är sorterat, vilket kan vara användbart emellanåt.

Den bästa jämförelsen jag känner till är en telefonkatalog. I en telefonkatalog finns sidor, som vi kan jämföra med data-pages i SQL Server. Varje telefonnummer kan jämföras med en rad i en tabell i SQL Server. Telefonnumren är sorterade efter Efternamn, Förnamn. På samma sätt funkar ett klustrat index. Datat på varje data-page är sorterat efter det klustrade indexet som finns på tabellen. Tabellens data-pages är sorterade inbördes enligt samma princip.

I SQL Server finns dessutom ett index-träd utanför tabellen som gör att det blir lite lättare att hitta till den första raden som uppfyller ett sökvillkor i det klustrade indexet. Det kan jämföras med att högerkanten på sidorna i en telefonkatalog ofta innehåller ett första-bokstaven-register, som ger genvägar till alla efternamn som börjar på en viss bokstav.

Har du en tabell med telefonnummer, Efternamn och Förnamn, där Efternamn, Förnamn är ett klustrat index, och du vill söka ut alla telefonnummer tillhörande någon som heter Andersson, så lägger du in "WHERE efternamn = 'Andersson'" som WHERE-klausul i din fråga. SQL Server börjar då leta i sitt index-träd efter 'Andersson', och när den hittar 'Andersson' så går den via en adress-pekare direkt till den data-page där den första förekomsten av 'Andersson' finns. Eftersom datat är sorterat efter Efternamn, Förnamn finns nu ingen anledning att gå tillbaka till något ytterligare index för att hitta alla rader för med Efternamn='Andersson', utan SQL Server kan börja läsa data-page efter data-page, tills den stöter på ett efternamn som INTE är 'Andersson'.

Icke-klustrat då?
Icke-klustrade index lagras helt och hållet utanför själva datat för en tabell. De lagras också i trädstrukturer, men innehåller till skillnad från klustrade index ett index-löv för varje rad i tabellen, och en pekare till adressen där raden lagras. Vill du läsa alla som heter 'Andersson' enigt exemplet ovan får SQL Server jobba lite mer för att hitta allt data. Den måste göra följande fler-stegs-raket:

1. Leta upp första lövet/nästa löv med efternamn='Andersson' i indexträdet.
2. Följ pekaren till den data-pagen där raden som index-lövet motsvarar lagras.
3. Stoppa in raden i en temporär "Spool-tabell"
4. Upprepa steg 1-3 tills alla 'Andersson' är funna
5. Läs "Spool-tabellen" och returnera.

Alltså något mer arbete än att bara leta reda på den första raden och sen läsa och returnera rad för rad.

Vad ska jag välja som klustrat index?
När man börjar diskutera kandidater för klustrade index ändras ofta diskussionen till att bli av religiös karaktär.

SQL Server skapar alltid ett klustrat index, oavsett om vi vill det eller inte. Om vi inte själva bestämmer vilket index som ska vara klustrat skapas ett "osynligt" klustrat index som vi inte har varken nytta av eller kontroll över. Därför är det hyfsat självklart att vi ska välja ett klustrat index. Som standard skapar SQL Server ett klustrat index över kolumnen eller kolumnerna som ingår i primärnyckeln, om det inte redan finns ett klustrat index när primärnyckeln skapas.

Vissa hävdar att det här alltid är helt rätt och att man aldrig ska använda sig av annat än primärnyckeln som klustrat index. Det är snick-snack i mina öron. Tänk dig återigen fallet med telefonkatalogen. Man kan tänka sig att telefonnummer är primärnyckel i en telefonkatalog, eller hur? Varför skulle vi vilja ha telefonnummer som klustrat index i telefonkatalogen? Din Del skulle förmodligen ha betydligt färre nöjda mottagare av katalogen om den var sorterad efter telefonnummer..

Vissa hävdar att man ska ha HÖG selektivitet på det index som är klustrat. Dvs att man får träff på väldigt få rader om man söker i indexet efter ett visst värde. Varför det skulle vara en fördel förstår jag ärligt talat inte. Har man hög selektivitet på ett index, till exempel en primärnyckel, så kostar det ju väldigt lite att först söka reda på index-lövet och sen besöka en eller ett fåtal rader via pekare. Har man däremot LÅG selektivitet (exempelvis efternamn i en telefonkatalog) så kostar det ganska mycket att gå via ett icke-klustrat index.

Jag tycker att främmande nycklar ofta utgör bra kandidater för klustrade index. Tänker dig en order-applikation, med en Order-tabell och en Order-rad-tabell. Där vill man väldigt ofta selektera ut alla orderrader som tillhör ett visst ordernummer. Då är Ordernummer-kolumnen i orderrad-tabellen en perfekt kandidat för klustrat index. Nu skulle jag i och för sig designa min tabell så att primärnyckeln bestod av Order-nummer och Orderrad-nummer. Men många SQL Server-utvecklare använder sig ALDRIG av naturliga nycklar, utan skapar istället en IDENTITY-kolumn som primärnyckel. Den kolumnen är precis värdelös att använda som klustrat index, ur sökbarhetssynpunkt.

Sammanfattningsvis
Tänk dig för innan du bara accepterar SQL Servers förslag att skapa klustrat index av primärnyckeln. Använder du dig av normaliserade tabeller, med bara naturliga nycklar är det förmodligen bra kandidater för klustrade index. Men om du tillhör de 99% SQL Server-utvecklare som istället skapar en surrogat-nyckel och en UNIQUE-constraint (VARFÖR GÖR FÖRRESTEN SÅ MÅNGA DET, DET ÄR VERKLIGEN DUBBELARBETE!!!) över det som kunde varit naturlig nyckel - ja då ska du fundera både en och två gånger på vilket klustrat index du bör använda. Tänk telefonkatalog!