Thanks, Javier, all good thoughts.
Quick question: If I am dialing into the system using Remote Desktop (no VPN,
not dialed into the database server itself), am I seeing the same performance
as a person on a network workstation? If not, then that screws up my ability
to test speed advances.
Multi-line commands: I wasn't sure if the whole code was read into memory or
not, didn't know if that was only in while loops. If a multi-line command is
read internally as one line that would be awesome. Although it would be a
quick "fix" if consolidating those lines would speed it up.
We changed from a WHILE loop to a GOTO in Nov 2014, so a little over 2 years
ago. The client was on 9.5 at that time. The routine was taking only 45
minutes to run in 9.5 with the goto/label and was acceptable, but we upgraded
to Version 10 and changed to 64-bit and with no changes to the program or table
structure now it takes 3 hrs. RBTI gave me some suggestions for checking code
and table structure, I made one change to indexes (removed duplicate indexes,
even though I'm not updating the table and it's only used for the primary
cursor), did another unload of the database, but it didn't help. Who knows,
maybe the while loop will not only work, but also would be faster than
goto/label in version 10? I'm wondering if, on my own time, I should try to
put the program "back" to a while loop and test it in version 10 to see if it
completes and if it's any faster... Hmmm.....
Temp tables: yeah I guess I should think of creating temp tables for all of
the lookups. I counted -- there's 10 different tables of lookups (so 10 temp
tables??), but there's many different criterias. All the criteria is at least
3 items, some search for up to 8 items to match. Some criteria is
non-indexable such as "not null" or ">". If I search one time by 8 items, once
by 3 items, once by 5 items, how would I go about setting up indexes for all
that? And do I only include columns where I search using "=" in multi-column
indexes?
For one example, it first looks at a table for the most restrictive match: can
I match the policy, the company, the agent, the year the policy started (which
is a >), the coverage plan, whether the policy offers advances. If it doesn't
match that, then it'll drop 1 or 2 of the criteria and do another search, and
so on. The only unique is policy / company. I don't know how I could set up
indexes to match the many searches just to this one table, and I literally have
9 other tables with similar lookups.
My usual routine is to set individual indexes for the most unique columns
(which are policy# & company code, both text), but not do indexes for those
columns that have alot of repetitive data. For example, for "agent", of the
40K rows, there may be only 20 different agents. I don't think that would be a
good index, do you?
If anyone wants a clue as to why health insurance is so confusing, you should
see this routine!
Thanks to anyone who had the fortitude to read my verbose email....
Karen
-----Original Message-----
From: Javier Valencia <javier.valen...@vtgonline.com>
To: rbase-l <rbase-l@googlegroups.com>
Sent: Mon, Apr 24, 2017 3:05 pm
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?
I think you have gotten several good answers already but this is what I know
based on my experience.
Indexes – I had an application that generated random inspection location for a
large number of records and ran relatively quick. Then it started to run very
slowly and I could not figure out why at first until I notice one of the techs
working for me with had removed the indexes and it made a huge difference. Now,
when looking to speed up code, the first thing I do is make sure I have the
indices properly configured.
WHILE versus GOTO – I remember that at one time there was an issue with WHILE
loops but as far as I can tell, that issue was resolved a while back. I have an
optimization utility that has several levels of WHILE loops and I have not had
an issue for them in a long time. Properly optimized the code runs very fast.
Multi-line Commands – I will guest that the code is read first into memory to
optimize it and each command line, regardless of how many lines it uses, is
interpreted as one line. For readability purposes and to get around the column
limitation of the old Codelock, I routinely use multiple lines, many times
dozens, for one command, particularly when selecting or updating records and I
also use the full command name rather than the abbreviation. Yes, it takes a
lot more space but memory is not the issue it used to be in the old DOS days. I
remember this topics being discussed at one time and I seem to recall that
multi command lines were not an issue…at least I hope it is not. J
Temporary Tables – Temporary Tables are stored in local memory and as such will
be accessed considerably faster than hard disks, particularly if you are
working over a network.
One technique I have used in the past is to set different variables at various
places in the code equal to #TIME
First, set the format to:
SET TIME FORMAT HH:MM:SS.SSSS
Then at various places set various variables
SETVAR vTime1 = .#TIME
SETVAR vTime2 = .#TIME
Or you can make your variable names more descriptive:
SETVAR vStartOfLoop = .#TIME
And so on.
By looking at the times at various places you can determine the time
differential it takes to execute the code and concentrate in the portions that
are taking longer within each iteration. If you want to get fancy you can make
the variable name contain the number of the iteration and write to an external
file at the end of the iteration and clear the older variables and then load
the file with all the times to a spreadsheet for easy analysis.
Javier,
Javier Valencia, PE
O: 913-829-0888
H: 913-397-9605
C: 913-915-3137
From: karentellef via RBASE-L [mailto:rbase-l@googlegroups.com]
Sent: Monday, April 24, 2017 1:05 PM
To: rbase-l@googlegroups.com
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
Yeah, I'll try to trace the program while everyone is connected and see if I
can pinpoint any particular commands that seem like they take longer than they
"should". The only issue is that depending on the particular data, it will be
processing just a small percentage of the entire 600 lines of code because of
all the jumping around so it's kinda hit-or-miss whether I'll happen to get a
nice row of data that gets to the section that's hanging everything up....
So I'm gonna guess that no one can answer my original question of whether RBase
can locate a "label" faster if there's less physical lines of code. If by
making all the multi-line command files just 1 or 2 lines instead, I could
probably reduce that 600 lines of code to about 400 I'll bet, but I don't want
to bother if it won't make a difference. Mercy, there even so many lines of
comments trying to explain what's going on, I could probably even get it down
to 300 lines!
Karen
-----Original Message-----
From: Dan Goldberg <d...@lancecamper.com>
To: rbase-l <rbase-l@googlegroups.com>
Sent: Mon, Apr 24, 2017 11:56 am
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?
Depending on the size of the tables and the complexity of the where clause
sometimes temp tables will speed it up tremendously. I use them throughout my
programming to speed up different things.
Tracing usually shows me which one is slowing it down and that is where I look
at.
Dan Goldberg
From: rbase-l@googlegroups.com [mailto:rbase-l@googlegroups.com] On Behalf Of
Doug Hamilton
Sent: Monday, April 24, 2017 9:53 AM
To: rbase-l@googlegroups.com
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
Hard to believe? No, just proves that sending guys to the moon is easier than
figuring out insurance stuff. :)
D
On 4/24/2017 11:37 AM, karentellef via RBASE-L wrote:
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>
To: rbase-l <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>
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>
To: rbase-l <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]
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
Virus-free. www.avast.com
--
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.
--
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.
--
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.
--
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.
--
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.
--
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.
--
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.
