|
|
Posted by jsfromynr on 06/09/06 06:18
Hi There,
I think you can try something like this .
Select * from tableA A
Where Exists
(
Select 1 from TableB B where B.coulmn1=A.coulmn1
Union All
Select 1 from TableB B where B.coulmn2=A.coulmn2
Union All
Select 1 from TableB B where B.coulmn3=A.coulmn3
and so on........
)
Using OR in where condition is causing it to FULL SCAN the table.
also create index on columns used for comparing.
With Warm regards
Jatinder Singh
> Unfortunately I did not write the parser, it is a generic component,
> passing me the following information
> 1) Field
> 2) Operation
> 3) Value
> I could replace all of the value above
> but the parser does not passing me the logic operator, even if it
> does, it is going to be very hard to for me to do a union because I
> could get very complicated logical operation and I could end up query
> the same table multiple time for different fields. Such as
> (Lastname='johnson' or firstname='smith') and (city='Los Angeles')
> or (productid in (234,23434,33) and (Lastname='Smith' or
> firstname='tom'))
>
> also for "and" operation, I will need to do a intersection instead
> of union. I do not believe SQLServer provide this function on
> transaction sql.
>
> Erland Sommarskog wrote:
> > John (johnxhc@yahoo.com) writes:
> > > Well, when I say I allow client to specify the where clause, that is a
> > > just simple way to put it, actually I publish a list of client is
> > > allowed to do search, which is different name than the database field
> > > name, I have a table to map the field name the client is using to real
> > > database field name ( or XML Query)
> > > when I get the where clause I do the parsing and convert the client
> > > supplied name into real database name (or XML Query expression), since
> > > the parse is doing all the checks, I am not worry about the SQL
> > > injection
> > >
> > > You may ask why I am going through so much trouble, the reason it I am
> > > writing a framework that needs to be extendable, which means I design
> > > the basic infrastructure, other group could design application sitting
> > > on top of the framework. For example, they could design their own
> > > database, tables, as long as they following the general design, they
> > > could reuse everything I am doing.
> >
> > Now, I understand lesser and lesser. If you are parsing the input, you
> > should be able to break up in the input so you could try the union thing.
> >
> > Looks like a very good idea to do this in a CLR procedure, by the way.
> >
> > --
> > 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
[Back to original message]
|