I would find out why the while loop never completes. I have 9 level while loops
for my BOM to break down the assemblies into a parts list that runs every night
and it always runs.
There are many tricks on speeding up processing. Sometimes using temp tables to
reduce the amount of items in the where clause usually speeds things up. This
is only one of them I use.
Example, maybe use a temp table for the select statement below. I am assuming
the select statement runs many times.
--create temp table to hold values filtering out the standard items
Create temp table tmpagtcomm (agentno integer, policy_no text, covcode integer)
Insert into tmpagtcomm select agentno, policy_no, covcode from agtcomm where
polyr = 1 and agtcomm < 0 and paidtoagton is not null
--While loop
SELECT agtcomm INTO vtestagtcomm +
FROM tmpagtcomm +
WHERE agentno = .vagentno AND policy_no = .vpolicy_no +
AND covcode = .vcovcode
This way it is not looking at all the where parameters which might slow it down.
Not sure if this helps. I usually trace it as well to see what is slowing it
down.
Dan Goldberg
From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com]
Sent: Monday, April 24, 2017 6:14 AM
To: rbase-l@googlegroups.com
Subject: [RBASE-L] - Thoughts on speeding up a cursor?
I inherited a monster program. It's 800 physical lines of code, separated like
this:
100 lines of pre-processing code before we set a cursor
600 lines of code that are within a DECLARE CURSOR that processes 40,000
records. We cannot use a "while" loop because it never completed, so we use a
"goto / label" structure to move around, and it always completes fine.
100 lines of post-cursor code.
I am trying to speed up this cursor as it now takes hours to process. There
are no "run" statements within this program, no printing of reports other than
post-cursor.
Within that cursor loop, there are many "goto" statements to move around within
that cursor loop.
My assumption: when the program hits a "goto" command, it must run through
every line of code, one line at a time, to find the "label". It would go all
the way to the end of the program, and if it cannot find the label, it then
goes back up to line 1 of the program and scans every line until it finally
hits the label. In this program, sometimes these labels are after the goto,
sometimes they are "above" it.
So question 1: is my assumption correct?
If it is: Let's say for readability that a line has been separated into
multiple lines, such as this:
SELECT agtcomm INTO vtestagtcomm +
FROM agtcomm +
WHERE agentno = .vagentno AND policy_no = .vpolicy_no +
AND covcode = .vcovcode AND polyr = 1 AND agtcomm < 0 +
AND paidtoagton IS NOT NULL
As it searches for a matching "label", is RBase evaluating 5 lines of code, one
at a time? Or is it "smart" enough to know it's one command and evaluates it
just once?
So IOW: if I was to retype this command so that it takes just one really long
line, or maybe just 2 lines, would it be "quicker" for RBase to search for a
label? I wouldn't normally be so anal about it, but when you're doing this
40,000 times.....
Karen
--
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
rbase-l+unsubscr...@googlegroups.com<mailto:rbase-l+unsubscr...@googlegroups.com>.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to rbase-l+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
