Re: Speed Difference

If you want to know which is faster in your environment start isql,
enter SET STATS ON, then run each query twice and compare the times.

-- 

Hello Again,

I'm curious as to the speed of the UNION statement.  I have a CONTACTS 
table, which contains phone number prefix fields: HPHONEPREFIX, 
MPHONEPREFIX, (and others, but we'll omit those for brevity).  These fields 
are indexed.

Comparing these two statements:  Say I want all people who have any phone 
number with '573' prefix.

select CONTACTID from CONTACTS where (HPHONEPREFIX = '573') OR (MPHONEPREFIX 
= '573')

As compared to this:
select CONTACTID from CONTACTS where HPHONEPREFIX = '573'
UNION
select CONTACTID from CONTACTS where MPHONEPREFIX = '573'

Will there be extra overhead using UNION?

Thanks,
- Jason 

"Jason Summers" <jason@grinc.org> wrote in message 
news:472bd113$1@newsgroups.borland.com...
>
> As compared to this:
> select CONTACTID from CONTACTS where HPHONEPREFIX = '573'
> UNION
> select CONTACTID from CONTACTS where MPHONEPREFIX = '573'


You would probably want to use UNION ALL to avoid duplicates.

-- 
Wayne Niddery - Winwright, Inc. (www.winwright.ca) 

Wayne Niddery (TeamB) wrote:

> You would probably want to use UNION ALL to avoid duplicates.

	UNION ALL will return duplicates. UNION eliminates them. 

-- 
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Useful articles about InterBase development:

The UNION should be faster because the OR clause in the first statement will 
prevent the use of an index on prefix.

Dan

"Jason Summers" <jason@grinc.org> wrote in message 
news:472bd113$1@newsgroups.borland.com...
> Hello Again,
>
> I'm curious as to the speed of the UNION statement.  I have a CONTACTS 
> table, which contains phone number prefix fields: HPHONEPREFIX, 
> MPHONEPREFIX, (and others, but we'll omit those for brevity).  These 
> fields are indexed.
>
> Comparing these two statements:  Say I want all people who have any phone 
> number with '573' prefix.
>
> select CONTACTID from CONTACTS where (HPHONEPREFIX = '573') OR 
> (MPHONEPREFIX = '573')
>
> As compared to this:
> select CONTACTID from CONTACTS where HPHONEPREFIX = '573'
> UNION
> select CONTACTID from CONTACTS where MPHONEPREFIX = '573'
>
> Will there be extra overhead using UNION?
>
> Thanks,
> - Jason