Re: Is there another way (Was: benchmarking, which statement is faster

On 1 Aug 2001 12:26:24 -0700, mpir_at_bellsouth.net (Joe Maloney) wrote:

>Bowing to the Masters:

face the other way, then... :-)

>
>Why not do a count first?
>snip...
>I realize two calls/selects is slower than 1, but it avoids the 'too
>many values' condition that can result from the select into with
>multiple response, unless you are always processing array values.
>

Hope the other message I sent after that one got to you. I meant to say to use the SQL%ROWCOUNT with the fetch cursor loop, not the implicit cursor. These can only return no rows, one row or the "too_many_rows" exception.

In general I don't like to do two SQL's, one for count and one for data. If possible, I use the implicit. If not, then I use the normal fetch next loop or the cursor for loop and SQL%ROWCOUNT. Avoids having to countfirst, then do. In addition, ROWCOUNT can effectively be used with updates or deletes, which do not return "no_data_found" when the "where" condition finds no rows to update/delet: just use an "if (SQL%ROWCOUNT = 0)" and bingo: there is the "no_data_found" simulated.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Thu Aug 02 2001 - 17:38:17 CEST