Karen, I wonder if SET ZERO ON/OFF has different results with the two selects. Inquiring minds want to know — well, mine anyway. Albert
> On Apr 25, 2017, at 3:12 PM, karentellef via RBASE-L > <[email protected]> wrote: > > It bugs the HECK out of me to see all this old syntax out there. If I wasn't > so busy I would upgrade it free of charge for them, but that's not the case > (fortunately I guess). It really surprised me, and caught me off guard, > with the difference between "set var" and "select into", so it goes to show > that you can't just update the syntax, you do have to carefully test to make > sure it returns what you expect under all conditions! > > And darn it, I just compared what happens when you do a "select (sum(column)) > into" and a "compute varname as sum column..." and both them also return > different results if nothing satisfies the where clause!!! So again, there > would be alot of testing to make sure the newer "select sum" would return the > same result as the program would have expected that the "compute" would have > returned. > > > Nope, I looked, I can't find a copy of the program from pre-2014. So I'd > have to reconstruct the while loop. > > Karen > > > -----Original Message----- > From: Javier Valencia <[email protected] > <mailto:[email protected]>> > To: rbase-l <[email protected] <mailto:[email protected]>> > Sent: Tue, Apr 25, 2017 1:30 pm > Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor? > > A while back I bit the bullet and upgraded all old syntax to the new one and > never looked back. If you have the original code with the cursors/while loops > it might be worth giving it a try and see if it works now. > > Javier, > > Javier Valencia, PE > O: 913-829-0888 > H: 913-397-9605 > C: 913-915-3137 > > From: karentellef via RBASE-L [mailto:[email protected] > <mailto:[email protected]?>] > Sent: Tuesday, April 25, 2017 12:44 PM > To: [email protected] <mailto:[email protected]> > Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor? > > No, I'm the only one using RD, everyone else is directly connected over an > internal network. But yes, I can see that if I compare a before/after on RD > and it's faster after, then it obviously means it would be faster on the > network too. > > Yeah, whew this is a toughy to figure out! If only there weren't so many > darn lines of code within the cursor loop! And because this is a legacy from > very old DOS code, there's also alot of "compute" statements rather than > "selects", don't know if compute is slower. Maybe version 10 isn't happy > with the computes. > > I once tried to replace an old "SET VAR vVarname = colname from table > where.... " with a "select colname into vVarname from table" and found out > that they behave totally different if nothing matches the where clause!!! In > the old code, it would replace any existing variable with a Null if there was > no match, whereas the new code gives the "warning no rows" message and it > keeps any previous value in that variable. So I realized that I'd have to > specifically null out the variables first! There's so many of these old "set > var =" statements that I didn't want to take the chance, so I decided to NOT > update that old code for fear of messing something up. > > I think it would take me about 10 hours simply to clean up and test the code > to bring the syntax itself literally into the 21st century! So far, haven't > gotten permission to do so.... Because the code used to run fast in 9.5, > they do the "if it ain't broke"..... > > Karen > > > > -----Original Message----- > From: Javier Valencia <[email protected] > <mailto:[email protected]>> > To: rbase-l <[email protected] <mailto:[email protected]>> > Sent: Tue, Apr 25, 2017 12:30 pm > Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor? > If the users are accessing the database the same way you are then the speed > should be comparable. If you are using Remote Desktop, then you are using the > server to do the processing and you are only seeing the screen upgrades. If > all your users are accessing the application this way, then the server > capacity would be the more important issue; hopefully you have plenty of > memory on that server. > Generally, the relative speed of two processes would be comparable when > running on two different platforms. i.e. if process A run faster than process > B on your local computer, chances are it will also run faster on a server as > well; obviously the actual speeds would be different. > > Optimization is in a way an art that requires fine tuning to get the most > value; how you construct your views and indices has big effect. In my > experience, indices for Integer columns work the fastest and these are the > ones I try to use first when developing new code; text columns longer that 4 > characters tend to be slowest. > > There is an article on optimization that might be of help in the From the > Edge Section; it was originally written for 9.5 but the principles should > still be valid for the newer versions. Here is the link: > http://www.razzak.com/fte/pdf/OptimizingApplicationPerformance95.pdf > <http://www.razzak.com/fte/pdf/OptimizingApplicationPerformance95.pdf> > > Javier, > > Javier Valencia, PE > O: 913-829-0888 > H: 913-397-9605 > C: 913-915-3137 > > From: karentellef via RBASE-L [mailto:[email protected] > <mailto:[email protected]?>] > Sent: Monday, April 24, 2017 3:50 PM > To: [email protected] <mailto:[email protected]> > Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor? > > 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 <[email protected] > <mailto:[email protected]>> > To: rbase-l <[email protected] <mailto:[email protected]>> > 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@go > <mailto:rbase-l@go?>oglegroups.com] > Sent: Monday, April 24, 2017 1:05 PM > To: [email protected] <mailto:[email protected]> > 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 <[email protected] <mailto:[email protected]>> > To: rbase-l <[email protected] <mailto:[email protected]>> > 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: [email protected] <mailto:[email protected]> > [mailto:[email protected] <mailto:[email protected]?>] On > Behalf Of Doug Hamilton > Sent: Monday, April 24, 2017 9:53 AM > To: [email protected] <mailto:[email protected]> > 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 <[email protected]> <mailto:[email protected]> > To: rbase-l <[email protected]> <mailto:[email protected]> > 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 <[email protected]> <mailto:[email protected]> > To: rbase-l <[email protected]> <mailto:[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 > > > <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 <http://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 [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] > <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] > <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.
