if exists in php with UDB Linux

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Stearns - 27 Feb 2007 18:16 GMT

I need to see if some rows exist, but do not need to read them. The best
I could come up with is:

$sql = "SELECT 1 FROM $schema.auction_owners
WHERE auction_id=$ai
AND item_id=$item_id
FETCH FIRST 1 ROW ONLY
FOR READ ONLY
";
dosql($sql, "S AO4", -1, "", $n2, $res2);
if(odbc_fetch_array($res)) {
...
}

Reply to this Message

jefftyzzer - 27 Feb 2007 18:40 GMT

> I need to see if some rows exist, but do not need to read them. The best
> I could come up with is:
[quoted text clipped - 12 lines]
>
> - Show quoted text -

Your method certainly works. If you want to do something SQL-related
after you find if the rows exist or not, you may want to consider
using BEGIN ATOMIC:

BEGIN ATOMIC
    IF ((SELECT 1 FROM X WHERE ... FETCH FIRST 1 ROW ONLY) = 1) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
    END IF;--
END;

You could also combine your method with BEGIN ATOMIC, i.e.,

BEGIN ATOMIC
    IF (NOT EXISTS(SELECT 1 FROM X WHERE ...)) THEN
do something, e.g. SIGNAL, INSERT, etc. (but no
DDL)...
    END IF;--
END;

--Jeff

Reply to this Message

Bob Stearns - 27 Feb 2007 18:48 GMT

>>I need to see if some rows exist, but do not need to read them. The best
>>I could come up with is:
[quoted text clipped - 34 lines]
>
> --Jeff

Thanks. I'm doing different things at the PHP level depending on the result.

Reply to this Message

Ray - 28 Feb 2007 15:23 GMT

> >>I need to see if some rows exist, but do not need to read them. The best
> >>I could come up with is:
[quoted text clipped - 36 lines]
>
> Thanks. I'm doing different things at the PHP level depending on the result.

Why not use SELECT COUNT (*) ?

Reply to this Message

jefftyzzer - 28 Feb 2007 18:40 GMT

> > >>I need to see if some rows exist, but do not need to read them. The best
> > >>I could come up with is:
[quoted text clipped - 40 lines]
>
> - Show quoted text -

Please see the section titled "Tuning existential predicates" in the
following TinyURL link for an opinion on COUNT(*): http://tinyurl.com/2t3gac

--Jeff

Reply to this Message

Ray - 01 Mar 2007 15:23 GMT

> Please see the section titled "Tuning existential predicates" in the
> following TinyURL link for an opinion on COUNT(*):http://tinyurl.com/2t3gac

I agree that irrelevant existential tests are a waste of time but I
don't see where that's the case when the whole reason for the query is
simply to establish existence to control program flow at a later time.
That said, I'm sure I'm overlooking something obvious to the more
experienced so what am I missing?

Reply to this Message

jefftyzzer - 01 Mar 2007 18:58 GMT

> > Please see the section titled "Tuning existential predicates" in the
> > following TinyURL link for an opinion on COUNT(*):http://tinyurl.com/2t3gac
[quoted text clipped - 4 lines]
> That said, I'm sure I'm overlooking something obvious to the more
> experienced so what am I missing?

Hi, Ray.

I'm not saying you're missing anything. I think Serge's argument (the
author of the opinion I included) is that there's no sense counting up
a bunch of things only to throw this number away.

The better thing about EXISTS is that the minute a single row tests
true, the search stops, so there's the potential for a much faster
test.

Anyway, I think we're splitting hairs here. As in all things,
individual results will vary. Do what works :-)

Regards,

--Jeff

Reply to this Message

Ray - 01 Mar 2007 20:52 GMT

> I'm not saying you're missing anything. I think Serge's argument (the
> author of the opinion I included) is that there's no sense counting up
> a bunch of things only to throw this number away.

Thanks, Jeff. In fact I WAS missing a couple of things. First was the
explicit optimization of the select, which I now understand.

> The better thing about EXISTS is that the minute a single row tests
> true, the search stops, so there's the potential for a much faster
> test.

Does that mean that DB2 will actually short circuit the select in the
infocenter example for exists?

EXISTS (SELECT * FROM TEMPL WHERE SALARY < 10000)

Reply to this Message

Brian Tkatch - 01 Mar 2007 21:45 GMT

>> I'm not saying you're missing anything. I think Serge's argument (the
>> author of the opinion I included) is that there's no sense counting up
[quoted text clipped - 11 lines]
>
> EXISTS (SELECT * FROM TEMPL WHERE SALARY < 10000)

I think it should. EXISTS (SELECT * is the classic example of
short-circuiting.

B.

Reply to this Message