September 21, 2005 at 8:23 am
Hi All,
I have an f_name column in one table . I have to match this column against f_name ,m_name,l_name. The where clause comes to f_name = f_name or f_name = m_name or f_name = l_name . this was taking a lot of time. I changed it to
select a.* from table a, table b where a.f_name = b.f_name union all select a.* from table a, table b where a.f_name = b.m_name and so on... In this way the time is reduced to half. If u can rewrite the query in a more optimal way... then pls do tell me.......
Also where r u from ? I am from New Delhi , India. Nice interacting with u. Thanx once again.
Rgds,
Rajesh
September 21, 2005 at 10:32 am
Rajesh,
when you execute this in Query Analyzer, use CTRL-K first and get the execution plan. Then you can compare the two methods. I think the OR is less efficient in general than the unions, but you'd have to test it.
Also, I'd use the ANSI syntax
select a.*
from a
inner join b
on a.f_name = b.f_name
I'm in CO, USA
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 22, 2005 at 5:02 am
Hi,
I agree with Steve - join them up like
select a.*
from a
inner join b
on (a.f_name = b.f_name) or (a.f_name = b.m_name)
Then if you are only interested to find out if they do exists rather than returning their content, then use IF EXISTS:
IF EXISTS(select a.*
from a
inner join b
on (a.f_name = b.f_name) or (a.f_name = b.m_name))
SELECT 'Yes'
ELSE
SELECT 'No'
Cape Town, South Africa
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply