Re: SQL performance question 1

[Tommy.Holden@xxxxxxxxxxxxxxxxxxxxx]

SETLL would be better in this case...but if you want to use the SQL for 
this (yuck) i'd use something like this

Exec SQL
Select 1 into :uselessfield from myfile where key = :hostvar;


Thanks,
Tommy Holden



From:
David FOXWELL <David.FOXWELL@xxxxxxxxx>
To:
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
Date:
12/18/2008 08:12 AM
Subject:
SQL performance question 1
Sent by:
rpg400-l-bounces@xxxxxxxxxxxx



Hi,

A lot of our developers have recently been introducing SQL into their RPG 
code.

We've had a discussion to see how SQL is being implemented.

A lot have replaced this kind of loop:

SETLL key file
dou %eof(file)
READE key file
if %eof
   iter
endif

If condition
    indicateur = *on
    leave
endif
enddo

to see if a row exists, by this :

Select count(*) from myfile where key = : hostvar

I have advised :

Select key from myfile where key = : hostvar fetch first row only.


Is there a better way? Is this a standard way to see if a row exists? I 
know there is EXISTS( ) but that would be the same request (according to 
visual explain).

Does the RPG version still have any advantages?

Thanks.