RE: Embedded sql without index faster than with index

Hi Vern, glad to see you up so early.

This thing is driving me nuts. I've just deployed to a copy of the production environment and the program is running painfully slowly compared to the one in my development library. Both are using the same files, however.

In development library : enter client number - subfile displays immediately
In production library : enter client number - subfile displays after 4 or 5 seconds!

enter amount : same time for each program 4 or 5 seconds. If I enter the same amount and run it again, sometimes it show immediately sometimes I have to wait.

Could a different acces plan be created for the same program if I recompile? Have I unintentionally fooled the optimizer?

My cursor is like 'Select * from qtemp/myView where ...

MyView is actually 2 files unioned together. I did it that way so that my cursor would not look so complicated.

The two pf in the view have the same format but different keyfields.

I'm going to try just recompiling.

-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] De la part de Vern Hamberg
Envoyé : mardi 8 mars 2011 11:52
À : RPG programming on the IBM i / System i
Objet : Re: Embedded sql without index faster than with index

David

If you are on a fairly recent release, there is a plan cache
for SQL, so that even dynamic SQL might not need to
reoptimize things. This means that as a statement is run
enough, its access plan will be stored.
Hence, better performance. The first time something is run,
things can be a bit slow. Without an index, it is likely a
temporary had to be built, and that can be slow.

All this is a guess but is definitely a possibility. The only
way to know for sure is to run a monitor over the whole
process - something not practical most of the time.

Anyhow, you got 2 good things - your first declared cursor,
and you saw the benefit of having a suitable index - the
latter is a must must have for good SQL performance.

HTH
Vern

DECLARE C1 CURSOR FOR
SELECT * FROM MYFILE
WHERE
date BETWEEN : wDate1 AND : wDate2 AND
client BETWEEN : wClientN1 AND : wClientN2 AND
amount BETWEEN : wAmount1 AND : wAmount2 ;

At least I did my first static cursor so thanks for that.

But..... when I enter only a client number and no amount,

eg client between 123 and 123 and amount between 0 and
9999999999.99 the results are fast..as I would expect because
the client is in the index.

Here's the thing : at first I thought that the dynamic

version was running faster than the static one. I have
continued testing and I cannot say which is the fastest. I am
entering data in both screens then rapidly hitting enter on
one screen and switching to the other. I have no explanation
why it was running so slowly yesterday. Or why the same
request executed in inav ran so much faster.

Anyhow, I shall probably now create an index on the amount.

If I'd done that in the first place I'd probably never have
noticed anything.

--
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.