Stored procedures zoveel mogelijk gebruiken of niet?

[jimbo123]

Ik vraag mij af of het verstandig is om een stored procedure alleen te gebruiken wanneer je een query vaker dan 1x nodig hebt, of dat het beter is om deze voor alle query's te gebruiken.

Weet iemand ook hoe dit performance-technisch werkt?

[P_de_B]

Het gebruik van sp's heeft nauwelijks performancevoordelen. Een 'gewone' query is net zo snel mits je gebruik maakt van parameterized queries.

[Arethusa]

Via de search zoals je ziet zijn er al wat discussies over geweest. Naar mijn idee heeft het voordelen en nadelen. Ikzelf werk altijd met stored procedures, vind het prettig werken en je houd je sql en vb/c#/andere taal zo veel mogelijk gescheiden. Het is maar net wat je gewend bent.

Misschien is de uitkomst op Google ook wel goed om te lezen.

[ Voor 13% gewijzigd door Arethusa op 12-12-2007 08:43 ]

[gorgi_19]

Je kan ook kijken of je niet een fundamenteler probleem hebt als je de eerdere code consequent niet kan herbruiken.

Discussie is inderdaad al eerder gevoerd; een bekende is onder andere deze: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx; een discussie tussen Frans Bouma en Rob Howard.

P_de_B schreef op woensdag 12 december 2007 @ 08:41:
Het gebruik van sp's heeft nauwelijks performancevoordelen. Een 'gewone' query is net zo snel mits je gebruik maakt van parameterized queries.

Als ik me goed herinner wil SQL Server ook query's zonder parameters zelf 'parametriseren', zodat het executionplan ook opgeslagen kan worden

[ Voor 44% gewijzigd door gorgi_19 op 12-12-2007 09:16 ]

[JKVA]

jimbo123 schreef op woensdag 12 december 2007 @ 08:38:
Ik vraag mij af of het verstandig is om een stored procedure alleen te gebruiken wanneer je een query vaker dan 1x nodig hebt, of dat het beter is om deze voor alle query's te gebruiken.

Consistentie is natuurlijk altijd wenselijk. Ik zou zeggen, kies voor SP's of voor geparametriseerde queries en wijk daar niet steeds vanaf. "Beter" is een woord dat in de IT niets betekent. SP's hebben voor- en nadelen.
Een simpele google search leverde o.a. deze pagina op. http://codebetter.com/blo...ve/2006/05/24/145393.aspx

Hier staan een paar pros en cons.

Weet iemand ook hoe dit performance-technisch werkt?

Verschillend per database, maar doorgaans is de performance vergelijkbaar met een geparametriseerde query.

[cspare]

Kan iemand mij de nadelen van stored procedures noemen dan, behalve de extra ontwikkel (en evt. onderhoud) tijd?

[Xiphalon]

Een voordeel van SPs is dat je al je sql in de database hebt zitten. Hierdoor is het makkelijker om van database te wisselen, zonder de applicatie te moeten wijzigen.

[Verwijderd]

Stored procedures hebben het voordeel dat ze al geparsed en gecompileerd zijn, scheelt toch weer een (klein) beetje in performance.

[JortK]

Verwijderd schreef op woensdag 12 december 2007 @ 10:38:
Stored procedures hebben het voordeel dat ze al geparsed en gecompileerd zijn, scheelt toch weer een (klein) beetje in performance.

Ik denk persoonlijk dat dit performance verschil te verwaarlozen is

[Storme]

JortK schreef op woensdag 12 december 2007 @ 10:44:
[...]

Ik denk persoonlijk dat dit performance verschil te verwaarlozen is

Ik heb momenteel geen database staan op deze laptop, maar ben benieuwd.

Iemand eens tijd om een klein appje te maken die laten we zeggen, 100.000 rijen toevoegd eens met sql en eens met sp's, en natuurlijk timen

Persoonlijk denk ik dat dit geen zo een groot verschil gaat maken, maar voor heel drukke databases kan dit mss wel de druppel zijn dat het systeem al dan niet platlegt ...

[Verwijderd]

Storme schreef op woensdag 12 december 2007 @ 11:52:
[...]
Ik heb momenteel geen database staan op deze laptop, maar ben benieuwd.

Iemand eens tijd om een klein appje te maken die laten we zeggen, 100.000 rijen toevoegd eens met sql en eens met sp's, en natuurlijk timen

Persoonlijk denk ik dat dit geen zo een groot verschil gaat maken, maar voor heel drukke databases kan dit mss wel de druppel zijn dat het systeem al dan niet platlegt ...

Het voorbeeld dat jij aanhaalt slaat op een enkele verwerking en zal dus nauwelijks verschil opleveren. Je praat dan over duizendste van seconden. Zowiezo maakt het weinig uit omdat een sql statement wat al eens uitgevoerd is, ook gecached blijft in zijn parsed vorm. Als je echter een query een maal per dag uitvoert en je hebt enkele duizenden van zulke queries, dan leveren stored procedures wel voordeel op.
Caching vind dan wel plaats, maar verouderd weer en zal dus verwijderd worden door de garbage collect. Stored procedures blijven parsed en compiled, dus je verdient dat continu terug. Bij grote databases (> 1TB) ga je dit echt wel merken. Nogmaals het scheelt niet veel, maar alle beetjes helpen.

[Verwijderd]

Groot voordeel van SP is security. Validatie gebeurt op de server.
Je kunt ook makkelijk de rechten beheren van verschillende gebruikers(als je die hebt natuurlijk) dmv SPs.

[curry684]

gorgi_19 schreef op woensdag 12 december 2007 @ 09:13:
Als ik me goed herinner wil SQL Server ook query's zonder parameters zelf 'parametriseren', zodat het executionplan ook opgeslagen kan worden

Correct.

Verwijderd schreef op woensdag 12 december 2007 @ 10:38:
Stored procedures hebben het voordeel dat ze al geparsed en gecompileerd zijn, scheelt toch weer een (klein) beetje in performance.

Onzin, SP's worden geparsed en gecompileerd bij first use, evenals normale queries, en execution plan, compiled code en caches worden volgens dezelfde regels geflushed als voor normale queries. Er is dus geen enkele performancewinst mee te halen.

Zelf ben ik fel tegen het gebruik van stored procedures voor alles wat niet inherent onderdeel is van de database (maintenance/cleanup jobs die ik beschouw als onderdeel van het complete model).

Verwijderd schreef op woensdag 12 december 2007 @ 13:04:
Groot voordeel van SP is security. Validatie gebeurt op de server.
Je kunt ook makkelijk de rechten beheren van verschillende gebruikers(als je die hebt natuurlijk) dmv SPs.

Dit is alleen boeiend als je de security van de database op hetzelfde model stoelt als de toegangsrechten van de applicatie, zoals bij een Active Directory met SQL Server mogelijk is. En dan nog introduceer je een groot nadeel in de vorm van maintainability: je moet users bij elkaar passende rechten geven op 2 verschillende plaatsen, zijnde in de applicatie en op de database.

[ Voor 25% gewijzigd door curry684 op 12-12-2007 13:14 ]

[EfBe]

Verwijderd schreef op woensdag 12 december 2007 @ 10:38:
Stored procedures hebben het voordeel dat ze al geparsed en gecompileerd zijn, scheelt toch weer een (klein) beetje in performance.

onzin.

procs worden geparsed en geconverteerd naar dezelfde interne relational algebra instructies als een geparameterizeerde query. At runtime wordt deze serie instructies dan geconverteerd naar een execution plan mbv runtime-statistics, zodat alleen at runtime wordt bepaald WAT er precies wordt uitgevoerd, want dat hangt van bepaalde factoren af, zoals statistics.

Het execution plan is dus identiek voor proc code en parameterizeerde queries en wordt at runtime gemaakt en niet opgeslagen (is ook niet nuttig, je moet at runtime immers nog optimizen voor de DAN geldende situatie, dus het opslaan is niet zinvol).

Verwijderd schreef op woensdag 12 december 2007 @ 13:04:
Groot voordeel van SP is security. Validatie gebeurt op de server.
Je kunt ook makkelijk de rechten beheren van verschillende gebruikers(als je die hebt natuurlijk) dmv SPs.

Alleen als je een token meegeeft aan de proc en dat token wordt gechecked in de proc is het veiliger. Immers, als jij een proc pr_DeleteCustomer(@customerID) maakt, dan kan ik die aanroepen met wat voor id ik wil om te zorgen dat deze wordt uitgevoerd. Mag ik niet deleten? Dan kan ik dat ook met rolebased security opgeven op de table.

[ Voor 26% gewijzigd door EfBe op 12-12-2007 13:15 ]

[curry684]

offtopic:
Anders typen we tegelijk 'onzin' met dezelfde lap uitleg

[Verwijderd]

EfBe schreef op woensdag 12 december 2007 @ 13:13:
[...]

onzin.

procs worden geparsed en geconverteerd naar dezelfde interne relational algebra instructies als een geparameterizeerde query. At runtime wordt deze serie instructies dan geconverteerd naar een execution plan mbv runtime-statistics, zodat alleen at runtime wordt bepaald WAT er precies wordt uitgevoerd, want dat hangt van bepaalde factoren af, zoals statistics.

Het execution plan is dus identiek voor proc code en parameterizeerde queries en wordt at runtime gemaakt en niet opgeslagen (is ook niet nuttig, je moet at runtime immers nog optimizen voor de DAN geldende situatie, dus het opslaan is niet zinvol).

Geen onzin, in een profesionele database (Oracle) worden ze geparsed en gecompileerd opgeslagen. Wijzigt er iets aan de definities van de onderliggende objecten, wordt de stored procedure op invalid gezet en opnieuw gecompileerd bij de eerste aanroep.

[curry684]

Ja dus? Je bedoelt dus te zeggen dat je exact 1 keer per restart van de server een minimale tijdswinst haalt omdat er al gecompileerd is?

Zoals EfBe correct aangeeft wordt pas @ runtime het definitieve execution plan bepaald, omdat dat niet alleen afhankelijk is van het model maar ook van de data.

[Niemand_Anders]

Stored procedures maken het juist lastiger om van database te wisselen. Lang niet alle databases ondersteunen stored procedures. Voor dit probleem worden al jaar en dag data access layers gebruikt samen met de business en presentatie lagen, de zogenaamde 3-tier applicaties.

Stored procedures kunnen wel handig zijn als er meerdere database lookups en controles moeten worden uitgevoerd. Een andere reden om stored procedures te gebruiken is als de verbinding tussen client en database server traag is (bijv. omdat de database in een hosting centrum staat en alleen bereikbaar is via een VPN verbinding). Het aanroepen van 1 stored procedure kan dan een performance winst opleveren ten opzicht van een aantal queries achter elkaar naar de database sturen.

Nog een laatste punt omtrend het wisselen van database. Er bestaat niet zoals als 'eenvoudig' wisselen van database. Dergelijke migratie trajecten kosten meestal veel tijd. Ondersteuning voor meerdere databases is eigenlijk alleen interessant als je niet weet wie jouw software gaat gebruiken of voor complexe CMS/CRM systemen.

Aan de andere kant is er juist een trend die data en business logica juist weer samen brengt. Kijk eens naar een techniek als linq. De discussie wat beter is SP of losse query is dezelfde als Linux vs Windows, vi(m) vs Emacs, etc. Iedereen heeft een andere voorkeur en zal die verkondigen als de heilige graal. Zet de voordelen en nadelen van beide een op een stukje papier en bepaal welk methode vervolgens het beste bij jouw projecten horen.

[Verwijderd]

curry684 schreef op woensdag 12 december 2007 @ 13:22:
Ja dus? Je bedoelt dus te zeggen dat je exact 1 keer per restart van de server een minimale tijdswinst haalt omdat er al gecompileerd is?

Zoals EfBe correct aangeeft wordt pas @ runtime het definitieve execution plan bepaald, omdat dat niet alleen afhankelijk is van het model maar ook van de data.

Heeft niets met de herstart van doen, gebeurt alleen als je een tabel of een andere stored procedure wijzigd waar de stored procedure gebruik van maakt. Anders zou de database na het opstarten niet vooruit te branden zijn omdat hij alles aan het (her)compileren is. Een erp pakket heeft tienduizenden packages, een volledige recompile is dan niet eens mogelijk vanwege de afhankelijkheden. Soms is package A afhankelijk van package B en viceversa. Bij procedures is dit niet mogelijk omdat de installatie van de tweede procedure dan gewoon fout gaat.

Het executie plan is inderdaad afhankelijk van de data(-hoeveelheid). Om dat probleem op te lossen worden er statistics verzameld (wekelijks of maandelijks). Zodoende weet de database op ieder moment wat ongeveer de hoeveelheid data is voor een tabel. Het commando voor het verzamelen van de statistics invalideerd de bijbehorende packages en procedures indien de resultaten teveel afwijken van de vorige run. Gelukkig moet het verschil erg groot zijn (in absolute hoeveelheden), wil dit gebeuren.

[whoami]

Verwijderd schreef op woensdag 12 december 2007 @ 12:09:

Caching vind dan wel plaats, maar verouderd weer en zal dus verwijderd worden door de garbage collect. Stored procedures blijven parsed en compiled, dus je verdient dat continu terug. Bij grote databases (> 1TB) ga je dit echt wel merken. Nogmaals het scheelt niet veel, maar alle beetjes helpen.

Wat is dat nou voor onzin. Waarom zou een SP sneller zijn dan een gewone parametrized query op eenzelfde tabel, met dezelfde indexes, met dezelfde inhoud, etc... ?

Het enige waar SP's voordeel in bieden, is als je echt 'data-intentensieve' operaties moet gaan doen, die bestaan uit meerdere SQL statements. Op die manier ga je alles op de server houden, en bespaar je vele roundtrips.

Het grote nadeel van SP's is wat curry al aanhaalde: je kan je model ermee vernaggelen.

[cspare]

Tot zo ver gaat het alleen maar over waarom stored procedures bepaalde meerwaarde_niet_ hebben ten opzichte van directe queries, maar niet over de nadelen van het gebruik.

Los daarvan denk ik dat stored procedures wel degelijk een goede keus zijn, met name voor grote gedistribueerde applicaties. Door het gebuik van sprocs kan je precies afdwingen welke data op welke manier mag worden benaderd. Zo kan je bijvoorbeeld bepaalde kolommen niet laten zien (denk aan gevoelige informatie) of niet te wijzigen maken (denk aan meta informatie, zoals wie de laatste update heeft doorgevoerd).

Daarnaast kan je (input) validatie doen op database nivo, wat erg handig is als er meerdere clients (ongecontroleerd) verbinding maken met je server.

Daarnaast kan je met een stored procedure transacties binnen de server laten leven, als er iets fout gaat word deze automatisch afgebroken. als je losse queries op een DB afvuurt en je verbinding wordt verbroken na je BEGIN TRAN query, blijft die transactie (en alle locks) langer leven.

[ Voor 26% gewijzigd door cspare op 12-12-2007 14:08 ]

[Verwijderd]

whoami schreef op woensdag 12 december 2007 @ 13:52:
[...]

Wat is dat nou voor onzin. Waarom zou een SP sneller zijn dan een gewone parametrized query op eenzelfde tabel, met dezelfde indexes, met dezelfde inhoud, etc... ?

Het enige waar SP's voordeel in bieden, is als je echt 'data-intentensieve' operaties moet gaan doen, die bestaan uit meerdere SQL statements. Op die manier ga je alles op de server houden, en bespaar je vele roundtrips.

Het grote nadeel van SP's is wat curry al aanhaalde: je kan je model ermee vernaggelen.

Was al verteld, een stored procedure of een package is al geparsed en gecompileerd (althans in Oracle).

[whoami]

cspare schreef op woensdag 12 december 2007 @ 13:52:
Tot zo ver gaat het alleen maar over waarom stored procedures bepaalde meerwaarde_niet_ hebben ten opzichte van directe queries, maar niet over de nadelen van het gebruik.

Toch wel, door SP's te gaan gebruiken kan je hele model waarin je je business weergeeft onduidelijk gaan zijn (let wel, kan)

Los daarvan denk ik dat stored procedures wel degelijk een goede keus zijn, met name voor grote gedistribueerde applicaties. Door het gebuik van sprocs kan je precies afdwingen welke data op welke manier mag worden benaderd. Zo kan je bijvoorbeeld bepaalde kolommen niet laten zien (denk aan gevoelige informatie) of niet te wijzigen maken (denk aan meta informatie, zoals wie de laatste update heeft doorgevoerd).

en dat kan niet met een gewone query ?

Daarnaast kan je (input) validatie doen op database nivo, wat erg handig is als er meerdere clients (ongecontroleerd) verbinding maken met je server.

Als je software goed opgezet is, kan je dat ook ....

Hoedanook, deze discussie is al in 't lang en breed gevoerd elders ...

[Face_-_LeSS]

Dus qua performance op de server maak het niet veel uit. Maar hoe zit het met de data van de query die over een lijntje moet?

Ik kan me voorstellen dat wanneer ik een stored procedure aanroep vanuit een applicatie, en dus bijv. alleen de query string

SQL:

GetAllMessagesWithManyDetails

verstuur naar de server, dat dat sneller is dan dat ik een hele query moet versturen bijv:

SQL:

SELECT Veld1, Veld2, Veld3, Veld4, Veld5, Veld6, Veld7, Veld8, Veld9, Veld10,
Veld11, Veld12, Veld13, Veld14, Veld15, Veld16, Veld17, Veld18, Veld19,
Veld20, Veld21, Veld22, Veld23, Veld24, Veld25, Veld26
FROM Messages
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
WHERE bla = bla
AND bla = bla
AND bla = bla
AND bla = bla
ORDER BY bla  DESC, bla DESC

Of zit ik er helemaal naast?

[cspare]

whoami schreef op woensdag 12 december 2007 @ 14:23:
[...]
Toch wel, door SP's te gaan gebruiken kan je hele model waarin je je business weergeeft onduidelijk gaan zijn (let wel, kan)


[...]
en dat kan niet met een gewone query ?

Nee, je kan in je DB geen select of update rechten per kolom defineren, alleen per tabel.

[...]
Als je software goed opgezet is, kan je dat ook ....

Maar niet altijd heb jij als DB ontwerper controle over de applicatie die de database aan (gaat) spreken.

[P_de_B]

Verwijderd schreef op woensdag 12 december 2007 @ 14:21:
[...]

Was al verteld, een stored procedure of een package is al geparsed en gecompileerd (althans in Oracle).

Het compileren kost echt geen drol aan tijd. Het bepalen van het executieplan wel; in het verleden kon dit alleen bij sp's bewaard worden (iig bij SQL Server) tegenwoordig blijft het executieplan ook bewaard bij geparameteriseerde queries.

[P_de_B]

cspare schreef op woensdag 12 december 2007 @ 14:34:
[...]

Nee, je kan in je DB geen select of update rechten per kolom defineren, alleen per tabel.

In SQL Server kan dit wel hoor.

[Verwijderd]

Misschien is het handig als de TS even vermeldt welke database dat hij gebruikt, anders blijven we allerlei verschillende implementaties door elkaar halen, cq met elkaar vergelijken.

[Gomez12]

Face_-_LeSS schreef op woensdag 12 december 2007 @ 14:33:
Dus qua performance op de server maak het niet veel uit. Maar hoe zit het met de data van de query die over een lijntje moet?

Ik kan me voorstellen dat wanneer ik een stored procedure aanroep vanuit een applicatie, en dus bijv. alleen de query string

SQL:

GetAllMessagesWithManyDetails

verstuur naar de server, dat dat sneller is dan dat ik een hele query moet versturen bijv:

SQL:

SELECT Veld1, Veld2, Veld3, Veld4, Veld5, Veld6, Veld7, Veld8, Veld9, Veld10,
Veld11, Veld12, Veld13, Veld14, Veld15, Veld16, Veld17, Veld18, Veld19,
Veld20, Veld21, Veld22, Veld23, Veld24, Veld25, Veld26
FROM Messages
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
INNER JOIN blablabla ON blablabla
WHERE bla = bla
AND bla = bla
AND bla = bla
AND bla = bla
ORDER BY bla  DESC, bla DESC

Of zit ik er helemaal naast?

In theorie heb je gelijk, in praktijk is dit bit-verschil niet te merken behalve als je nog met een 300 baud modem gaat zitten connecten met je dbase.

Dus ja, je zit er helemaal naast...

Maar wat wel een klein voordeel van SP's kan zijn bij een grote dbase is inderdaad je security. Je zou iets kunnen bedenken dat jouw app altijd onder user x ( of huidige user en dan alleen vanaf ip x ) connect naar de dbase, hier heeft de app dan alle benodigde rechten.
Als de gebruiker dan odbc wil gebruiken dan krijgt hij een andere inlognaam hiervoor die alleen met SP's kan werken.
Maar in de praktijk gebeurt het meer dat er dan een aparte report module opgezet wordt binnen de app, deze kan compleet met query's werken ( en dus net zo makkelijk uit te breiden als zelf odbc hebben mits je een beetje snel reagerende dba/programmeur hebt )

[EfBe]

Verwijderd schreef op woensdag 12 december 2007 @ 13:36:
[...]

Heeft niets met de herstart van doen, gebeurt alleen als je een tabel of een andere stored procedure wijzigd waar de stored procedure gebruik van maakt. Anders zou de database na het opstarten niet vooruit te branden zijn omdat hij alles aan het (her)compileren is. Een erp pakket heeft tienduizenden packages, een volledige recompile is dan niet eens mogelijk vanwege de afhankelijkheden. Soms is package A afhankelijk van package B en viceversa. Bij procedures is dit niet mogelijk omdat de installatie van de tweede procedure dan gewoon fout gaat.

Het executie plan is inderdaad afhankelijk van de data(-hoeveelheid). Om dat probleem op te lossen worden er statistics verzameld (wekelijks of maandelijks). Zodoende weet de database op ieder moment wat ongeveer de hoeveelheid data is voor een tabel. Het commando voor het verzamelen van de statistics invalideerd de bijbehorende packages en procedures indien de resultaten teveel afwijken van de vorige run. Gelukkig moet het verschil erg groot zijn (in absolute hoeveelheden), wil dit gebeuren.

Dat geeft dus al aan dat wat jij zegt onzin is mbt compiled storage: want waarom moet je een proc compileren (en waarnaartoe dan?) wanneer je het execution plan (== wat je feitelijk runt!) at runtime bepaalt!

Oracle heeft bij 8i dacht ik al een runtime optimizer toegevoegd die procs compileren overbodig maakte. DB2 heeft nog wel compiled procs in sommige gevallen, maar dat komt omdat je procs in 1001 talen kunt maken in DB2.

[Fiander]

Bij mij op het werk gebruiken we voornamelijk SP's, zodat later, als het nodig blijkt te zijn,
het datamodel aangepast kan worden zonder de applicatie omver te hoeven halen.

hierbij moet je oa denken aan het denormaliseren van bepaalde data doormidel van een geindexeerde view, zodat bepaalde selects met minder joins toe kunnen.
of juist het toevoegen van een extra normalisatie.

Dit is al elkele keren een life-saver geweest, en zou niet mogelijk zijn geweest zonder stored procedures. ( kan wel, maar dan met een hercompilatie van de applicatie, het opnieuw testen van de applicatie, en het opnieuw distrubueren van de applicatie. )

Let wel, je moet wel goed gedocumenteert hebben wat de specs zijn van alle procedures, en vooral welke tabelen ze raken.

verder is het toestaan van selects/deletes/updates zonder SP's mijns inziens gewoon vragen om SQL-injection, en iets wat je NOOIT moet toestaan als DBA.
Dit houd ook in dat query building binnen een SP ook nono is.

[ Voor 15% gewijzigd door Fiander op 12-12-2007 21:21 ]

[Face_-_LeSS]

Gomez12 schreef op woensdag 12 december 2007 @ 20:04:
[...]

In theorie heb je gelijk, in praktijk is dit bit-verschil niet te merken behalve als je nog met een 300 baud modem gaat zitten connecten met je dbase.

Dus ja, je zit er helemaal naast...

Hmm ja inderdaad, nu ik er over nadenk... Over het algemeen bevind de db server zich ook in het zelfde netwerk (LAN), dus dan is bandbreedte geen probleem natuurlijk.

[Gomez12]

Fiander schreef op woensdag 12 december 2007 @ 21:18:
Bij mij op het werk gebruiken we voornamelijk SP's, zodat later, als het nodig blijkt te zijn,
het datamodel aangepast kan worden zonder de applicatie omver te hoeven halen.

Grappig punt, nooit zo over nagedacht. Maar waarom zou je je datamodel aanpassen als er in je app niets verandert?
Enige geldige reden die ik zo snel kan bedenken is dat je erg veel maatwerk hebt wat ook ingrijpt op je datamodel en je bij de rest van de klanten geen problemen wilt veroorzaken. Maar dan denk ik dat je nog eens goed moet nadenken over je app.

En wat gebeurt er als er met grote spoed een verandering aan je app moet gebeuren, dan moet je je
datamodel aanpassen, je sp's aanpassen en je app aanpassen

Dit is al enkele keren een life-saver geweest, en zou niet mogelijk zijn geweest zonder stored procedures.

Life saver??? Dan is er volgens mij iets grondig mis met je initiele datamodel. Zet dit eens goed op en breng dan een nieuwe versie uit, iedereen ziet overal het verschil en iedereen is er blij mee.

verder is het toestaan van selects/deletes/updates zonder SP's mijns inziens gewoon vragen om SQL-injection, en iets wat je NOOIT moet toestaan als DBA.
Dit houd ook in dat query building binnen een SP ook nono is.

SQL-injection zonder SP's... Het moet niet gekker worden, mijn apps hebben de meeste business logica ( dus ook de validatie van invoer ) in de app zitten en de dbase is alleen maar voor opslag.
De dbase doet wel enkele simpele checks om te zien of een veld valide is, maar een ean-controle bijv bouw ik toch echt in mijn app in, niet in mijn dbase...
Mijn app moet namelijk ook de foutmelding geven aan de gebruiker zodat deze er iets aan kan doen, of heb jij zo'n groot systeem dat je op je dbase checks hebt zitten, indien fout wordt dit doorgegeven aan je sp's, deze geven het weer door aan je app. Deze produceert een foutmelding voor de gebruiker.

[gorgi_19]

Fiander schreef op woensdag 12 december 2007 @ 21:18:
verder is het toestaan van selects/deletes/updates zonder SP's mijns inziens gewoon vragen om SQL-injection, en iets wat je NOOIT moet toestaan als DBA.
Dit houd ook in dat query building binnen een SP ook nono is.

Hoe wil je dit doen met parametrized queries?
Ook met SP's kan je SQL Injection krijgen; het idee dat je veilig bent omdat je SP's gebruikt is nonsense.

[ Voor 12% gewijzigd door gorgi_19 op 12-12-2007 21:38 ]

[Creepy]

Fiander schreef op woensdag 12 december 2007 @ 21:18:
Dit is al elkele keren een life-saver geweest, en zou niet mogelijk zijn geweest zonder stored procedures. ( kan wel, maar dan met een hercompilatie van de applicatie, het opnieuw testen van de applicatie, en het opnieuw distrubueren van de applicatie. )

Dus bij een SP aanpassing zijn je tests minder rigoreus dan bij een code wijziging? Een SP zie ik als normale code. Als die verandert zal je alle andere code die de SP raakt moeten testen, wat mij betreft niet anders dan een normale query in een datalayer ergens.

Ik kan me overigens ook vrij weinig datamodel wijzigingen voorstellen die je applicatie niet raken en alleen maar je SP behalve bepaalde optimalisaties.
Edit: wat Gomez12 zegt

verder is het toestaan van selects/deletes/updates zonder SP's mijns inziens gewoon vragen om SQL-injection, en iets wat je NOOIT moet toestaan als DBA.
Dit houd ook in dat query building binnen een SP ook nono is.

No offence, maar nog nooit gehoord van geparametriseerde query's? Daarmee is SQL injection niet mogelijk omdat van een parameter het type bekend is en je dus niet een string als platte SQL in de query kan krijgen. Dat is dus geen voordeel van een SP.

Edit: wat gorgi zegt dus.

offtopic:
Damn ik ben traag

[ Voor 40% gewijzigd door Creepy op 12-12-2007 21:44 ]

[Fiander]

Tja, jammer genoeg hebben we hier een ontwikkel afdeling, en als iets klaar is, wordt het getest ofdat het werkt, en daarna over de muur gegooit. wou dat het anders was, maar dat is het helaas niet.

Verder hebben we hier jammer genoeg te maken met 'inteligente' gebruikers, welke dus de connectiestring van de applicaties weten te vinden, en de database met andere applicaties gaan koppelen. het is zelfs gebeurt dat iemand met acces Oracle omzeep hielp, omdat ie een tabel even aan het leeggooien was. ( acces doet voor elke regel een delete. )

verder veranderen de behoeftes van gebruikers vaak in de loop van de tijd, waardoor er een verschuiving van de druk in de tabellen voorkomt, waardoor je soms deze maatregels moet nemen.
en de ontwikkelaars zijn dan als met iets anders bezig.

verder, als ik in een SP een aanpassing doe, samen met een wijziging in het model, en ik koppel dit terug naar de ontwikkelaars, dan ben ik in een dag klaar, en draaien me servers weer op 100%

als ik eerst naar de ontwikkelaars moet, en daarna het test proces van de applicatie in moet, en daarna de applicatie uitrollen, ben ik 2 maand verder. dat heb je met grote bedrijven.

[Fiander]

Creepy schreef op woensdag 12 december 2007 @ 21:38:
[...]

Dus bij een SP aanpassing zijn je tests minder rigoreus dan bij een code wijziging? Een SP zie ik als normale code. Als die verandert zal je alle andere code die de SP raakt moeten testen, wat mij betreft niet anders dan een normale query in een datalayer ergens.

tuurlijk test ik / wij de SP even goed, alleen moet voor een applicatie test, er een volledig team samen gestelt worden, alles besproken worden enz enz enz, en voor een SP hoeft alleen getest worden wat er gewijzicht is, en dat is dus niet de client.

Creepy schreef op woensdag 12 december 2007 @ 21:38:
[...]
No offence, maar nog nooit gehoord van geparametriseerde query's? Daarmee is SQL injection niet mogelijk omdat van een parameter het type bekend is en je dus niet een string als platte SQL in de query kan krijgen. Dat is dus geen voordeel van een SP.

Edit: wat gorgi zegt dus.

offtopic:
Damn ik ben traag

klopt, echter hebben hiero om de een of de andere reden de gebruikers toegang tot de connectiestring, en willen ze nogal eens dingen doen die niet mogen.

[Fiander]

gorgi_19 schreef op woensdag 12 december 2007 @ 21:36:
[...]

Hoe wil je dit doen met parametrized queries?
Ook met SP's kan je SQL Injection krijgen; het idee dat je veilig bent omdat je SP's gebruikt is nonsense.

ik ben ook niet veilig omdat ik SP's gebruik. ik ben veiliger omdat mijn gebruikers geen querys mogen uitvoeren.

[Gomez12]

Fiander schreef op woensdag 12 december 2007 @ 21:51:
Tja, jammer genoeg hebben we hier een ontwikkel afdeling, en als iets klaar is, wordt het getest ofdat het werkt, en daarna over de muur gegooit. wou dat het anders was, maar dat is het helaas niet.

Als het werkt werkt het toch??? Jij hebt liever dat het niet werkt ofzo??? Werkt het niet naar behoren dan moet je de test-methodiek aanpassen, niet het datamodel...

Verder hebben we hier jammer genoeg te maken met 'inteligente' gebruikers, welke dus de connectiestring van de applicaties weten te vinden, en de database met andere applicaties gaan koppelen. het is zelfs gebeurt dat iemand met acces Oracle omzeep hielp, omdat ie een tabel even aan het leeggooien was. ( acces doet voor elke regel een delete. )

Dus oftewel je security van je dbase staat niet goed en je contract met je klant is niet goed opgezet. Heel simpel voorbeeld, verschillende klanten hebben de connection strings tot hun gegevens. Klagen zij over performance / rare dingen dan wordt er eerst de query-log nagelopen, staat hier 1 query in die niet van onze apps afkomstig is in de afgelopen week dan heeft de klant geen support.
Een klant mag best zijn eigen query's / systeempjes bouwen bij ons. Maar een klant hoeft geen support te vragen zolang deze systeempjes draaien. Wij leveren een app met een dbase en wij geven garantie / support op onze app, niet op de dbase als daar andere apps ook in zitten te wroeten.
Het kan wel, maar dat is risico klant.

verder veranderen de behoeftes van gebruikers vaak in de loop van de tijd, waardoor er een verschuiving van de druk in de tabellen voorkomt, waardoor je soms deze maatregels moet nemen.

Klinkt mij als dat je op een directie vergadering moet verantwoorden waarom je veranderingen in je datamodel wilt doorvoeren zonder dat je app verandert. Je datamodel is imho gewoon niet goed.

verder, als ik in een SP een aanpassing doe, samen met een wijziging in het model, en ik koppel dit terug naar de ontwikkelaars, dan ben ik in een dag klaar, en draaien me servers weer op 100%

als ik eerst naar de ontwikkelaars moet, en daarna het test proces van de applicatie in moet, en daarna de applicatie uitrollen, ben ik 2 maand verder. dat heb je met grote bedrijven.

Dus oftewel een app verandering betekent 2 maanden testen, maar een SP wijziging betekent geen formeel test-proces en in 1 dag klaar. Volgens mij zit er iets fout in jullie procedures, want een SP verandering is imho ( in deze situatie ) nog steeds 100% dezelfde impact als een app verandering.
Bij een app verandering kan je 1 schermpje wat 1x per jaar aangeroepen wordt vergeten, maar bij een SP verandering wordt het veel geniepiger, in je SP maak je een foutje en over je hele app heb je dezelfde doorwerkende fout waardoor niemand het meer ziet.
( Doe voor de grap eens bij de SP voor de berekening van de totaal omzetten per klant het eindgetal eens -100, en kijk eens hoeveel mensen alle bonnen die leiden tot dit eindresultaat gaan nakijken omdat het eindgetal overal hetzelfde is moet dit wel kloppen, dus er moet ergens 1 foute bon zitten... )

Fiander schreef op woensdag 12 december 2007 @ 21:56:
[...]
tuurlijk test ik / wij de SP even goed, alleen moet voor een applicatie test, er een volledig team samen gestelt worden, alles besproken worden enz enz enz, en voor een SP hoeft alleen getest worden wat er gewijzicht is, en dat is dus niet de client.

Nee, alleen maar de data die de client gebruikt... Subtiel verschil wat imho beter getest moet worden dan de client zelf maar ok.

[...]
klopt, echter hebben hiero om de een of de andere reden de gebruikers toegang tot de connectiestring, en willen ze nogal eens dingen doen die niet mogen.

Verbied ze dit dan, of door rechten of door contracten. Nu heb je een tussenoplossing die imho veel gevaarlijker is omdat jij direct in de datastroom veranderingen zit door te voeren, waardoor als jij een foutje maakt je app kan werken met gegevens die niet in de dbase staan.

[creator1988]

Jongens, wat is er mis met het downloaden van de database als een csv bestand, deze bewerken op de client en dan weer terug uploaden. Gaat lekker snel en je kan makkelijk bij al je tabellen!

En bovendien kunnen je gebruikers de data ook bewerken in Excel!

[ Voor 15% gewijzigd door creator1988 op 12-12-2007 22:25 ]

[Verwijderd]

Ik weet niet of je gevoel voor humor zo gewaardeerd wordt.

[Gomez12]

creator1988 schreef op woensdag 12 december 2007 @ 22:24:
Jongens, wat is er mis met het downloaden van de database als een csv bestand, deze bewerken op de client en dan weer terug uploaden. Gaat lekker snel en je kan makkelijk bij al je tabellen!

En bovendien kunnen je gebruikers de data ook bewerken in Excel!

Ooit wel eens gezien wat excel kan doen met een csv bestand wat gewoon even ingelezen wordt? Gooi eens in een csv bestand een ean code, open dit csv bestand in excel via volgende,volgende,volgende,finish en sla dan het csv bestand opnieuw op en gooi het terug in de dbase. Veel plezier...

Of numerieke codes met streepjes ertussen die excel probeert om te zetten in datums.
Of, of, of...

kijk, met csv bestanden valt goed te werken, maar niet voor de gemiddelde gebruiker die niet nadenkt, dan gaat excel opeens nadenken en dan gaat het fout. Er is een reden dat formaten als xml gewild zijn...

[creator1988]

Gomez12 schreef op woensdag 12 december 2007 @ 22:32:
[...]

Ooit wel eens gezien wat excel kan doen met een csv bestand wat gewoon even ingelezen wordt? Gooi eens in een csv bestand een ean code, open dit csv bestand in excel via volgende,volgende,volgende,finish en sla dan het csv bestand opnieuw op en gooi het terug in de dbase. Veel plezier...

Of numerieke codes met streepjes ertussen die excel probeert om te zetten in datums.
Of, of, of...

kijk, met csv bestanden valt goed te werken, maar niet voor de gemiddelde gebruiker die niet nadenkt, dan gaat excel opeens nadenken en dan gaat het fout. Er is een reden dat formaten als xml gewild zijn...

Oh... maar als ik mijn database opsla in csv formaat in de cookie van een gebruiker en die terug zend naar de webserver dan kan de gebruiker er niet aanzitten. Dan gaat het toch wel goed?

()

[whoami]

creator1988, blijf even ontopic en hou dergelijke nonsens voor de HK ofzo.

[Fiander]

@Gomez12 ik wou dat dat bij ons allemaal mogelijk was...

maar helaas, we ziten vast aan procedures, en één daarvan is : een strikte scheiding van functies. ik ben DBA, en heb geen rechten/zeggenschap over de servers. en de werkplekken worden door weer een andere club beheert.

en wat betreft support, nou, dan gaan er mensen op hun strepen staan, en word het alsnog doorgedrukt. en daar sta je dan met je plannen.

[ Voor 19% gewijzigd door Fiander op 12-12-2007 23:41 ]

[Gomez12]

Fiander schreef op woensdag 12 december 2007 @ 23:40:
@Gomez12 ik wou dat dat bij ons allemaal mogelijk was...

maar helaas, we ziten vast aan procedures, en één daarvan is : een strikte scheiding van functies. ik ben DBA, en heb geen rechten/zeggenschap over de servers. en de werkplekken worden door weer een andere club beheert.

en wat betreft support, nou, dan gaan er mensen op hun strepen staan, en word het alsnog doorgedrukt. en daar sta je dan met je plannen.

Ok, maar kan er dan gezegd worden dat jouw eerdere post dus redelijk afgezwakt kan worden in een normale situatie?

[Fiander]

hangt er vanaf.
de TS vroeg om redenen waarom SP te gebruiken, en ik noem de redenen waarom wij ze gebruiken.

kan dat afgezwakt worden ?

[Gomez12]

Fiander schreef op donderdag 13 december 2007 @ 00:34:
hangt er vanaf.
de TS vroeg om redenen waarom SP te gebruiken, en ik noem de redenen waarom wij ze gebruiken.

kan dat afgezwakt worden ?

Mwah, jullie hebben hopelijk een vrij unieke situatie.

Zonder achtergrond vind ik jouw post vrij waardeloos omdat het imho een uitzondering is en geen standaard practice, als jij dan ook nog zegt dat select / insert / update query's volgens jou niet zonder SP moeten / kunnen dan vind ik dat een heel erg boute uitspraak waarvan ik niet denk dat het voor TS ( of voor de andere 99% van de SQL gebruikers / dba's ) van toepassing is.

But it's just my 2 cents.

[Verwijderd]

EfBe schreef op woensdag 12 december 2007 @ 21:05:
[...]

Dat geeft dus al aan dat wat jij zegt onzin is mbt compiled storage: want waarom moet je een proc compileren (en waarnaartoe dan?) wanneer je het execution plan (== wat je feitelijk runt!) at runtime bepaalt!

Oracle heeft bij 8i dacht ik al een runtime optimizer toegevoegd die procs compileren overbodig maakte. DB2 heeft nog wel compiled procs in sommige gevallen, maar dat komt omdat je procs in 1001 talen kunt maken in DB2.

Wat zit je nu te bazelen, vanaf Oracle 8i is Oracle van rule-based optimizer naar cost-based optimizer geswitched. Vandaar dat er nu analyse runs nodig zijn, deze zijn namenlijk specifiek voor cost-based, rule-based kijkt alleen maar naar de index definities.
Echter dat doet hij niet voor procedures/packages omdat die al gecompileerd zijn. Ik heb het ook helemaal niet over de taal gehad. Dat kan in Oracle van alles zijn, PL/SQL, C, java enz. De SQL statements zijn embedded in de taal en gaan niet meer door de optimizer.
Heb je uberhaupt wel gelezen wat ik neergezet heb ?