March 13, 2003 at 10:25 am
Hey all,
I have a sproc that allows users to query a table using any combo of optional parameters.
Based on the pubs.authors table it looks something like this:
CREATE PROC search_authors
@fname varchar(255) = '%',
@lname varchar(255) = '%'
AS
-- code to make sure @fname and @lname
-- always have trailing wildcard '%' goes here
SELECT fname, lname FROM authors
WHERE fname LIKE @fname AND lname LIKE @lname
This means the user can pass in no parameters and get the whole result set back, pass in 'a' to @fname and get back all authors whose first name starts with 'a', or use the last name, or ... hopefully you get the picture. (This serves as a "search engine" for a web-based directory)
Since my sproc actually has 8 possible input variables (as well as a few application flags that further complicate the mess), the approach shown above is very simple to write and maintain.
I'm concerned that I might be taking a performance hit over other methodologies such as:
A. Have one sproc for each possible combo (64 sprocs??!!) and have the application logic choose the appropriate one (seems like a terrible approach)
B. Have the sproc build a dynamic SQL statement based on the parameters actually passed in by the user (i.e. eliminate the lname from the WHERE clause if there is nothing passed in @lname), and then execute this via sp_executesql. I've used this approach many times in the past but it seems overly complex to maintain, and nothing I've read or seen has convinced me (yet) that this is more efficient than a string of WHERE x LIKE @x ...
Any recommendations, thoughts?
Thanks,
Jason Langston
JasonL
March 13, 2003 at 4:18 pm
I think the answer to your question is directly related to the exclusivity of indexes that are, or can be, applied to the table. A covering index that will quickly reduce the record set would produce the best results. Separate indexes can be utilized by 2000 to achieve the same goal. However, the exclusivity of the indexes will play a major factor in the efficacy of stored procedure. Adding indexes can be tricky business, as they will slow Inserts, Updates, and Deletes on the table. If the information in the table does not change often then adding indexes will dramatically increase the speed. If the data changes quit often then add indexes sparingly to fields few repeated values and try to enforce a value for these fields in your search form where possible. Once you have added or changed the indexes I don’t think you will find much difference speed between separate stored procedures or combined into one.
March 13, 2003 at 4:18 pm
I usually do somethign like this, if i want them to be able to pass in an optional parameter to a query :
CREATE PROC search_authors(@fname varchar(255) = NULL,
@lname varchar(255) = NULL)
AS
SELECT fname, lname FROM authors
WHERE (@fname IS NULL OR fname LIKE @fname + '%')
AND (@lname IS NULL OR lname LIKE @lname + '%')
Because of short circuiting I believe this would prove faster when selecting all the records when they wish to wild card that field.
Tim C.
//Will write code for food
Tim C //Will code for food
March 13, 2003 at 4:26 pm
I often use boolean logic and short circuirting to write one proc so that I RARELY, and I mean RARELY ever write dynamic sql. I also make use of this UDF I wrote to help me achieve that as well : http://www.sqlservercentral.com/scripts/contributions/632.asp
I have a combo that I wrote that diplays all the equality operators in readable format, and as an underlying value has the value for the operator that gets sent for use by this UDF. I then take the operator and the value and send those two into the stored procedure and he uses them in in his where clause.
Tim C.
//Will write code for food
Tim C //Will code for food
March 13, 2003 at 4:30 pm
I anyone wishes email me, and I will email them that combo. It alos will construct a normal where clause for you.
Tim C.
//Will write code for food
Tim C //Will code for food
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply