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

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Optimising search on multiple fields

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2000-12-06 : 15:42:50
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

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)<>''
select * from Search where Column4 like '%' + @Field4 + '%'

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


- Advertisement -