Author |
Topic |
AskSQLTeam
Ask SQLTeam Question
0 Posts |
Posted - 2003-01-08 : 08:53:16
|
Keith writes "I have a sproc that has several parameters to it and if more than one are passed into the sproc, the results much match ALL criteria. I am trying to write this query without dynamic sql. My problem is performance once I include all the parameters. My way to avoid eliminating all results when not all parameters were passed into the function was to include a way for each specific parameter where clause section a "way out" that would evaluate to TRUE. The problem is that with several parameters the performace begins to crawl. When I do a simple select with the ..IS NULL OR ContentID IN () and compare its cost to the same query without the IS NULL part it is MUCH MUCH slower. I am not sure if this question is very clear but I have read about every post on google groups and found nothing similar to my problem.I thought that since CASE quits after the first true evaluation that it would be my fix but I cannot seem to get the syntax correct. Use the following query as a shortened sample.SELECT c.ContentID, c.ContentName, c.SearchDescriptionFROM Content AS cWHERE ( @sContentTypeIDs IS NULL OR c.ContentTypeID IN ( SELECT value FROM dbo.fn_Split(@sContentTypeIDs, ',') ) ) AND ( @iGradeLevel IS NULL OR c.ContentID IN (.....where gl.GradeLevelID = @iGradeLevelID))ALL HELPFUL SUGGESTIONS WOULD BE GREATLY APPRECIATED!!!" |
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-08 : 09:34:36
|
Maybe try something like:SELECT c.ContentID, c.ContentName, c.SearchDescription FROM Content AS cLEFT OUTER JOIN (SELECT * FROM dbo.fn_Split(@sContentTypeIDs, ',')) DON c.ContentTypeID = D.ValueLEFT OUTER JOIN(SELECT .... where gradelevelId = @gradelevelid) EON c.content = E.contentWHERE ( @sContentTypeIDs IS NULL OR D.Value is Not Null)AND(@iGradeLevel IS NULL OR E.content is Not Null) - Jeff |
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2003-01-08 : 09:37:47
|
CREATE PROCEDURE spSearch@param1 int = null,@param2 int = nullASSELECT * FROM theTABLEWHERE ISNULL(field1,0) = COALESCE(@param1,field1,0)AND ISNULL(field2,0) = COALESCE(@param2,field2,0)if a parameter is not passed to the sproc, it gets default to a NULL value. If the param equals NULL the column is compared to itself, therefor it returns all records. |
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2003-01-08 : 10:34:35
|
Couldnt you try this as well?CREATE PROCEDURE spSearch @param1 int = null, @param2 int = null AS SELECT * FROM theTABLE WHERE (field1 = @param1 OR @param1 IS NULL)AND (field2 = @param2 OR @param2 IS NULL) Got SQL? |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-01-08 : 10:41:52
|
Note that 1 of the parameters is actually a list of values, that is converted into a table.Peter -- the only problem with your technique is that it can't make use of any indexes; the original problem was that his query works, but very slowly I believe.- Jeff |
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2003-01-08 : 10:46:32
|
Jeff,are you sure?Well I wasn't so I did a little test, and the execution plan DOES show the use of the (clustered) index on the tableWhy wouldn't it use an index? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-08 : 11:18:12
|
It's using a function on the field so probably won't be able to use the index.Are you sure it isn't scanning the index?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Peter Dutch
Posting Yak Master
127 Posts |
Posted - 2003-01-09 : 01:54:40
|
of course you're rightI have a similar function, like this:WHERE field1= ISNULL(@param1,field1)there is a NOT NULL contraint on 'field1' so I don't have to use the ISNULL function on the field. (This was the statement I tested.)In this case I didn't know if there was a NOT NULL contraint on the field, so I used ISNULL on the field.So, sorry Jeff. You were absolutely right. |
|
|
|