Den gode stored procedure
Følgende beskriver retningslinjer for udvikling af Stored Procedures,
Rretningslinjerne skal sikre ensartethed og god performance.
Forretningslogik
Stored Procedures kan bruges til mange ting, både store batch job lignende konstruktioner, der kun kører få gange om dagen, og små konstruktioner der kaldes tusindvis af gange, f.eks. via webservice kald
Stored procedures der kører tusindvis af gange, skal selvsagt holdes simple uden for mange logiske udtryk som if-then-else osv. og den enkelte stored procedure bør kunne eksekveres på (mili)sekunder og ikke minutter.
Start altid med
SET NOCOUNT ON.
Herved undertrykkes beskeden om hvor mange rækker der blev udtrukket/slettet/indsat, hvorved spares lidt netværkstrafik og aftagere af resultatet, undgår mulig forvirring over hvad der returneres fra stored procedure
Ved brug af følgende udtryk, skal succes af operationen testes, enten med @@Error eller en TRY...CATCH blok:
INSERT, DELETE, UPDATE
SELECT INTO
Kald af stored procedures
Afvikling af dynamisk SQL
COMMIT TRANSACTION
DECLARE ogd OPEN CURSOR
FETCH from cursor
WRITETEXT og UPDATETEXT
Vælg fornuftig navngivning af variable.
Resten er standard SQL Best Practice
Input parametre
Input parametres datatyper skal svare til de datatyper der anvendes i de tabeller der benyttes af den stored procedure som filtre, joins etc.
Hvis datatyper ikke svarer til de underliggende, konverteres disse implicit af SQL serveren, med forøget memory-, cpu- og tidsforbrug til følge.
(se nærmere under 'SQL Best practices').
Altid default værdier for input parametre.
Output parametre
Output bør holdes simpelt, som enten lister af værdier eller enkeltværdier, flere resultatsæt bør som hovedregel undgåes.
Returværdi
Benyt altid returværdien til at signalere succes eller ej
Retur værdien skal være 0 ved succes og større end 0 ved fejl.
Variabler
Som for parametre gælder at datatyper skal svare til den forventede datatype ved anvendelsen.
(se nærmere under 'SQL Best practices')
Fejlhåndtering
Brug TRY...CATCH blokke
Brug sysmessage, sp_addmessage istedet for hardkodede fejlbeskeder
Med sp_addmessage og sysmessages, benyt altid fejlbesked nummer fra 50001 og opefter.
Når RAISERROR benyttes, sæt 'severity level' <= 10 ved warning.
Når RAISERROR benyttes, sæt 'severity level' mellem 11 og 16 ved fejl.
Idempotente scripts til brug i DBInstaller ved deploy
For at sikre ensartede og idempotente scripts, er udviklet en stribe templatescripts
anvendelsen er beskrevet her DBInstaller SSMS Templates - STAR City - Internt område - Confluence (atlassian.net)
SQL Best practice
Best practice, enten på grund af performance, læsbarhed, kompatibilitet ...
Ikke i prioriteret rækkefølge.
Der kan altid findes eksempler hvor det giver mening, ikke at følge best practice, men generelt vil det være en fordel at følge disse.
At benytte Query Hint 'WITH (NOLOCK)', er næsten aldrig en god løsning
Det er en udbredt misforståelse at benyttelsen af query hint 'WITH (NOLOCK)' betyder at tabellen ikke låses mens der læses.
SELECT EfterNavn FROM Person.Person WITH (NOLOCK)
I virkeligheden betyder 'WITH (NOLOCK)' det samme som READ UNCOMMITED
(og der sættes faktisk en ekstra lås på data når 'WITH (NOLOCK)' benyttes, der kan forårsage deadlocks, se senere)
Det vil sige i ovenstående eksempel, læses alle rækker fra tabellen Person, uanset om det der står i rækken er commitet eller ej
Det betyder at man kan risikere følgende i resultatsættet
- Samme række flere gange
Sker hvis rækken opdateres af en anden query og derved skifter 'plads' og kommer til at ligge længere fremme end din query er nået til i sin læsning - Mangle rækker
Sker hvis rækken opdateres af en anden query og derved skifter 'plads' og kommer til at ligge før det sted din query er nået til i sin læsning - Indeholde data der ikke er committet, og måske aldrig bliver det
Såkaldt dirty reads, der altså vil indeholde direkte fejlagtige oplysninger - Din Query kan resultere i deadlocks og vil næsten altid fejle
Årsagen kan f.eks.være index reorganisering.
Så 'taber' din query, lidt ligesom i et deadlock scenarie da den lås der sættes er en 'schema stability (Sch-S) lock', mens index reorganisering kræver en 'schema modification (Sch-M) lock'.
For temp tabeller gælder at her skal 'WITH (NOLOCK)' ikke anvendes. Det har absolut ingen effekt, hverken den ene eller den anden vej. Det vil blot forvirre i læsning af SQL
Der er selvfølgelig steder hvor det giver mening at benytte 'WITH (NOLOCK)'
- Hvis den tabel der spørges mod ALDRIG opdateres kan det give bedre performance, da SQL Server ikke behøver kontrollere om der er en lås der skal respekteres
- I en adhoc forespørgsel hvor man ikke er så nøjeregnende med nøjagtigheden af resultatsættet
- En tabel med brugere på et website hvor man gerne vil vide hvor mange der er online lige nu. Der er det ikke så vigtigt om det er 123004 eller 123097
Der findes masser af artikler om emnet, her er een af de mere humoristiske: NOLOCK Is Bad And You Probably Shouldn't Use It. - Brent Ozar Unlimited®
Undgå 'implicit conversion' ved at have korrekte datatyper i parametre og variabler
'Implicit conversion' ses i query plan, oftest markeret med en advarselstrekant.
'Implicit conversion' skyldes enten forskel i datatype mellem parameter/variable ('prædikatet') og datatypen for den kolonne der søges mod,
eller at en beregning udføres på tabelkolonnen i stedet for prædikatet.
I begge tilfælde kan SQL Server ikke lave en indekssøgning på kolonnen og vil istedet foretage en scanning.
Dette skyldes at SQL Serveren først skal konvertere hver eneste række i datasættet før den kan foretage den ønskede operation.
Eksempler:
/* Kolonnen efternavn i tabellen Person er defineret som NVARCHAR(30) Der findes et indeks på kolonnen Efternavn */ DECLARE @cEfterNavn CHAR(30) SELECT @cEfterNavn = 'Nielsen' /* Dette giver implicit conversion på grund af forskellen i datatype */ SELECT pers.EfterNavn FROM Person.Person pers WHERE pers.EfterNavn = @cEfterNavn /* det kan rettes på tre måder, men den sidste er IKKE best practice: Man kan enten ved at ændre datatypen for variablen */ DECLARE @cEfterNavn NVARCHAR(30) /* eller udføre konverteringen på variablen */ SELECT pers.EfterNavn FROM Person.Person pers WHERE pers.EfterNavn = CAST(@cEfterNavn AS NVARCHAR(30) ) /* i praksis kan man også komme af med advarslen om 'implicit conversion' på denne måde (så har vi ikke ændret datatypen for @cEfterNavn) Men det vil stadig gøre at SQL Server ikke kan benytte det korrekte indeks og man får det der kaldes 'NON-SARGABLE'- prædikat */ SELECT pers.EfterNavn FROM Person.Person pers WHERE CAST(pers.EfterNavn AS CHAR(30) ) = @cEfterNavn
SARGABLE prædikater
SARGABLE er en forkortelse af 'Search ARGument Able', der betyder at man kan benytte et indeks til sin operation (hvis det findes :-))
Det gælder altså om at opbygge sin WHERE betingelse så SQL Server kan benytte et index seek, istedet for scan
Vi bruger samme tabel som ovenfor
/*variablen sættes til at være korrekt type */ DECLARE @cEfterNavn NVARCHAR(30) SELECT @cEfterNavn = 'Niels' /* og vil gerne have alle der begynder med 'Niels' */ SELECT pers.EfterNavn FROM Person.Person pers WHERE LEFT(pers.EfterNavn,5) = @cEfternavn
Dette er også et 'NON-SARGABLE'- prædikat og løsningen er igen at lade operationen foregå på variablen og i dette tilfælde er det ok at lave en LIKE:
SELECT pers.EfterNavn FROM Person.Person pers WHERE pers.EfterNavn LIKE @cEfternavn+'%'
Helt generelt vil ethvert prædikat hvor en kolonne manipuleres med en funktion, blive NON-Sargable
Altid alias på tabeller
SELECT pers.EfterNavn FROM Person.Person pers
Altid 'fully qualified' objektnavne, altså både skema og objektnavn:
SELECT pers.EfterNavn FROM Person.Person pers
EXEC person.GetPersonData @CPR='xxxxxxxxxx'
Brug ANSI joins
SELECT borger.Navn, adr.vejnavn FROM borger borger INNER JOIN borger_adr adr ON borger.cpr = adr.cpr
og ikke
SELECT borger.Navn, adr.vejnavn FROM borger borger, borger_adr adr WHERE borger.cpr = adr.cpr
Brug aldrig RIGHT JOIN
Der er ikke noget forkert med RIGHT JOIN, men det er nemmere at læse hvis alle JOINs peger i samme retning
Brug SELECT til at angive variabelværdier
Man kan assigne værdier til mere end een variabel, og det er hurtigere end een SET pr variabel.
SELECT @Var1 = 45,
@Var2 = 56
istedet for
SET @Var1 = 45
SET @Var2 = 56
Brug CAST istedet for CONVERT hvis muligt
CAST er ANSI standard, mens CONVERT kun virker på SQLServer.
Kun ved DATETIME konverteringer er CONVERT bedste valg, det kan CAST ikke i nær samme omfang
Brug EXISTS istedet for IN
Prøv også at undgå IN.
Hvis eksistensen af værdier skal kontrolleres, benyttes EXISTS i stedet for IN.
IN tæller også NULL værdierne, men det gør EXISTS ikke.
EXISTS returnerer boolean Ja eller Nej), mens IN returnerer alle værdier, derfor er resultatsættet for IN større end EXISTS
Brug:
SELECT pers.EfterNavn FROM Person.Person pers WHERE EXISTS (SELECT 1 FROM person.Borgere cpr WHERE pers.cpr = cpr.cpr)
istedet for
SELECT pers.EfterNavn FROM Person.Person pers WHERE pers.cpr IN (SELECT cpr.cpr FROM person.Borgere cpr)
Undgå DISTINCT og ORDER BY, hvis de ikke behøves
Det er bare en masse ekstraarbejde for databasen, hvis det ikke behøves.
Medtag kun de kolonner du skal bruge i din SELECT
Vær opmærksom på læsbarheden,
Ved få kolonner er det ok at have det i een linie:
SELECT Col1, Col2, Col3, Col4, FROM TabelA
men når antallet af kolonner vokser er det bedre at lave een linie pr kolonne
SELECT Col1 , Col2 , Col3 , Col4 , Col5 , Col6 , Col7 , Col8 FROM TabelA
SELECT INTO istedet for INSERT ved insert til ny tabel (SELECT INTO virker ikke hvis tabellen allerede eksisterer)
SELECT INTO er en ikke logget operation, og derfor meget hurtigere end INSERT
SELECT Col1 , Col2 INTO TabelA FROM TabelB
istedet for
INSERT INTO TabelA (Col1, Col2) SELECT Col1 , Col2 FROM TabelB
Generelle formateringsregler
-KeyWords og datatyper med stort: SELECT, VARCHAR, DATETIME, INSERT, FROM et
-Indentering med tabs
-Kommentering i block comment:
/* kommentar */
Ikke kommentere det åbenlyse, men istedet hvad en klump sql gør, eller hvorfor en specifik lille detalje i koden er med:
SELECT o.name FROM sys.indexes i INNER JOIN sys.objects o ON i.[object_id] = o.[object_id] WHERE i.type_desc = 'HEAP' /* vi vil kun se tabeller uden clustered index */ AND o.[type] = 'U' /* kun user tables */
-for JOIN kriterier gælder at tabellen der joines mod, nævnes sidst
SELECT A.* FROM TabelA A LEFT JOIN TabelB B ON A.ID = B.ID LEFT JOIN TabelC C ON B.ID = C.ID
istedet for
SELECT A.* FROM TabelA A LEFT JOIN TabelB B ON B.ID = A.ID LEFT JOIN TabelC C ON C.ID = B.ID
-Boolean udtryk (AND, OR, NOT)
Benyt parenteser til at vise hvordan det skal læses
SQL Server skal nok gøre det rigtigt, men læsbarheden er meget bedre
Ikke:
… A = B AND A = C OR A = D …
Men istedet:
… (A = B AND A = C) OR A = D …