Performance Question!

[make]

Hallo !

I start a query over a "choose of" Trigger of a button.
Everything works fine.
When fire the trigger again, the result comes much later than before.

Here a part of my query :

Find Kunden where Kunden.k-nr = int(trim(kunde)) no-lock no-error.
if available Kunden then do :
find first xaufpos
where (xaufpos.kdnr eq Kunde)
and (xaufpos.ldat ge ChVon) no-lock no-error no-Wait.
if not available xaufpos then
message "Für diesen Zeitraum liegen keine Kunden-Daten vor !" view-as alert-box information .
else
run Value_Del.
run Value_Set.
Run P_Del.
PercDsp :Screen-Value = "".
FOR EACH xaufpos
WHERE (xaufpos.kdnr eq Kunde)
and (xaufpos.ldat ge ChVon)
and (xaufpos.ldat le ChBis)
and (geliefert eq yes)
and (xaufpos.posstat eq "7.4")
NO-LOCK.
if xaufpos.mat = 1 then do : /* Mineral */
if xaufpos.typ = 1 THEN do :
MinEin = MinEin + string(xaufpos.stk).
...
...
...

I thougt that the reason is that i use the string-function "String(xaufpos.stk) and that a data has to be transmitted to my client. But iam not shure !

Can anyone help me.

make

 

[jongpau]

Hi,

First of all check that all finds and for each statements use an appropriate index. The most common cause of performance problems is finds and for each statements for which no decent index can be found (and Progress searches the whole table sequentially).

To figure this out it is easiest to compile your code with the XREF option (eg compile program.p XREF myxcode.txt). This produces a file named myxcode.txt in your STARTUP directory. You can open this file with for instance Notepad or WordPad. Look in the xcode file for entries that contain "WHOLE-INDEX"... these should make you suspicous and are very much worth investigating. Look in your Progress documentation to find out how to exactly interpret an XREF file. If you do not have the Progress books, look for the Progress e-doc cd (which comes with your Progress installation disks) and install that (it contains ALL Progress handbooks). If you do not have the edoc cd, you can always read (and download) the Progress documentation from www.progress.com.

If that does not seem to be the cause of the problem, I would do the following to see where things start to get slow:

At certain points (for instance before and after each find, for each, run etc) put the following in your code:
ETIME(TRUE).
FOR EACH......

END. /* for each */
MESSAGE "Some Identifier so you know wher your are" ETIME(FALSE) VIEW-AS ALERT-BOX.

The MESSAGE ETIME(FALSE) shows the number of miliseconds that have elapsed since the last ETIME(TRUE) was executed. This helps you to see how long each of the statements or codeblocks have taken to execute.

If it looks like a RUN statement is eating up your valueable time, start doing the same in the code of your (internal) procedure (of course remove the previous ETIMEs first).

It's best to keep the number of messages limited and working through your code gradually so you do not get lost in too many alert-boxes popping up.

Once you have found the piece of code that is making your stuff slow, check out what you are doing there and try to work out what the problem is (as I said, most of the time it is a query or find on a more or less large table with no or no well defined index available).

One more thing... you use a FIND statement with NO-LOCK and NO-WAIT. NO-WAIT is of no use with a NO-LOCK and only has to be used with EXCLUSIVE-LOCKs (in combination with the LOCKED function).

HTH

 

[jongpau]

Ooops... almost forgot.....

You say you execute that code in a "choose" trigger (probably of a button).

It's best to place large(r) amounts of code like what you show in your example in an internal procedure. This to make sure you do not exceed the - unfortunate - limits of Progress (which may happen when you have a lot of triggers, a large main block, a few variables and temp-tables etc).

Each internal procedure can be up to 63kb (compiled) while the main procedure - which contains your definitions, triggers and main block - can also only be 63kb....

Also it makes the procedure far more readable and easier to maintain when you use well defined (and if you do really well, reusable) internal procedures.

HTH