Here's the thing -- believe it or not, there is NOT A SINGLE PLACE in
that 600 lines of cursor where I am _updating_ a record. Never, not
once. It does a whole bunch of selects, from a whole bunch of
different tables, and there's a whole bunch of variable calculations.
Depending on conditions, it skips around all over the place to
retrieve those variables from tables, whether or not to make certain
calcs, etc.
The _only_ table operation it does is at the very end, when it's done
with its calculations, it finally inserts one row into a temporary
table....
I know, hard to believe, isn't it? I don't think NASA has
calculations as complicated as this routine just to get a single answer.
Karen
-----Original Message-----
From: Doug Hamilton <bugl...@wi.rr.com>
To: rbase-l <rbase-l@googlegroups.com>
Sent: Mon, Apr 24, 2017 11:32 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
Karen - I had a similar speed question when using the UPDATE command
on 2/3/15, although my questions was more about optimal use of the
WHERE clause than GOTO and labels. You, Dennis and others offered
many helpful answers.
If you think optimizing the DECLARE CURSOR would help, here is the
response from Dennis that might help you as far as order of the
columns, using parens, etc.:
Doug,
First of all, is the column you are updating indexed? That would slow updating
it tremendously on a table this long.
If that is not the case I would do this:
1. Make a multi column index on your temp table for the 3 columns in the order
that is used in your joining where clause.
2. Make the temp table the second table, not the first.
3. Set manopt on to make sure R:BASE follows your optimization.
4. Use this syntax (no parenthesis around the where clause):
UPDATE TxnHist +
SET ChryInvNbr = INV.ChryInvNbr +
FROM TxnHist TXN, ChryInvDtlTmp INV +
WHERE +
TXN.VPlNmbr = INV.VPlNmbr AND +
TXN.CusPnbr = INV.CusPnbr AND +
TXN.TxDate = INV.InvoiceDate
This will avoid trying to use any of the single indexes in TxnHist, and use a
very efficient multi-column index to get the update value from the temp table.
Further optimization can be done by changing the where clause (and temp index)
clause so the most unique column is first.
I suspect InvoiceDate would be the most unique, but only you can answer that
question.
BTW, I don't think labels and GOTOs are the problem. Suppose you
rewrote the code and saved a few milliseconds per loop by "optimizing"
the GOTO/labels. At 40,000 records that's only a difference of, say,
40 to 120 seconds total (a few minutes), hardly a dent in the several
hours the program now runs. I think Dennis's first point might be a
clue: Updating an indexed column.
Doug
On 4/24/2017 9:50 AM, karentellef via RBASE-L wrote:
That select statement is _not_ my cursor, that's just one of the
many 600 lines of code that the cursor is evaluating. The cursor
itself would not be index-able as it contains >=, not null, etc....
I mean, yes, I could look at the many, many select statements
within the loop (my wild guess is that there's around 50 of them)
and maybe there would be 10 or 15 different potential compound
indexes. I'm not sure if there's a practical limit to the number
of compound indexes you could create on a single table (there
would be probably 10 different "lookup" tables).
So yeah, good idea, I'll look at all the lookups and check
indexing. But I'm assuming that compounds would only work in
instances where all of the components are using "=", right?
Karen
-----Original Message-----
From: Albert Berry <alb...@albertberry.com>
<mailto:alb...@albertberry.com>
To: rbase-l <rbase-l@googlegroups.com>
<mailto:rbase-l@googlegroups.com>
Sent: Mon, Apr 24, 2017 9:40 am
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
Karen - wild thought. Would a compound index work here?
CREATE INDEX LoopTroubles (PolicyID,AgentNo,Policy,CovCode)
This would enable an index only retrieval.
Albert
On Apr 24, 2017, at 8:26 AM, karentellef via RBASE-L
<rbase-l@googlegroups.com <mailto:rbase-l@googlegroups.com>>
wrote:
Dan:
I had actually posted here on the list a few years ago when,
as the business grew, our cursor (which used to process about
25K rows) started randomly crashing in the 30K or 40K range.
Several people here recommended to replace the while loop with
goto/label, so that's what I did. The goto works fine, so I'm
not interested in revisiting a while loop.
I'm not understanding what you're suggesting on a temp table.
I would have to create a temp table that would hold probably
30K rows, and my "select into" would simply operate against a
temp table rather than the permanent table. Are you saying
selecting against a temp table would be faster than a
permanent table?
One thing that I've asked permission to try -- that is to
avoid a "declare cursor" altogether, which puts an hours-long
"cursor lock" against a very heavily used table.
I'm thinking I could create a 40K row temp table with the
policyID I'm to process (the PK), with an autonumber column,
such as:
1111 1
1222 2
3535 3
Then using my goto/label block, I could (just quick code here,
not 100% right)
set var vcount int = 1
label top
select policyid into vid from temptable where autonumbercol
= .vcount
if vid is null then ; quit ; return
select .... into ..... from policytable where policyid =
.vid (this replaces the "fetch")
-- do all the "cursor" loop stuff
set var vcount = (.vcount + 1)
goto top
I don't know if this will speed up the code, but it prevents
the routine from putting ANY locks on the main table.
Karen
-----Original Message-----
From: Dan Goldberg <d...@lancecamper.com
<mailto:d...@lancecamper.com>>
To: rbase-l <rbase-l@googlegroups.com
<mailto:rbase-l@googlegroups.com>>
Sent: Mon, Apr 24, 2017 8:34 am
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?
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
<mailto:rbase-l@googlegroups.com?>]
*Sent:*Monday, April 24, 2017 6:14 AM
*To:*rbase-l@googlegroups.com <mailto: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
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient&utm_term=icon>
Virus-free. www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient&utm_term=link>
<#aolmail_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
--
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
<mailto:rbase-l+unsubscr...@googlegroups.com>.
For more options, visit https://groups.google.com/d/optout.
