I’m working on my Code Generator so it will be able to generate stored procedures for me. Until now I generated the SQL queries in my C# code, but now, due to a development request(\demand) from my client I must work with stored procedures.
The first thing I thought about is how to make my search pages easy to upgrade and maintain if I’m going to use SP(stored procedure). Now, when I need to add another Field to my dynamic where clause, it’s quite simple – I’m building the query in my data ccess object according to the parameters and everything is OK. I feared that by using SP for my search pages, I’ll need to call something like EXEC which looks like a joke – If you must call EXEC in the SP, you better put the SQL in your C# code and get it over with.
After doing some searches, I found this article which present a way to build and execute dynamic where clauses with “COALESCE” function. After I’ve searched a little more, I found a similar way to do the same thing with better performance:
” I check the value against NULL and achieve good performance and flexibility. No problem with LIKE values either:
WHERE
(@title IS NULL OR title LIKE @title)
AND
(@min_release_date IS NULL OR release_date >= @min_release_date)
AND
(@document_id IS NULL or document_id = @document_id)
Great performance and flexibility! All indexes are used as expected. ” (Zelk)
Looks great !
If the sent parameter is null, don’t “cut” the results by the parameter, else – use it…
OK, I must dig into my generator and start implementing this…
Hey Oren,
I wouldn’t use anything like that. it remind sme too much of simmilier ‘%’ usage. Best thing (preformance vise) is using a SP for each and everycase.
if you have 3 possiable search parameters you can generate 8 SPs that do all the work for you.
most of the SPs (except one) will each handle one specific case of Parameters usage combination. And another SP will get all the parameters and decide with Specific SP to use.
In oracle i would have packaged all the related SPs togather.
Hey Justin-Josef,
Although you’ll probably right about the performance impact while using my suggested implementation,
you must concider the difficulties of maintaining or extending the code using yours.
I have a search screen with 7 fields to search by, It’s redundant to do the exact math – I’m not going to create so much SP’s for that screen.
Like always, We have to "trade-off" on what is more painfull;
I believe that a little performance impact (nothing that the user will feel) is tolerable in this scenrio, and the BIG profit is an easy maintenance and extendibility options.
Oren .. take a look at that :
http://www.sql-server-performance.com/pk_or_clause.asp
Though we use this OR convention for filters too … and I don’t think we’re gonna change that , unless some serious performance problems will be noticed when we reach huge amounts of data .
Hey Iago !
I’ve read you reference, thanks.
I still thinking about using the first method – It’s "cleaner", I don’t have to do so many "IF" for each case.
About performance, I don’t see a big difference, so I guess time will tell if I made the right choice.
Hey Oren,
I’m not an sequel DBA, but i bet you’ll see that the trade off you made doesn’t necesarilly pay off.
Generating a "SP search package" takes about 2 minutes. and is maintainable as any other data-driven generated code. You’ll have to see if it’s an aviable templete in myGeneration/CodeSmith for sequel.
Hey Justin-Josef,
I don’t like this way, regardless of the option to generate it and "forget" about it.
This is one of those things that each of us will have to do in it’s on way.
So we must agree to not agree ;-)