AW: SQL to only check for existence:

Hi,

>>mine had a LIKE '____xxxx___' compare in the subselect, which runs
>>much slower, especially if the first match is deep into the access
>>path it chooses.   I tested it - if the first match was towards the
>>beginning of the chosen access path, it found it sub-second.  near the
>>end, it was more like 6-10 secs.

The difference between using EXIST and 
Select 1 
Into :HstVar :IndHstVar
from MyTable 
where MyField like '_____XYZ%'
Fetch first row only

should be marginal.

In both cases the query execution gets stopped as soon as the first match
will be found.

The problem why the result gets returned almost immediatedly if the first
match is at the beginning of the table and the long wait time if the first
match is located towards the end of the table, is the optimizer cannot use
any existing index (access path). Instead a table scan will be performed and
there is no work around, except you have additional where clauses to reduce
the number of selected rows and the optimizer can use an index for these
clauses.

A table scan is even worth, if your table/physical file consists of a huge
number of deleted rows. These rows will be read, too.

Birgitta

"If you think education is expensive, try ignorance"
(Derek Bok)
 
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von rick baird
Gesendet: Dienstag, 18. April 2006 23:52
An: Midrange Systems Technical Discussion
Betreff: Re: SQL to only check for existence:

Vern,

On 4/18/06, vhamberg@xxxxxxxxxxx <vhamberg@xxxxxxxxxxx> wrote:
> Are you sure there's no where clause? It's in the subquery (where
exists...), I think.
>
> The point is that "where exists" does not do a full table scan to return a
result, therefore it is very fast.

I meant, no where clause in the subselect inside the where exists().

mine had a LIKE '____xxxx___' compare in the subselect, which runs
much slower, especially if the first match is deep into the access
path it chooses.   I tested it - if the first match was towards the
beginning of the chosen access path, it found it sub-second.  near the
end, it was more like 6-10 secs.

> I'm not sure whether the select into is faster - probably no noticeable
difference.

no, I don't think select '1' is any faster, I just like it better.  it
returns 1 to my indicator variable when successful, and I can test for
(not $found).

I tried both ways and found no noticable difference.

> To explain further, the "select 1 from..." is working against a single-row
table, hence it
> returns a single value and can be assigned to the host variable (I think
that makes
> sense). It'd probably fail if the "select 1 from..." returned multiple
rows.

my first attempt (without the sysdummy1 file) did fail.  I'd never
heard of the sysdummy1 file.

> The idea is to test for records in a file, so that file name goes into the
innermost select.
>
> HTH

it does, thanks!

Rick