[SQL] Stored Procedure en dynamische query

[RetepV]

Hi, ik heb een stored procedure met een hele grote SELECT query waar ik een aantal waardes aan mee geef. Afhankelijk van of deze waardes ingevuld zijn of leeg zijn moet de WHERE clause van de SELECT query anders zijn.

Dit is een (heel erg ) verkorte versie:

code:

PROCEDURE spTest
@Plaats as varchar(50)
AS

IF (@Plaats IS NULL) OR (@Plaats = '')
    SELECT * FROM Adres
ELSE
    SELECT * FROM Adres WHERE Plaats LIKE @Plaats

Dit heeft als nadeel dat het 'SELECT * FROM Adres' gedeelte meerdere keren voorkomt. In dit geval is het nogal simpel, maar in mijn werkelijke query heb ik 2 'SELECT' gedeeltes die elk 11 regels beslaan met een aantal joins etc., en heb ik ook nog eens 3 variabelen (en dus 8 cases). Dat is dus vrijwel niet te onderhouden.

Nou kan ik het ook zo doen:

code:

PROCEDURE spTest
@Plaats as varchar(50)
AS

DECLARE @Query as varchar(512)

SET @Query = 'SELECT * FROM Adres'

IF (@Plaats IS NOT NULL) AND (@Plaats <> '')
    SET @Query = @Query + ' WHERE Plaats LIKE ''' + @Plaats + ''''
    
EXEC (@Query)

Maar nu vraag ik me toch af... Een stored procedure wordt door SQL Server gecompiled en geoptimaliseerd. Het eerstgenoemde stukje code zal dus vrij snel uitgevoerd worden, het hoeft maar 1 keer gecompiled en geoptimaliseerd te worden. Bij het tweede stukje code zal de dynamische query echter elke keer opnieuw gecompiled worden bij het uitvoeren van de EXEC. Hoeveel gaat dat ten koste van de performance?

Ik begrijp dat het bij deze simpele queries niet zo heel veel uitmaakt, maar de werkelijk query beslaat gemiddeld zo'n 50 regels, met twee keer 9 joins en een UNION...

Het gaat wel om een zoekfunctie, dus het hoeft ook weer niet de lichtsnelheid te behalen.

edit:

Hmm, misschien is het afhankelijk van de database server. Ik gebruik MS SQL Server 2000.

edit:

Edit2:

Effe test op "(@Plaats IS NULL)" toegevoegd, want dat moet hetzelfde betekenen als wanneer "(@Plaats = '')".

[ Voor 16% gewijzigd door RetepV op 21-10-2003 14:26 ]

[Jaspertje]

je kan in Sp's ook ISNULL gebruiken.. misschien moet je daar eens naar kijken..

Ik ga even kijken of ik wel gelijk heb

Je kan ook case gebruiken: pseudo code:

code:

CASE WHEN ID <> 1 THEN y ELSE '' END as x,

[ Voor 59% gewijzigd door Jaspertje op 21-10-2003 12:16 ]

[EfBe]

Je kunt nulls gebruiken in je parameters. Dan kun je dingen doen als:

SQL:

CREATE PROCEDURE pr_Orders_SelectMultiWCustomerEmployeeShipper
    @sCustomerID nchar(5),
    @iEmployeeID int,
    @iShipperID int
AS
SELECT  *
FROM    Orders
WHERE   CustomerID = COALESCE(@sCustomerID, CustomerID)
    AND
    EmployeeID = COALESCE(@iEmployeeID, EmployeeID)
    AND
    ShipVia = COALESCE(@iShipperID, ShipVia)

De 3 parameters zijn in feite alle 3 optional. Wanneer je niet wilt filteren op een parameter, dan geef je NULL mee. Door COALESCE wordt dit automatisch verwerkt.

String concatenatie is in sql altijd erg traag. Het beste kun je dit op de client doen en dan een query samenstellen op basis van de zoekcriteria. Wel de values met parameters meegeven! Als je dan je query meerdere keren uitvoert wordt het execution plan wel gecached.

[whoami]

Het zal zeker wegen op de performance.

String concatenatie schijnt niet de sterkste kant te zijn van een DBMS, en daarnaast zal de optimizer ook iedere keer het optimale execution-plan moeten gaan bepalen.

Kan je je SQL string niet opbouwen in je client applicatie, en die dan zo laten uitvoeren (mbhv parametrized queries wel te verstaan)

EfBe: is COALESCE ook niet vertragend?

[ Voor 6% gewijzigd door whoami op 21-10-2003 12:16 ]

[RetepV]

Ja, kan natuurlijk ook in de client applicatie, maar het is dan wel weer mooi om alle ingewikkelder SQL dingen in stored procedures te hebben. Zo kun je on-site ook nog eens dingen veranderen als er bugs in mochten zitten.

Heh, die COALESCE functie kende ik trouwens nog niet, ziet er handig uit. Ja, je begrijpt wel dat SQL niet mijn expertise is .

[RetepV]

Jaspertje schreef op 21 October 2003 @ 12:12:
je kan in Sp's ook ISNULL gebruiken.. misschien moet je daar eens naar kijken..

Ja, ok, maar dat is beside the point. De vraag was eigenlijk in hoeverre een EXEC met een dynamische string de boel vertraagt.

Zelfs als ik ISNULL gebruik zal ik ook nog op <> '' moeten testen omdat een LIKE met een lege string nooit wat zal opleveren.

EfBe schreef op 21 oktober 2003 @ 12:14:
String concatenatie is in sql altijd erg traag. Het beste kun je dit op de client doen en dan een query samenstellen op basis van de zoekcriteria. Wel de values met parameters meegeven! Als je dan je query meerdere keren uitvoert wordt het execution plan wel gecached.

Hmm, waarom het uitroepteken bij 'values met parameters meegeven'? Wordt een query die ik in mijn client opbouw en meegeef ook gecached?

[ Voor 39% gewijzigd door RetepV op 21-10-2003 12:33 ]

[EfBe]

whoami schreef op 21 October 2003 @ 12:14:
EfBe: is COALESCE ook niet vertragend?

Tuurlijk. Maar het is sneller dan string concatenatie plus hij kan execution plans hergebruiken en dat is altijd te prefereren.

[EfBe]

RetepV schreef op 21 October 2003 @ 12:30:
Hmm, waarom het uitroepteken bij 'values met parameters meegeven'? Wordt een query die ik in mijn client opbouw en meegeef ook gecached?

Het uitroepteken stond er omdat veel mensen de values in de string meeconcatenaten en zodoende sql injection mogelijk maken Met parameters heb je daar geen last van.

Een query die met parameters werkt en is opgebouwd in de client wordt gecached in sqlserver.

[Verwijderd]

De ISNULL-functie heeft dezelfde functionaliteit als de COALESCE-functie. Met een verschil. ISNULL mag maar twee argumenten hebben. Het zorgt er wel voor dat de code een stuk gemakkelijker leesbaar is.

EfBe schreef op 21 October 2003 @ 12:14:
Je kunt nulls gebruiken in je parameters. Dan kun je dingen doen als:

SQL:

CREATE PROCEDURE pr_Orders_SelectMultiWCustomerEmployeeShipper
    @sCustomerID nchar(5),
    @iEmployeeID int,
    @iShipperID int
AS
SELECT  *
FROM    Orders
WHERE   CustomerID = COALESCE(@sCustomerID, CustomerID)
    AND
    EmployeeID = COALESCE(@iEmployeeID, EmployeeID)
    AND
    ShipVia = COALESCE(@iShipperID, ShipVia)

[P_de_B]

Klein probleempje hiermee is dat NULL !=NULL, dus als in het bovenstaande voorbeeld ShipVia NULL is, wen @iShipperId ook, wordt dit record niet teruggegeven.

Als je COALESCE (of ISNULL) gebruikt zal er ook geen gebruik van indexes gemaakt gaan worden, volgens mij zou je dan toch het beste in de client een string op kunnen bouwen, of niet?

[whoami]

Je kan natuurlijk altijd ook dit gebruiken voor die LIKE waarden:

code:

SET @naam = @naam + '%'

Zorg ervoor dat je geen wildcard teken meegeeft aan je procedure, maar dat je dat in je procedure erbij plakt. Als @naam dan leeg is, dan bevat het een %, waardoor die LIKE gewoon alles teruggeeft.
Je zou wel ff moeten testen hoe dat werkt als @naam == NULL

[EfBe]

P_de_B schreef op 21 October 2003 @ 13:05:
Klein probleempje hiermee is dat NULL !=NULL, dus als in het bovenstaande voorbeeld ShipVia NULL is, wen @iShipperId ook, wordt dit record niet teruggegeven.

Hmm, klopt inderdaad, omdat field = NULL altijd false is en field IS NULL wel getoetst wordt.

Op zich kun je de expressie omschrijven naar:
(ShipVia IS NULL OR ShipVia = @ShipperID)

dus de complete query wordt dan:

code:

CREATE PROCEDURE pr_Orders_SelectMultiWCustomerEmployeeShipper
    @sCustomerID nchar(5),
    @iEmployeeID int,
    @iShipperID int
AS
SELECT  *
FROM    Orders
WHERE   
(@sCustomerID IS NULL OR CustomerID = @sCustomerID) AND
(@iEmployeeID IS NULL OR EmployeeID = @iEmployeeID) AND
(@iShipperID IS NULL OR ShipVia = @iShipperID)

Als je COALESCE (of ISNULL) gebruikt zal er ook geen gebruik van indexes gemaakt gaan worden, volgens mij zou je dan toch het beste in de client een string op kunnen bouwen, of niet?

Ik zie nergens staan dat dit niet zou gebeuren.

edit:
bugfixes

[ Voor 3% gewijzigd door EfBe op 21-10-2003 14:20 ]

[RetepV]

EfBe schreef op 21 October 2003 @ 13:34:
Op zich kun je de expressie omschrijven naar:
(ShipVia IS NULL OR ShipVia = @ShipperID)

Nee, dat klopt niet . Je test hier of de kolom ShipVia NULL is, terwijl ik juist *alles* wil terugkeren als @ShipperID NULL is. Je moet dus testen of @ShipperID NULL is. Ik neem effe mijn (simpeler) voorbeeld weer:

code:

PROCEDURE spTest
@Plaats as varchar(50)
AS

SELECT * FROM Adres WHERE ((@Plaats IS NULL) OR (Plaats LIKE @Plaats))

Dit werkt blijkbaar prima, hoewel ik dacht van niet. Als @Plaats NULL is, krijg ik alles terug uit de Adres tabel. Als @Plaats niet NULL is krijg ik alleen de records die voldoen aan de LIKE.

De OR is een logische OR, en blijkbaar handelt SQL dat goed af. Als @Plaats NULL is, dan is het eerste deel van de OR al True en zal de WHERE clause dus als True worden geevalueerd.

Als @Plaats leeg is, komt er echter niks terug, terwijl in dit geval NULL en '' hetzelfde moeten betekenen. Dus het ozu zo moeten worden:

code:

PROCEDURE spTest
@Plaats as varchar(50)
AS

SELECT * FROM Adres WHERE ((@Plaats IS NULL) OR (@Plaats = '') OR (Plaats LIKE @Plaats))

Dit kan in feite alleen maar werken als de OR expressie NIET verder geevalueerd wordt nadat één van de onderdelen True is. SQL Server mag namelijk niet verder met de evaluatie nadat geconstateerd is dat @Plaats NULL is. Anders krijg je een foutmelding. Misschien nog niet op de "(@Plaats = '')", dat wordt dan "(NULL = '')", en die vergelijking mag prima. Maar "(Plaats LIKE NULL)" kan niet geevalueerd worden, dat levert een foutmelding op.

Maar SQL Server doet het goed, lijkt het vooralsnog. Ik vraag me af hoe portable dit is.

In sommige C++ compilers kun je kiezen hoe logische expressies geevalueerd moeten worden. Dwz. je kunt dan kiezen of altijd alle onderdelen van de logische expressie worden geevalueerd of dat de evaluatie gestopt wordt wanneer de uitkomst al zeker is. Bij een OR statement is de uitkomst al zeker als één van de onderdelen naar True evalueert. Dan is alleen nog de richting van evaluatie van belang (links naar rechts of rechts naar links), maar ik ben nog nooit een logische expressie evaluator tegengekomen die van rechts naar links ging evalueren (ja, in Arabische landen misschien ).

edit:

Edit:

Hmm, net even getest, maar SQL Server 2000 klaagt nergens over als ik de volgorde van de expressie verander. Dus blijkbaar mag "(Plaats LIKE NULL)" wel. Het lijkt me dan echter wel dat je een rare uitkomst zou krijgen. "(Plaats LIKE NULL)" zal wel hetzelfde betekenen als "(Plaats IS NULL)".

edit:

Edit2:

Je begrijpt al wel dat ik er nu wel zo'n beetje uit ben. Deze laatste vorm lijkt me prima te compilen en te cachen. Zo gauw de WHERE clause ingewikkelder wordt, zal de code natuurlijk wel weer slechter leesbaar worden. En je moet al iets gevorderd zijn met logische expressies. Ik moet er nog eens goed over nadenken . In dit geval hoeft het al niet supersnel te zijn. In feite is een hercompilatie een vaste extra tijd bij elke query. De query wordt niet langzamer naarmate er meer data in de tabel komt.

[ Voor 24% gewijzigd door RetepV op 21-10-2003 14:15 ]

[EfBe]

RetepV schreef op 21 October 2003 @ 14:07:
[...]

Nee, dat klopt niet . Je test hier of de kolom ShipVia NULL is, terwijl ik juist *alles* wil terugkeren als @ShipperID NULL is. Je moet dus testen of @ShipperID NULL is. Ik neem effe mijn (simpeler) voorbeeld weer:

Oh darn inderdaad. Ik hack mn example zo ff goed. (was uit blote hoofd getikt )

code:

PROCEDURE spTest
@Plaats as varchar(50)
AS

SELECT * FROM Adres WHERE ((@Plaats IS NULL) OR (Plaats LIKE @Plaats))

Dit werkt blijkbaar prima, hoewel ik dacht van niet. Als @Plaats NULL is, krijg ik alles terug uit de Adres tabel. Als @Plaats niet NULL is krijg ik alleen de records die voldoen aan de LIKE.

Precies, dat was ook de bedoeling

Je begrijpt al wel dat ik er nu wel zo'n beetje uit ben. Deze laatste vorm lijkt me prima te compilen en te cachen. Zo gauw de WHERE clause ingewikkelder wordt, zal de code natuurlijk wel weer slechter leesbaar worden. En je moet al iets gevorderd zijn met logische expressies. Ik moet er nog eens goed over nadenken . In dit geval hoeft het al niet supersnel te zijn. In feite is een hercompilatie een vaste extra tijd bij elke query. De query wordt niet langzamer naarmate er meer data in de tabel komt.
[/edit]

Search routines zijn nooit leesbaar qua code.

[RetepV]

Oeps, ik realiseer me toch nog iets...

code:

PROCEDURE spTest
@Plaats as varchar(50)
AS

SELECT * FROM Adres WHERE ((@Plaats IS NULL) OR (@Plaats = '') OR (Plaats LIKE @Plaats))

Kan dit wel goed gecompiled en gecached worden?

"(Plaats LIKE @Plaats)" is in feite een statische expressie met een variabele.

"(@Plaats = '')" kan omgeschreven worden naar "('' = @Plaats)" en dus zo ook weer een statische expressie met een variabele wordt.

Maar ik vraag me af of "(@Plaats IS NULL)" ook geschreven kan worden naar "(NULL IS @Plaats)". Betekent dat nog hetzelfde? Wat doet 'IS' eigenlijk precies anders dan '='?

Wat ik bedoel is of de laatste statement (met de IS NULL) wel gecompiled kan worden of telkens weer een hercompilatie initieert.

[ Voor 65% gewijzigd door RetepV op 21-10-2003 14:42 ]

[P_de_B]

dit zou moeten werken:

WHERE ISNULL(Plaats,'plaats is null') = COALESCE(@Plaats,Plaats,'plaats is null')

[RetepV]

P_de_B schreef op 21 October 2003 @ 14:26:
dit zou moeten werken:

code:

PROCEDURE spTest
@Plaats as varchar(50)
AS

SELECT * FROM Adres WHERE ISNULL(Plaats,'plaats is null') = COALESCE(@Plaats,Plaats,'plaats is null')

Hmm, ziet er ook goed uit. Enig idee wat dit voor performance met zich mee brengt? Deze code lijkt me wel een stukje slechter te optimaliseren dan de code die ik zelf bedacht had .

Leesbaarder is het zeker, hoewel ik niet zo'n lange string ('plaats is null') had genomen. Die moet toch helemaal tot het laatste karakter gecompared worden. Maar wat je er dan wel voor invult mag natuurlijk niet in je tabel terecht komen.

edit:

Nee w8, het klopt toch niet.

Dit houdt geen rekening met het feit dat als @Plaats leeg is, hij hetzelfde behandeld moet worden als wanneer @Plaats NULL is. En dan moet daar weer een extra test bij komen (kan in feite eenmaal aan het begin IF @Plaats = ''....). Dan is de versie met de lang OR netter. En waarschijnlijk sneller omdat in het geval van dat alles NULL is, niet de vergelijking "('plaats is null' = 'plaats is null')" uitgevoerd hoeft te worden.

Het wordt dan zo:

code:

PROCEDURE spTest
@Plaats as varchar(50)
AS

SELECT * FROM Adres WHERE ((@Plaats = '') OR (ISNULL(Plaats,-1) = COALESCE(@Plaats,Plaats,-1)))

Ik ben er vergeten bij te zeggen dat de plaatsnaam nooit NULL of leeg zal zijn.

[ Voor 54% gewijzigd door RetepV op 21-10-2003 15:02 ]

[P_de_B]

Performance zal waarschijnlijk slechter zijn dan een aan de clientside opgebouwde dynamische query.

De string is natuurlijk illustratief, en kan inderdaad beter vervangen worden ( -1 ?? )

[RetepV]

Beetje late reactie . Ik heb eindelijk uitgevonden dat ik topics kan bookmarken! Snel hé, voor iemand die pretendeert intelligent te zijn .

P_de_B schreef op 21 oktober 2003 @ 14:53:
Performance zal waarschijnlijk slechter zijn dan een aan de clientside opgebouwde dynamische query.

Dat vraag ik me af. Een aan de client side opgebouwde dynamische query zal niet gecached worden (geen enkele dynamische query wordt gecached/geoptimaliseerd). Daarbij moet de query telkens opgestuurd worden naar de SQL engine. Met de hier gebruikte voorbeelden is dat niet zo erg, maar de werkelijke query is nogal wat groter .

Wat ik dus moet zien te bereiken is een stored procedure met een statische query waarbij de gegeven parameters wel of niet meedoen afhankelijk van een paar stuurvariabelen.

Het zou overigens leuk zijn als er nog iemand was die mijn verhaal zou kunnen bevestigen .

[ Voor 7% gewijzigd door RetepV op 29-10-2003 15:30 ]

[whoami]

RetepV schreef op 29 October 2003 @ 15:28:

Dat vraag ik me af. Een aan de client side opgebouwde dynamische query zal niet gecached worden (geen enkele dynamische query wordt gecached/geoptimaliseerd).

Waarom zou die query niet gecached worden door de DB als je parametrized queries gebruikt?
De query zelf die doorgestuurd wordt is altijd dezelfde, dus die kan perfect gecached worden. Het enige dat veranderd is de waarde van de parameters, maar die zitten niet in de SQL query zelf gebakken.

Het zou overigens leuk zijn als er nog iemand was die mijn verhaal zou kunnen bevestigen .

Ik geloof dat EfBe ooit eens een benchmark gedaan heeft....
Als ik het me nog goed herinner zou de conclusie zijn dat parametrized queries die door de client opgebouwd werden wel degelijk gecached werden.

[ Voor 14% gewijzigd door whoami op 29-10-2003 15:37 ]

[P_de_B]

Ja hoor, ook van clientside opgebouwde queries wordt het executionplan bewaard (vanaf SQL2K)

Ik had een link naar het MSDN artikel in mijn favorieten maar die is helaas weg.

[EfBe]

RetepV schreef op 29 October 2003 @ 15:28:
Beetje late reactie . Ik heb eindelijk uitgevonden dat ik topics kan bookmarken! Snel hé, voor iemand die pretendeert intelligent te zijn .

Jij kraakt microsoft af, dus dat predicaat intelligent is dan bij voorbaat onhaalbaar geworden

[...]
Dat vraag ik me af. Een aan de client side opgebouwde dynamische query zal niet gecached worden (geen enkele dynamische query wordt gecached/geoptimaliseerd). Daarbij moet de query telkens opgestuurd worden naar de SQL engine. Met de hier gebruikte voorbeelden is dat niet zo erg, maar de werkelijke query is nogal wat groter .

Onzin. Er is in sqlserver geen verschil tussen een stored proc en een dyn. parametrized query. Het enige verschil is inderdaad wat je zegt, dat je de dynamische moet oversturen. Echter, die kun je dan OOK op maat maken, en dat is in erg veel gevallen voordeliger (bv een update query: een stored procedure die een table update zal t.a.t. alle fields moeten ontvangen, of veel logica moeten bevatten (wat in veel gevallen recompilation oplevert). )

Voor query execution plans verwijs ik je graag naar de BOL.:
From BOL (SQL Stored Procedures page):

"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. The database engine uses an efficient algorithm for comparing new Transact-SQL statements with the Transact-SQL statements of existing execution plans. If the database engine determines that a new Transact-SQL statement matches the Transact-SQL statement of an existing execution plan, it reuses the plan. This reduces the relative performance benefit of precompiling stored procedures by extending execution plan reuse to all SQL statements."

Dus al sinds 7.0 zit dit er in.
Zoeken naar execution plan in de BOL levert al genoeg pages op btw

Wat ik dus moet zien te bereiken is een stored procedure met een statische query waarbij de gegeven parameters wel of niet meedoen afhankelijk van een paar stuurvariabelen.
Het zou overigens leuk zijn als er nog iemand was die mijn verhaal zou kunnen bevestigen .

Hahaha Nou vooral dat laatste kan ik met bewijs ontkrachten
http://weblogs.asp.net/fbouma/posts/7008.aspx
en:
http://weblogs.asp.net/fbouma/story/7049.aspx

Het is echt schokkend hoe traag de SP is. Dit komt voornl. door COALESCE, maar die was nog sneller dan de andere constructies die je kunt gebruiken daarvoor (al besproken hier).

Ik denk dat je nu wel genoeg feiten hebt voor een beslissing

[EfBe]

P_de_B schreef op 29 oktober 2003 @ 16:00:
Ja hoor, ook van clientside opgebouwde queries wordt het executionplan bewaard (vanaf SQL2K)

Vanaf 7.0 al . SqlServer parametrized zelfs queries zonder parameters om dit te bereiken.

Ik had een link naar het MSDN artikel in mijn favorieten maar die is helaas weg.

BOL en op execution plan cache zoeken is genoeg.

[P_de_B]

EfBe schreef op 29 October 2003 @ 16:28:
[...]

Vanaf 7.0 al . SqlServer parametrized zelfs queries zonder parameters om dit te bereiken.


[...]

BOL en op execution plan cache zoeken is genoeg.

offtopic:
zeg, moet jij niet druk aan het programmeren ipv mij hier wat te verbeteren

[EfBe]

P_de_B schreef op 29 October 2003 @ 16:43:
[...]

offtopic:
zeg, moet jij niet druk aan het programmeren ipv mij hier wat te verbeteren

offtopic:
heh sorry Peter, morgen en overmorgen ben je weer aan de beurt

[RetepV]

EfBe schreef op 29 oktober 2003 @ 16:27:
Jij kraakt microsoft af, dus dat predicaat intelligent is dan bij voorbaat onhaalbaar geworden

Hahaha, het ligt er maar aan HOE je Microsoft afkraakt. Ik doe het op een arrogante manier die impliciet aangeeft dat ik het beter weet. Dat is dan toch prima?

http://weblogs.asp.net/fbouma/posts/7008.aspx
en:
http://weblogs.asp.net/fbouma/story/7049.aspx

Nou zal ik het vanmiddag nog eens zelf proberen, maar als je zin hebt moet je deze ook eens benchmarken:

code:

CREATE PROCEDURE pr_Orders_SelectMultiWCustomerEmployeeShipper
    @sCustomerID nchar(5),
    @iEmployeeID int,
    @iShipperID int
AS
SELECT  *
FROM    Orders
                ((@sCustomerID = NULL) OR (CustomerID = @sCustomerID)) AND
                ((@iEmployeeID = NULL) OR (EmployeeID = @iEmployeeID)) AND
                ((@iShipperID = NULL) OR (ShipVia = @iShipperID))

En nog een stukje uitgebreider, er van uit gaande dat een string leeg kan zijn en dat dat dan hetzelfde moet betekenen als dat de string NULL is:

code:

CREATE PROCEDURE pr_Orders_SelectMultiWCustomerEmployeeShipper
    @sCustomerID nchar(5),
    @iEmployeeID int,
    @iShipperID int
AS
SELECT  *
FROM    Orders
                ((@sCustomerID = NULL) OR (@sCustomerID = '') OR (CustomerID = @sCustomerID)) AND
                ((@iEmployeeID = NULL) OR (EmployeeID = @iEmployeeID)) AND
                ((@iShipperID = NULL) OR (ShipVia = @iShipperID))

Kijk, COALESCE() is een functie, ik kan me voorstellen dat daar een redelijke hoeveelheid overhead bij komt kijken. Bovenstaand bestaat echter uit een paar onveranderlijke logische vergelijkingen, en je mag wel verwachten dat de SQL engine op dat punt toch wel geoptimaliseerd is.

Mocht jij eerder gebenchmarked hebben dan ik, dan hoor ik het wel .

Edit:

Wat freudiaanse vertypingen verbeterd .

[EfBe]

Die versies zijn even traag zoniet trager dan de COALESCE functie. Reden hiervoor is (net als bij de COALESCE functie) dat hij 3 keer een filter moet uitvoeren, no matter what, zie de execution plans.