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