I am sure there are many ways to handle this. Here is what I have done, and it has worked well.
In the StoredProc:
Each of the arguments has a default value, like Null or "" or 0 etc. Then in the where clause, I check to see if an argument has default value before using in the expression like:
Where ((@ID is Null) or (Person_Id = @ID))
In DataPortal_Fetch:
I check each optional parameter for a default value before calling AddWithValue() for that parameter. This assures that in the SP only the arguments I send will be evaluated against.
In Criteria Class:
Each instance variable is assigned a Default Value which I check against in the DataPortal_Fetch. If no value is assigned in the Criteria Constructor, the default will be automatically sent.
In the Shared GetList method(s):
I create either Overloads or use Optional Arguments to send exactly what the criteria need.
Jav
Just stumbled across this old(ish) post, thought i'd add something for completeness.
Something like the above SQL would be non-SARGable [on SQL2K at least] I played around with the following and the second SQL took 600 times longer [although they both returned data within a second..! but if you scale up..?!?!]
declare
@id intThe table here has is quite small only abut 300K records and shiftid is the Clustered primary key.
The first SQL performs a SEEK the second a SCAN...!
This could be an important consideration when chosing a solution,
Regards,
Graeme.
Hey Dog Ears, thanks for that post. I didn't realize that the SQL optimizer will never bother looking for an index to use with isnull() or coalesce().
I did know that you also have a similar potential performance problem creating a stored procedure that just creates a dynamic ad hoc query string and executes it, since SQL Server can't cache an execution plan for the sproc.
This is all part of why I've never really bought into the idea of jumping through all these hoops to be able to mandate the use of sprocs for all reading of data, but that's just my opinion.
BTW, does SQL 2005 have any functionality whereby if you change a column name it will tell you all the sprocs you just broke (or change them)?
Hello....
About the performance issue...
You should never use a function or calculate with a field from a table. This will void all indexes and every record has to be evaluated so this will result in an index or table scan like already posted.
Example (Showing last weeks orders):
... Where orderdate + 7 > getdate (No index can be used. The calculation needs to be done for every record)
... Where orderdate > getdate -7 (Index can be used.Calculation only needs to be done once)
Or how to solve an "optional" Filter field:
where (myParam is null OR myfield = myParam)
I also wanted to search some info on www.searchcsla.com site, but it doesn't have a search feature (or i missed it somehow), instead on the main page it says
This site is a tool --- use it in conjunction with your telephone conference calls.
How do I do search on this site (I know it sounds a pretty strange question)? Thanks.
Copyright (c) Marimer LLC