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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Optimising search on multiple fields
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 12/06/2000 :  15:42:50  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Kush writes "Hi Guys,

I have a search form, with 4 fields. All 4 are optional but atleast 1 is compulsory.

One option for the SQL query is to use LIKE keyword
i.e.

select * from Search where ColumnA like '%'+ @Field1 + '%'
and ColumnB like '%'+ @Field2 + '%'
and ColumnC like '%'+ @Field3 + '%'
and ColumnD like '%'+ @Field4 + '%'


I check the cost for this query and it came to 77%

I then made a query with nested IF's that checks which of the 4 fields are empty and omits them from the WHERE CLause.

e.g. if FIELD1,FIELD2,FIELD3 are empty but FIELD4 is not empty. Then the IF Statement for this would be

if select (@Field1)='' and select(@Field2)='' and select(@Field3)='' and select(@Field4)<>''
begin
select * from Search where Column4 like '%' + @Field4 + '%'
end


as u might have guessed, this solution has several IF's

so for my 4 variables, i have 16 IF's like the one on top.
writing these IF's is extremely complicated and tedious
but the cost for this query came out to be 33%.

Is that a substantial performance improvement.??

The prob with the 2nd solution is that as the number of variables keep on increasing, the number of IF's will keep on increasing.... if i have 5 variables, instead of 4, the number of IF's will increase from 16 to 32.

and this SQL SP is not very easy to modify in the future.
What i want to know is, is there a better solution to this.????
if so, please let me know


Kush"

jamman
Starting Member

USA
44 Posts

Posted - 12/06/2000 :  15:42:50  Show Profile  Visit jamman's Homepage  Reply with Quote
Check out this article: http://www.sqlteam.com/item.asp?ItemID=259

You can use one IF statement for each field to build your query string and then use the EXEC keyword to execute that string. The cost of that query should be minimal and you would only have to add one more IF statement for each field that is added later.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 12/06/2000 :  22:13:41  Show Profile  Visit robvolk's Homepage  Reply with Quote
This should work too:

SELECT * FROM SEARCH WHERE
ColumnA Like '%' + IsNull(@Field1,'%') + '%',
ColumnB Like '%' + IsNull(@Field2,'%') + '%',
ColumnC Like '%' + IsNull(@Field3,'%') + '%',
ColumnD Like '%' + IsNull(@Field4,'%') + '%'

Just add fields to the end as needed. If @Field1,2,3,4 etc. are empty strings ('') instead of NULL, use this instead:

SELECT * FROM SEARCH WHERE
ColumnA Like '%' + CASE @Field1 WHEN '' THEN '%' ELSE @Field1 END + '%',
ColumnB Like '%' + CASE @Field1 WHEN '' THEN '%' ELSE @Field2 END + '%',
ColumnC Like '%' + CASE @Field1 WHEN '' THEN '%' ELSE @Field3 END + '%',
ColumnD Like '%' + CASE @Field1 WHEN '' THEN '%' ELSE @Field4 END + '%'

You can use CASE for NULL values too. If you don't pass a @Field1 value, the expression becomes:

ColumnA Like '%%%' --always TRUE

I haven't tested it to see if it affects execution time. Another syntax that might work is:

SELECT * FROM SEARCH WHERE
ColumnA Like CASE @Field1 WHEN '' THEN ColumnA ELSE '%'+@Field1+'%' END,
ColumnB Like CASE @Field1 WHEN '' THEN ColumnB ELSE '%'+@Field2+'%' END,
ColumnC Like CASE @Field1 WHEN '' THEN ColumnC ELSE '%'+@Field3+'%' END,
ColumnD Like CASE @Field1 WHEN '' THEN ColumnD ELSE '%'+@Field4+'%' END

Maybe the optimizer will handle this better.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000