Re: Opinions on approach, please...

On Tue, 27 May 2008 08:34:51 +0100, Frederico Fonseca <real-email-in-msg-spam@xxxxxxxxx>
wrote:

On Mon, 26 May 2008 22:52:47 -0500, Robert <no@xxxxxx> wrote:

On Mon, 26 May 2008 22:42:39 +0100, Frederico Fonseca <real-email-in-msg-spam@xxxxxxxxx>
wrote:

snip

Regarding another point you mentioned on one of your posts regarding
validating if A start does return any records.
You mentioned that you intended to do a open cursor, fetch next to
determine this.
You may find that it will perform better if you code as follows.
exec sql
select coalesce(max(first_key_field),'invalid_key') into :host_var
from tblx where first_key_field = :key_field and ....
end-exec.
Under normal conditions this sql will always return 0 on SQLCODE.
If not then the error is not an indication that the key does not
exist, but rather another error on the table/database, or eventually a
-911 (record locked timeout on DB2).

If sqlcode is zero, then host_var will either contain 'invalid_key' as
text, or it will contain the value of the first key field of that
table. You are really only interested to know if it contains
invalid_key or not, and act accordingly.

If there are 10,000 rows containing the selected first_key_field (e.g. company), you are
telling it to read all 10,000. A better way is:

select null from table where first_key_field = :key_field and rownum = 1

This will return SQLCODE = -100 (no data found) if no rows match.

In a where clause, EXISTS does the same -- it exits on the first matching row:

WHERE EXISTS (select null from table where first_key_field = :key_field)

Robert. you are wrong on the assumption that my sql will return all
rows.

Had you looked properly at it, you would have noticed the use of
MAX(first_key_field). This associated with the fact that we are
dealing with a case where every index on the COBOL files will have its
corresponding index on the database, means that my sql will process a
single row of the index. This is true in all main RDBMS.
For example on SQL Server the above SQL will translate immediately by
the engine to a TOP function with index seek, no data access.
DB2 will translate to a "one fetch index access as MAX used", and both
will perform under 1 hundredth of a second

You are correct. TOP is a range predicate, which is faster than index sargable
(searchable).

also rownum = x is database dependent. my code isnt.

It seems unintuitive to use MAX on a single value to test for existence. Rownum = 1 is
more descriptive. My Cobol background is showing.
.

Relevant Pages