RE: SQL performance question 2

It'd be nice if someone had some examples which could show typical cases of where SQL would be appropriate, and where it would not. It would make a good chapter for the WIKI and provide some guidelines for those starting off.

I agree this sort of question is too common, but searching in the archives RPG vs SQL isn't very easy.


-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Vern Hamberg
Envoyé : jeudi 18 décembre 2008 16:13
À : RPG programming on the IBM i / System i
Objet : Re: SQL performance question 2

David

I use SQL all the time, and I agree completely that it is NOT the tool for what your people are doing, especially the SETLL test for a record's existence - that is so well done in RPG, and it is at least clumsy in SQL.

It hurts nothing to have an F-spec for a file AND to use that file in a SELECT - FETCH loop - nice thing is, you already have all the field names, then.

I recommend putting *HIVAL into wKeyfield2, then use READPE with the same 2 key fields - the READP might not work, because you can't guarantee here that a record exists for wKeyfield1 - in fact, this can work as both your existence test and finding the max as you want. I think!

David FOXWELL wrote:

My second question :

I'm rather worried when I see this :


EXEC SQL

Select MAX (keyfield2) Into : wkeyfield2

From Myfile

Where keyfield1 = : wKeyfield1;



To replace this :

SETGT (wKeyfield1 :wKeyfield2 ) MyFile

READP MyFile

To get the highest value of wKeyField2 for wKeyField1.

I would have thought that, on a large file, RPG would excel here.

I'd like your opinion.

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.