5 svar · 719 visningar · startad av Lukaspojken
Jag vill börja med att säga att jag är en förespråkare av lagrade procedurer :)
Men om man paramatiserar sql-satserna i tex .net kod innebär det då att det blir ungefär samma prestanda som med lagrade procedurer? Att en lagrad procedur är förkompilerad kan det tänkas påverka prestandan positivt?
Sen en fråga till. Vad är det som talar för att använda lagrade procedurer framför sql-satser i kod?
En fördel med procedurer är väl utseendet i koden, blir ju färre rader och kompaktare kod alltså enklare att läsa. Du minskar redundansen i koden.
Om man säger att man skulle bygga koden lika effektivt i programmet som i proceduren vinner proceduren(iaf i oracle som är byggt för detta). Eftersom det är som du säger proceduren är ju förkompilerad.
Om vi säger att databasen inte ligger på samma burk som programmet påverkas ju trafiken till ökad eller minskad beroende på ifall procedurer rensar eller utvidgar datan. Du behöver heller inte skicka satser fram och tillbaka om du använder procedurer utan ändas värdena.
Enda direkta nackdelen jag kan komma på är väl överblicken i koden, du vet ju inte riktigt vad proceduren egentligen gör när du bara kollar i din vanliga kod.
Nu pratar jag sql-server och inte databaser generellt.
Lukaspojken skrev:
Men om man paramatiserar sql-satserna i tex .net kod innebär det då att det blir ungefär samma prestanda som med lagrade procedurer?
Nej, därmot skyddar den automatiskt mot sql-injection, dessutom får du på ett tydligare sätt datatyperna specificerade.
Lukaspojken skrev:
Att en lagrad procedur är förkompilerad kan det tänkas påverka prestandan positivt?
Ja i de flesta fall. Det som sparas är en körplan (execution plan), som beskriver det bästa sättet att köra den aktuella frågan på, så istället för att databasen skall behöva beräkna det varje gång frågan körs så hämtar den "ritningen" från cachen och kör den direkt.
Lukaspojken skrev:
Sen en fråga till. Vad är det som talar för att använda lagrade procedurer framför sql-satser i kod?
Bland annat minskar ju nätverkstrafiken eftersom du bara behöver skicka procedurnamnet, parametrar och värden istället för den fullständiga frågan. Utöver det kan du ha bättre koll på säkerheten så att man inte kan köra frågor mot tabellerna direkt exempelvis.
Men allt beror på vad man skall göra och vilka krav man har.
Det finns tillfällen då cachade execution plans inte alltid är bra. Om vi tänker oss att man har en tabell som man gör sökningar mot på ett varchar-fält. Vi tänker oss också att kolumnen är indexerad. Om vi söker på exempelvis "a%" så kommer den troligtvis inte använda indexet, medan om man söker på ett mer unikt namn, exempelvis "nyårsafton%", så kanske det uppfyller alla krav för att indexet skall användas. Om planen sedan den förra sökningen på "a%" är cachad så kommer den inte att använda indexet, trots att det kanske vore det bästa. Detta händer ju bara om man använder lagrade procedurer, om du hade byggt frågan dynamiskt så hade den byggt execution planen varje gång och det hade troligen gått snabbare med dynamisk sql i detta fallet. Om man vet att man alltid vill ha en ny execution plan så kan man använda with recompile-attributet på procedurern, så det finns lösningar på det problemet också.
Det jag ville komma fram till var att det oftast inte finns ett enkelt svar, man måste testa sig fram för att se vilka krav just din applikation har.
Bra beskrivet, Brimba!
Min åsikt är att SP har ett oförtjänt gott rykte, medan "dynamisk SQL" har ett oförtjänt dåligt rykte.
Prestandafördelarna med SP kommer i princip endast till sin rätt i mycket högtrafikerade system med låg varians i frågornas kriterier.
Säkerhetsaspekten (rättigheter till tabeller och data) är en betydligt mer konkret fördel, men situationer där detta är relevant är inte så vanligt förekommande, i synnerhet inte när det gäller system där man har stor kontroll som utvecklare. I större system, där man t.ex. som konsult får arbeta med en kunds känsliga databaser, kan dock SP vara enda alternativet .
Angående det här med execution plan så tror jag det är så att det inte är någon skillnad mellan paramatiserade sql-satser och procedurer. Men kör man icke-paramatiserade sql-satser då skapas det en exekution plan för varje unik sql-sats. Samma gäller för procedurer.
Det här med SQL-injection är en annan bra synpunkt som jag inte tänkt på. Men det borde man väl kunna komma runt genom att skicka in sin paramatiserade sql-sats i sp_executesql?
Sammanfattningsvis skulle man kunna säga att fördelarna med procedurer är:
- Säkerhetsaspeketen
- Nätverkstrafik
Nackdelarna är:
- Att översikten blir något sämre vid användningen av procedurer.
Om ni kommer på några fler så är det bara att skriva ett inlägg...
Lukaspojken skrev:
Angående det här med execution plan så tror jag det är så att det inte är någon skillnad mellan paramatiserade sql-satser och procedurer. Men kör man icke-paramatiserade sql-satser då skapas det en exekution plan för varje unik sql-sats. Samma gäller för procedurer.
I princip kan man tänka så, men även icke-paramatiserade sql-satser kan köras med en cachad exekveringsplan. I synnerhet SQL Server 2005 som använder en hel del mönstermatchning för att hitta bra träffar i cachen.
Lukaspojken skrev:
Det här med SQL-injection är en annan bra synpunkt som jag inte tänkt på. Men det borde man väl kunna komma runt genom att skicka in sin paramatiserade sql-sats i sp_executesql?
Kör man parametriserat (utan att använda parametervärdena för att med konkatenering bygga ihop en ny SQL-sats) så är man injection-säker. sp_executesql behöver man sällan utnyttja "manuellt", utan den nyttjas automatiskt av t.ex. SqlClient i .NET. Men visst kan den vara bra att ha tillhands.
Lukaspojken skrev:
Sammanfattningsvis skulle man kunna säga att fördelarna med procedurer är:
- Säkerhetsaspeketen
- Nätverkstrafik
Ja, det är tämligen rättvist, även om säkerhetsaspekten i vissa lägen kan vara en synnerligen avgörande aspekt. Andra fördelar kan vara:
- DBMS-abstraktion (om man endast arbetar mot DBMS som hanterar SP).
- Uppsamling av affärslogik (men detta är egentligen oftare en nackdel)
- Tydlig parametrisering av frågor
Lukaspojken skrev:
Nackdelarna är:
- Att översikten blir något sämre vid användningen av procedurer.
- Kan bli en driftmardröm om man har flera versioner/installationer av samma databas och måste uppgradera
- Hantering av array-liknande inparameterar är obefintlig
- Frågor med hög kriteriavarians blir ineffektiva
Jag kommer att fortsätta att använda SP i många lägen, och det är verkligen en teknologi man bör bemästra, men det är inte något som man blint ska använda bara för att gammal tradition påskiner att det är bäst.
Om man är ute efter att få ut mer prestanda ur sin databas så är det indexering och exekveringsplaner man ska plugga på.
