Greetings experts,

I have an sql search that requires 'OR' search on two different _fields_
eg. (Sun -- Sybase databases)
select f1, f2, ..., fn
from tableA, tableB
where (tableA.fld_1 = 'abc' OR tableA.fld_2 = 'abc')
and tableA.fld_i = tableB.fld_j

Without OR, it takes 1 sec to get data from tableA with 500,000 records.
With OR, it takes almost 15 - 20 seconds even though both of the search
fields are indexed.

Is there a way to speed up this search?
---------------
Another question... what is the difference between WHERE and HAVING?
select ... from ... where ...
select ... from ... having ...
They're both used for searching, is there any need to use one or the other?

Thank you for your help.
P.S. I would appreciate if you CC your reply to my email: rlyudmirsky@abac.com
Thanks.
-----------------------------------------------------------------------------
Rostislav "Steve" Lyudmirsky - rlyudmirsky@abac.com - http://rvl.netfirms.com
-----------------------------------------------------------------------------
"The Universe... is a pretty big place. It's bigger than anything, anyone has
ever dreamed of before. So, if it's just us... it seems like an awful waste
of space." -- Ellie Arroway (Contact)

The classic trick is to split such a query into two parts and connect
these in a union:

select f1, f2, ..., fn from tableA, tableB
where tableA.fld_1 = 'abc' and tableA.fld_i = tableB.fld_j
union
select f1, f2, ..., fn from tableA, tableB
where tableA.fld_2 = 'abc' and tableA.fld_i = tableB.fld_j

Each of these queries will be optimised separately. The assumption is
that each individual query will be fast, but due to the OR, a less
efficient query plan is chosen (often a table scan). When splitting it
into two queries as above, we'd therefore hope to see improved
performance.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

Author of "Tips, Tricks & Recipes for Sybase ASE" and
"The Complete Sybase ASE Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop

mailto:rob@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

"Rostislav Lyudmirsky" <rlyudmirsky@abac.com> wrote in message
news:69fb089d.0312220813.31856693@posting.google.c om...
> Greetings experts,
>
> I have an sql search that requires 'OR' search on two different
_fields_
> eg. (Sun -- Sybase databases)
> select f1, f2, ..., fn
> from tableA, tableB
> where (tableA.fld_1 = 'abc' OR tableA.fld_2 = 'abc')
> and tableA.fld_i = tableB.fld_j
>
> Without OR, it takes 1 sec to get data from tableA with 500,000
records.
> With OR, it takes almost 15 - 20 seconds even though both of the
search
> fields are indexed.
>
> Is there a way to speed up this search?
> ---------------
> Another question... what is the difference between WHERE and HAVING?
> select ... from ... where ...
> select ... from ... having ...
> They're both used for searching, is there any need to use one or the
other?
>
> Thank you for your help.
> P.S. I would appreciate if you CC your reply to my email:
rlyudmirsky@abac.com
> Thanks.
> --------------------------------------------------------------------
---------
> Rostislav "Steve" Lyudmirsky - rlyudmirsky@abac.com -
http://rvl.netfirms.com
> --------------------------------------------------------------------
---------
> "The Universe... is a pretty big place. It's bigger than anything,
anyone has
> ever dreamed of before. So, if it's just us... it seems like an
awful waste
> of space." -- Ellie Arroway (Contact)

"Rob Verschoor" <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote
in news:3fe72774$0$64977$1b62eedf@news.euronet.nl:

> select f1, f2, ..., fn from tableA, tableB
> where tableA.fld_1 = 'abc' and tableA.fld_i = tableB.fld_j
> union
> select f1, f2, ..., fn from tableA, tableB
> where tableA.fld_2 = 'abc' and tableA.fld_i = tableB.fld_j

Just to add to Rob's point, read up on the difference between UNION
and UNION ALL. If you can avoid the de-duplication phase, you're that
much further along.

Thx!
--
Pablo Sanchez - Blueoak Database Engineering, Inc
http://www.blueoakdb.com