At 11:11 PM 18/12/2003 -0800, Anand wrote:

> Date: Thu, 18 Dec 2003 10:20:30 +0300
> From: "Dimitry Sibiryakov" <SD@...>
> Subject: Re: Getting number of records affected ...
>
>
> >2. IF (EXISTS (SELECT 1 FROM <table> WHERE
><condition>)) THEN
> > UPDATE <table> SET .... WHERE <condition>
> > ELSE
> > INSERT INTO <table> VALUES <values>
>
> This is one test and only the one operation - EITHER an
>update OR an
>insert..
>
> /heLen
>
>
>Thanks, Helen and Dimitry :)
>
>Dimitry, the method you have posted seems similar in its basic
>working with
>the Update/Insert method I had used.
>
>Helen, if you say that "SELECT COUNT(*) FROM <table> WHERE
><condition>" and
>"SELECT 1 FROM <table> WHERE <condition>" are equally bad, your
>original
>suggestion of the EXISTS() function uses the SELECT 1 FROM ...
>syntax. If
>we were to dissect the following two methods:
>
>1. IF (EXISTS(SELECT 1 FROM <table> WHERE <condition>)) THEN
> UPDATE ...
> ELSE
> INSERT ...
>
>First it will execute the SELECT clause, which will have to
>traverse through
>the whole table to find matching records. Then it will have to
>evaluate the
>EXISTS function.

Wrong. If you use an indexed column, it only has to traverse the index
until it finds the first matching. Then it exits True. The whole index is
traversed if no match is found at all. This is one operation, and it is a
read operation that, when complete, has the Boolean it needs to then go on
to either insert or update.

> Then it will either UPDATE or INSERT.
>
>2. SELECT COUNT(*) FROM <table> WHERE <condition> INTO
><variable>
> IF <variable> = 0 THEN
> INSERT ...
> ELSE
> UPDATE ...
>
>Here, too, it will go through the entire table to retrieve the
>COUNT, assign
>it to a variable, test the variable and perform either an INSERT
>or an
>UPDATE.

Yes, that is what count does - it traverses the whole *set* (not the whole
table, if you have a WHERE clause on an indexed column).

>The only question for me here is how exactly the SELECT 1 FROM
>... will
>work. Will it scan the entire table, or will it return 1 as
>soon as it
>finds a record that satisfies the condition?

It is very much like count except that, instead of keeping a tally of found
rows, it returns a set containing '1' for each row found.

>Till this SELECT
>statement
>returns, the EXISTS() won't be evaluated, or will it?

EXISTS() doesn't return a set at all. It just reads.

>Thirdly, the approach suggested by Dimitry: Is it really the
>fastest as he
>claims?
>
>Funnily, IBExpert flagged the variable "ROW_COUNT" saying it was
>"Undefined". Dunno what's wrong. I have specified the database
>to be
>Firebird 1.5.

You do need to read ROW_COUNT inside the begin...end block where the DML
operation occurs. But IBExpert might somehow have hard-references to
keywords (I don't know what parsing IBExpert performs on the SQL before it
hands it over to the server) and you might have a version that only works
with v.1.0 databases. Check also that you have the right client
library. Maybe IBExpert is loading a gds32.dll....

As for the cursor method - it should be fast for the update, because it
"earmarks" the rows before it starts to operate on them. Whether it's
faster than the logical check that EXISTS() does, I don't know. It would
be a pleasant surprise, if it were faster. Dmitry Yemanov has done a full
implementation of cursor treatment in PSQL for Firebird 2, something we
have to look forward to.

When you post on threads, please don't use the "Digest" subject.

/heLen