Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Search query w/o dynamic sql

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.SearchDescription
FROM Content AS c
WHERE (
@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 c
LEFT OUTER JOIN
(SELECT * FROM dbo.fn_Split(@sContentTypeIDs, ',')) D
ON c.ContentTypeID = D.Value
LEFT OUTER JOIN
(SELECT .... where gradelevelId = @gradelevelid) E
ON c.content = E.content
WHERE (
@sContentTypeIDs IS NULL
OR
D.Value is Not Null)
AND
(
@iGradeLevel IS NULL
OR
E.content is Not Null
)



- Jeff
Go to Top of Page

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-01-08 : 09:37:47
CREATE PROCEDURE spSearch

@param1 int = null,
@param2 int = null

AS

SELECT * FROM theTABLE
WHERE 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.


Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page

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 table

Why wouldn't it use an index?

Go to Top of Page

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.
Go to Top of Page

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2003-01-09 : 01:54:40
of course you're right

I 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.

Go to Top of Page
   

- Advertisement -