Date: Thu, 18 Dec 2003 10:20:30 +0300
From: "Dimitry Sibiryakov" <SD@...>
Subject: Re: Getting number of records affected ...

On 17 Dec 2003 at 4:34, Anand wrote:

>How do I find out the number of records affected by an
action
>query?

Besides other suggestions, I must tell you another
variant by
Dmitri Popov:

DECLARE VARIABLE MY_PK_VALUE INTEGER;
............
MY_PK_VALUE = NULL;
FOR SELECT PK FROM MY_TABLE WHERE ....
INTO :MY_PK_VALUE AS CURSOR TMPCURSOR
DO
UPDATE MY_TABLE SET ... WHERE CURRENT OF TMPCURSOR;

IF (MY_PK_VALUE IS NULL) THEN
INSERT INTO MY_TABLE .....
..........

This variant has proved itself as the fastest.

SY, Dimitry Sibiryakov.



Date: Thu, 18 Dec 2003 18:27:55 +1100
From: Helen Borrie <helebor@...>
Subject: Re: Re: Re: Getting number of records affected
...

At 10:27 PM 17/12/2003 -0800, you wrote:
>Thanks Helen and Lucas :)
>
>I think the ROW_COUNT variable is what I was looking
for.

I don't.

>However, I have
>another question to ask now:
>
>Till now I have designed the SPs in such a way that a
SELECT
>COUNT(*) FROM
><table> WHERE <condition> is executed on the table in
question.
>If the
>return value is zero, I would attempt an INSERT, and if
the
>return value was
>1, I would attempt an UPDATE. My senior told me the
COUNT(*)
>function will
>slow down the procedure in case the table has a huge
number of
>records, and
>that I should get rid of it. By that logic, would the
SELECT 1
>FROM ...
>statement also slow down the procedure?

You should *totally avoid* counting rows to use as an
existence
check. SELECT 1 etc. is no better. In both cases, they
have to do
first a
search and then do something. In the first case, the
"do" is
"increment a
total", in the second, it is "output a row".

This is rusty old Paradox/DB4-style programming. SQL
provides
existence
predicates, old desktop DBs don't. The EXISTS() test
completes and
returns
true as soon as the first matching row is found; and it
does not
produce
any output.


>In short, from the two SELECT queries below, which one
would
>work faster?
>
>1. SELECT COUNT(*) FROM <table> WHERE <condition>
>
>2. SELECT 1 FROM <table> WHERE <condition>

If there is any difference, it would be insignificant.
Equally bad.

>And then, if I used the ROW_COUNT variable, which of
the
>following would
>work faster?
>
>1. UPDATE <table> SET .... WHERE <condition>
> IF ROW_COUNT = 0 THEN
> INSERT INTO <table> VALUES <values>

This is three or four operations: 1) perform an update
2) read the
row_count 3) test the row_count 4) insert a row if
row_count is 0.
The
update is costly because it has to search the whole
table, as well
as any
pending record versions in other transactions.


>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. 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.

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? Till this SELECT
statement
returns, the EXISTS() won't be evaluated, or will it?

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. So I am using the EXISTS() method right now.



Thanks and Regards,

Anand S. Kashelkar
Mumbai





__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/