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