Need help with search stored procedure… | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need help with search stored procedure…

Hello, I am coding a stored procedure that returns a result set from a couple of joined tables. The stored procedure accepts several parameters for a search criteria, and if any of this parameters is defined (ie, not null) then it filters the result set based on the parameter. The parameters are captured from a extensive search form on a web site. Since the query is fairly complex (over 50 criteria parameters), I would like to know if this is the best way to handle this, or if there are better ways. What I have has been working great for some time, but I am expecting a heavier load of users and want to make sure this is optimal. I have limited experience w/ SQL so any pointers and help you can provide will be appreciated. The one requirement is that if no parameters are defined, then it returns everything on the table. If one or more parameters are defined, then the result set is filtered. TIA,
george A *simplified* version of what I have:
CREATE PROCEDURE spGetList (
@Username varchar(16) = NULL,
@AreaCode smallint = NULL,
@PostalCode varchar(7) = NULL,
@Status varchar(10) = NULL,
@ID1 int = NULL,
@ID2 int = NULL,
@ID3 int = NULL,
— … lots more parms here …
@Email varchar(75) = NULL
) AS SELECT *
FROM tblResource
WHERE (Username = ISNULL(@Username,Username)) AND
(AreaCode = ISNULL(@AreaCode,AreaCode)) AND
(PostalCode = ISNULL(@PostalCode,PostalCode)) AND
(Status = ISNULL(@Status,Status)) AND
(ID1 = ISNULL(@ID1,ID1)) AND
(ID2 = ISNULL(@ID1,ID1)) AND
(ID3 = ISNULL(@ID1,ID1)) AND
— … lots more criteria here …
(@Email = ISNULL(@Email,Email)
Look… http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=708
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=624
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=999
http://sql-server-performance.com/forum/topic.asp?TOPIC_ID=953 HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
I would consider going through the extra work and writing a few IF blocks to filter at least columns that are indexed and most common in search. Even one indexed column can make a difference. For the rest of the condition in those if blocks you could stay with ISNULL/COALESCE or maybe (@var IS NULL) OR (field_name = @var). None of them will use an index seek, but if @var is evaluated to NULL it should (not garenteed) exit the condition and avoid a table/index scan.
As an else block or an alternative, I would consider dynamic sql with sp_executesql. Sometimes it’s the best approach in cases like this. just make sure you are familiar with security issues related to sp_executesql (search for sql injection). And take into consideration that you would have to give SELECT permission to all tables in your query.
Another thing I suggest you is to use SET QUERY_GOVERNOR_COST_LIMIT int_value at the beginning of your procedure. This means that if procedure is extimated to run longer than the indicated value, it will not run (and exit with an error). Bambola.
Bambola covered almost everything… One suggestion is to look at both the options suggested in other forums. Either of them can be best solution depending on conditions… Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

NOTE that this is a difference between isnull/coalesce and (@var is null or field = @var) The former two will never pick up records where the field is null, the latter one will. (unless some set parameters are changed) definitely write IF statements for the indexed cases (but still have the full query there, just for that one field or combination of fields have an equality clause) Also the procedure will not allow searching for records with a null in a given field, but presumably that is not a requirement or is not relevant in this case Cheers
Twan
> The former two will never pick up records where the field is null, the latter one will. (unless some set parameters are changed) I am not sure I understood that. As far as I can see they all return the same results. declare @orderid int
select @orderid = 10248
— select @orderid = null select count(*)
from orders
where orderid = ISNULL(@orderid, orderid) select count(*)
from orders
where orderid = COALESCE(@orderid, orderid) select count(*)
from orders
where (@orderid IS NULL) or (orderid = @orderid) what I can also see is that in the last statement the second part of the conditionis not evaluated when @orderid is null. Bambola.
Thanks for all the suggestions. After reading the other threads I think I am going to go with replacing the ISNULL() for the more SARGable ((@criteria IS NULL) OR (@field = @criteria). Just wondering… has anybody tested these two methods in a compiled stored procedure for performance? Any stats…? TIA,
george.
Jross has done some testing athttp://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=708 Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Hi Bambola, isnull and coalesce wont return records where the column name is null, since NULL=NULL is unknown unless ANSI_NULLS is off Cheers
Twan
I got you now, Twan. I was thinking about the variable and you were talking about the field value. Thanks for clearning that up. Bambola.
]]>