[SQL] Stored procedures - Wat is de beste manier?

[akakiwi]

Hallo,

Ik werk al een hele tijd met SQL server en vraag me het volgende af.
Als je met stored procedures werkt kun je deze op 2 manieren maken.
Namelijk. Voor elke actie een aparte stored procedure. Bewerkingen op, bijvoorbeeld, TBL_Gebruikers zouden dan gebruik maken van de volgende stored procedures:
- OpslaanGebruiker
- VerwijderGebruiker
- WijzigGebruiker
Elke SP handelt eigen statements uit.

Nu heb ik er laatst voor gekozen om deze 3 SP's samen te voegen onder de naam
- BeheerGebruiker
Deze SP krijgt dan als extra parameter een @Actie VARCHAR(20) mee en in de SP staan 3 IF statements
IF @Actie = 'Toevoegen'
IF @Actie = 'Wijzigen'
IF @Actie = 'Verwijderen'

Het einde van elke IF wordt afgesloten met het RETURN statement, zodat de overige IF's niet worden gecontroleerd.

Nu vraag ik me af welke methode het snelste is?
De methode met voor elke actie een eigen SP, of alles in 1 SP gestopt.
Het laatste heeft als voordeel dat je minder SP's hoeft te maken en makkelijk overzicht hebt in je Analyser en Enterprise Manager.

Ik zou graag van jullie willen weten hoe jullie je SP's opbouwen en waarom. Mocht iemand ergens gevonden hebben welke methode wordt aangeraden omdat deze het snelst is, laat het dan ook even weten aub.

Alvast bedankt.

[curry684]

Sja doet hoe dan ook extra werk als je het in 1 functie doet, dus is het per definitie langzamer. Ik vind het ook geen toonbeeld van goede stijl om nauwelijks gerelateerde functionaliteit in dezelfde functie te stoppen

[whoami]

De eerste manier is de beste.

Gewoon: omdat je die procedure's dan makkelijker kunt gaan hergebruiken
En omdat je in de tweede manier de uit te voeren functionaliteit gaat gaan bepalen door een of ander argument, en ik weet niet of SQL Server dan execution plans kan gaan cachen.

[akakiwi]

@Whoami:
Daar heb je een heel goed punt inderdaad. Door die execution plans worden SP's sneller uitgevoerd.

Algemeen:
2de optie ben ik pas 2 dagen aan het proberen, dus vandaar mijn vraag.

[Verwijderd]

Bij 1 procedure voor 3 handelingen kan je ook klem lopen met gebruikersrechten.

[d00d]

Een andere reden om geen @Actie te gebruiken is dat de query optimizer zijn werk niet meer goed kan doen. Hij maakt een plan voor de sp, maar moet deze steeds aanpassen omdat de query heel andere dingen doet.

Dit gaat behoorlijk ten koste van de performance.

[curry684]

Execution plans worden opgesteld en gecached onafhankelijk van de gegeven parameters omdat deze niet van invloed zijn op de inhoudelijke uitvoering, hoogstens op filtering of resultaten (tenzij je EXECUTE SQL gaat gebruiken in de SP, maar dan ben je sowieso ongecached bezig). De Query Optimizer zal dus zijn werk goed blijven doen, maar doet wel iedere call een overbodige stringcompare (die in SQL duurder is dan in C++, geloof me) en er wordt een overbodige parameter over het netwerk geslingerd. De overhead is het wmb simpelweg niet waard, omdat het qua overzichtelijkheid dus ook imho laakbaar is om verschillende functionaliteit (INSERT, UPDATE en DELETE, simpel gesteld) binnen 1 SP te stoppen.

[whoami]

d00d schreef op 24 september 2004 @ 14:18:
Een andere reden om geen @Actie te gebruiken is dat de query optimizer zijn werk niet meer goed kan doen. Hij maakt een plan voor de sp, maar moet deze steeds aanpassen omdat de query heel andere dingen doet.

SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.

Hij maakt dus geen plan aan voor de stored procedure, maar voor de statements daarin.