I think using the compounds would allow you to pick up the values from the index (index only retrieval) and then the code wouldn’t have to actually retrieve the data from file 2 for some of the selects.
Albert > On Apr 24, 2017, at 8:50 AM, karentellef via RBASE-L > <[email protected]> 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 <[email protected]> > To: rbase-l <[email protected]> > 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 > <[email protected] <mailto:[email protected]>> 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 <[email protected] <mailto:[email protected]>> > To: rbase-l <[email protected] <mailto:[email protected]>> > 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:[email protected] > <mailto:[email protected]?>] > Sent: Monday, April 24, 2017 6:14 AM > To: [email protected] <mailto:[email protected]> > 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 [email protected] > <mailto:[email protected]>. > 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 [email protected] > <mailto:[email protected]>. > 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 [email protected] > <mailto:[email protected]>. > 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 [email protected] > <mailto:[email protected]>. > 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 [email protected] > <mailto:[email protected]>. > 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
