Den gode stored procedure

Owned by Rune Gram-Madsen (Unlicensed)
Last updated: Nov 07, 2022 by Peter Tilsted
6 min read


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 …





Tilgængelighed: https://starwiki.atlassian.net/was