Posted by Erland Sommarskog on 06/06/06 22:04

John (johnxhc@yahoo.com) writes:
> (Using SQL2005) I need to design the generic search utility on the
> database server (run as Web Service), client supply the search criteria
> (where clause)

The WHERE clause is not coming as a parameter, I hope?

> 2) Substitute the where clause with subquery . for example, if the
> where clause is following:
>
> where subtable1.name ='john' or subtable2.product_code ='xyz'
> or subtable1.name ='tom
>
> I will replace it with the following : (please trust me I have the code
> to do the Substitution but it will be too hard to explain here)
>
> Select distinct maintable.id where
> exists (select * from subttable1 where name = 'john' and
> maintable.id=subtable1.id)
> or
> exists (select * from subttable2 where product_code = 'xyz' and
> maintable.id=subtable2.id)
> or
> exists (select * from subttable1 where name = 'tom' and
> maintable.id=subtable1.id)
> order by ...

So, the rule is that if any of the criterias match, the row should
be returned. Often dynamic searches are the other way round: all
should match.

One thing you could try is:

Select maintable.id
where exists (select * from subttable1 where name = 'john' and
maintable.id=subtable1.id)
union
Select maintable.id
exists (select * from subttable2 where product_code = 'xyz' and
maintable.id=subtable2.id)
union
Select maintable.id
exists (select * from subttable1 where name = 'tom' and
maintable.id=subtable1.id)

If the query processor chokes, you could insert ids into temp tables
as you handle the tables, one by one. (Or two by two or whatever.)

> the problem with that is the query is getting slower & slower when user
> specify more search criteria,

If more criterias means more data to search and return that may be
inevitable.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Challenge: Can you optimize this?
• Prev in forum: Re: SQL Union Problems When Trying to Retrieve Random Records
• Thread view: Re: Tough SQL problem, need expert advice!!!

[Reply to this message]