Karen,Here is some code I use for SELECT INTO where column data may or may not
exist.IF RvRctCCntRecHome > 0 THEN
SELECT (IFEXISTS(RctCMCampusLiveSts,RctCMCampusLiveSts,CHAR(32))), +
(IFEXISTS(RctCMOnCampusRoom,RctCMOnCampusRoom,CHAR(32))), +
(IFEXISTS(RctCMPMainAdrLine,RctCMPMainAdrLine,CHAR(32))), + +
(IFEXISTS(RctCMPCity,RctCMPCity+CHAR(32),NULL)+ +
IFEXISTS(RctCMPStateUSPSCd,RctCMPStateUSPSCd+CHAR(32),NULL)+ +
IFEXISTS(RctCMPPostalCode,RctCMPPostalCode, +
NULL)+ (IFNULL(RctCMPCountry, NULL,IFEQ(RctCMPCountry,'U.S.A.', +
CHAR(32),CHAR(32)+RctCMPCountry)))), +
(IFEXISTS(RctCMLMainAdrLine,RctCMLMainAdrLine,CHAR(32))), +
(IFEXISTS(RctCMLCity,RctCMLCity+CHAR(32),NULL)+ +
IFEXISTS(RctCMLStateUSPSCd,RctCMLStateUSPSCd+CHAR(32),NULL)+ +
IFEXISTS(RctCMLPostalCode,RctCMLPostalCode, +
NULL)+ (IFNULL(RctCMLCountry, NULL,IFEQ(RctCMLCountry,'U.S.A.', +
CHAR(32),CHAR(32)+RctCMLCountry)))) +
INTO RVCampusLiveSts IND RVHSIv1, RVOnCampusRoom IND RVHSIv1,+
RVPAddress IND RVHSIv1, RVPCityStateZip IND RVHSIv1,+
RVLAddress IND RVHSIv2, RVLCityStateZip IND RVHSIv1 +
FROM RctCurMailInfo WHERE RctNumber=.RvRctNumber
ELSE
SELECT CHAR(32),CHAR(32),CHAR(32),CHAR(32),CHAR(32),CHAR(32) +
INTO RVCampusLiveSts, RVOnCampusRoom, RVPAddress, RVPCityStateZip,+
RVLAddress, RVLCityStateZip FROM DirHighSchools +
WHERE HighSchoolNbr=0
ENDIF
It is easy enough to substitute NULL for CHAR(32) which is a space.The key is
to set the IFEXISTS statements correctly.
Jim Bentley, American Celiac Society 1-504-305-2968
From: karentellef via RBASE-L <[email protected]>
To: [email protected]
Sent: Tuesday, April 25, 2017 4:12 PM
Subject: Re: [RBASE-L] - Thoughts on speeding up a cursor?
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]>
To: rbase-l <[email protected]>
Sent: Tue, Apr 25, 2017 1:30 pm
Subject: RE: [RBASE-L] - Thoughts on speeding up a cursor?
#yiv4283550327 #yiv4283550327AOLMsgPart_2_8f2c118c-1539-44cb-81c9-06d27d680bbf
td{color:black;} _filtered #yiv4283550327 {font-family:Wingdings;panose-1:5 0 0
0 0 0 0 0 0 0;} _filtered #yiv4283550327 {font-family:Wingdings;panose-1:5 0 0
0 0 0 0 0 0 0;} _filtered #yiv4283550327 {font-family:Calibri;panose-1:2 15 5 2
2 2 4 3 2 4;} _filtered #yiv4283550327 {font-family:Tahoma;panose-1:2 11 6 4 3
5 4 4 2 4;} _filtered #yiv4283550327 {font-family:Consolas;panose-1:2 11 6 9 2
2 4 3 2 4;}#yiv4283550327 .yiv4283550327aolReplacedBody
p.yiv4283550327aolmail_MsoNormal, #yiv4283550327 .yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_MsoNormal, #yiv4283550327 .yiv4283550327aolReplacedBody
div.yiv4283550327aolmail_MsoNormal
{margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody a:link, #yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_MsoHyperlink
{color:blue;text-decoration:underline;}#yiv4283550327
.yiv4283550327aolReplacedBody a:visited, #yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_MsoHyperlinkFollowed
{color:purple;text-decoration:underline;}#yiv4283550327
.yiv4283550327aolReplacedBody p
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody pre
{margin:0in;margin-bottom:.0001pt;font-size:10.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody p.yiv4283550327aolmail_MsoAcetate, #yiv4283550327
.yiv4283550327aolReplacedBody li.yiv4283550327aolmail_MsoAcetate,
#yiv4283550327 .yiv4283550327aolReplacedBody
div.yiv4283550327aolmail_MsoAcetate
{margin:0in;margin-bottom:.0001pt;font-size:8.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_HTMLPreformattedChar
{font-family:Consolas;color:black;}#yiv4283550327 .yiv4283550327aolReplacedBody
p.yiv4283550327aolmail_aolmailmsonormal, #yiv4283550327
.yiv4283550327aolReplacedBody li.yiv4283550327aolmail_aolmailmsonormal,
#yiv4283550327 .yiv4283550327aolReplacedBody
div.yiv4283550327aolmail_aolmailmsonormal
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody p.yiv4283550327aolmail_aolmailmsoacetate,
#yiv4283550327 .yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailmsoacetate, #yiv4283550327
.yiv4283550327aolReplacedBody div.yiv4283550327aolmail_aolmailmsoacetate
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody p.yiv4283550327aolmail_aolmailaolmailmsonormal,
#yiv4283550327 .yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailaolmailmsonormal, #yiv4283550327
.yiv4283550327aolReplacedBody div.yiv4283550327aolmail_aolmailaolmailmsonormal
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody p.yiv4283550327aolmail_aolmailaolmailmsoacetate,
#yiv4283550327 .yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailaolmailmsoacetate, #yiv4283550327
.yiv4283550327aolReplacedBody div.yiv4283550327aolmail_aolmailaolmailmsoacetate
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody
p.yiv4283550327aolmail_aolmailaolmailaolmailmsonormal, #yiv4283550327
.yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailaolmailaolmailmsonormal, #yiv4283550327
.yiv4283550327aolReplacedBody
div.yiv4283550327aolmail_aolmailaolmailaolmailmsonormal
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody
p.yiv4283550327aolmail_aolmailaolmailaolmailmsonormal0, #yiv4283550327
.yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailaolmailaolmailmsonormal0, #yiv4283550327
.yiv4283550327aolReplacedBody
div.yiv4283550327aolmail_aolmailaolmailaolmailmsonormal0
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody
p.yiv4283550327aolmail_aolmailaolmailaolmailmsochpdefault, #yiv4283550327
.yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailaolmailaolmailmsochpdefault, #yiv4283550327
.yiv4283550327aolReplacedBody
div.yiv4283550327aolmail_aolmailaolmailaolmailmsochpdefault
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody
p.yiv4283550327aolmail_aolmailaolmailaolmailmsonormal1, #yiv4283550327
.yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailaolmailaolmailmsonormal1, #yiv4283550327
.yiv4283550327aolReplacedBody
div.yiv4283550327aolmail_aolmailaolmailaolmailmsonormal1
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody p.yiv4283550327aolmail_aolmailaolmailmsonormal0,
#yiv4283550327 .yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailaolmailmsonormal0, #yiv4283550327
.yiv4283550327aolReplacedBody div.yiv4283550327aolmail_aolmailaolmailmsonormal0
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody
p.yiv4283550327aolmail_aolmailaolmailmsochpdefault, #yiv4283550327
.yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailaolmailmsochpdefault, #yiv4283550327
.yiv4283550327aolReplacedBody
div.yiv4283550327aolmail_aolmailaolmailmsochpdefault
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody p.yiv4283550327aolmail_aolmailaolmailmsonormal1,
#yiv4283550327 .yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailaolmailmsonormal1, #yiv4283550327
.yiv4283550327aolReplacedBody div.yiv4283550327aolmail_aolmailaolmailmsonormal1
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody p.yiv4283550327aolmail_aolmailaolmailmsoacetate1,
#yiv4283550327 .yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailaolmailmsoacetate1, #yiv4283550327
.yiv4283550327aolReplacedBody
div.yiv4283550327aolmail_aolmailaolmailmsoacetate1
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody p.yiv4283550327aolmail_aolmailmsonormal0,
#yiv4283550327 .yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailmsonormal0, #yiv4283550327
.yiv4283550327aolReplacedBody div.yiv4283550327aolmail_aolmailmsonormal0
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody p.yiv4283550327aolmail_aolmailmsochpdefault,
#yiv4283550327 .yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailmsochpdefault, #yiv4283550327
.yiv4283550327aolReplacedBody div.yiv4283550327aolmail_aolmailmsochpdefault
{margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_aolmailmsohyperlink
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailmsohyperlinkfollowed {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailhtmlpreformattedchar {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailmsohyperlink {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailmsohyperlinkfollowed {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailhtmlpreformattedchar {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailaolmailmsohyperlink {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailaolmailmsohyperlinkfollowed
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailaolmailhtmlpreformattedchar
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailaolmailaolmailaolmailapple-converted-space
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailaolmailemailstyle22 {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailaolmailmsohyperlink1 {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailaolmailmsohyperlinkfollowed1
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailaolmailaolmailapple-converted-space
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailemailstyle32 {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailemailstyle34 {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailballoontextchar {}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_aolmailaolmailspelle
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailmsohyperlink1 {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailmsohyperlinkfollowed1 {}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_aolmailballoontextchar
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailemailstyle22 {}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailaolmailapple-converted-space
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailemailstyle51 {}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_aolmailemailstyle52
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailballoontextchar1 {}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_aolmailemailstyle55
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailspelle {}#yiv4283550327
.yiv4283550327aolReplacedBody p.yiv4283550327aolmail_aolmailmsonormal1,
#yiv4283550327 .yiv4283550327aolReplacedBody
li.yiv4283550327aolmail_aolmailmsonormal1, #yiv4283550327
.yiv4283550327aolReplacedBody div.yiv4283550327aolmail_aolmailmsonormal1
{margin:0in;margin-bottom:.0001pt;font-size:11.0pt;color:black;}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_aolmailmsohyperlink1
{color:blue;text-decoration:underline;}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailmsohyperlinkfollowed1
{color:purple;text-decoration:underline;}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_BalloonTextChar
{}#yiv4283550327 .yiv4283550327aolReplacedBody
p.yiv4283550327aolmail_aolmailmsoacetate1, #yiv4283550327
.yiv4283550327aolReplacedBody li.yiv4283550327aolmail_aolmailmsoacetate1,
#yiv4283550327 .yiv4283550327aolReplacedBody
div.yiv4283550327aolmail_aolmailmsoacetate1
{margin:0in;margin-bottom:.0001pt;font-size:8.0pt;}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_aolmailemailstyle32
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailemailstyle34 {}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_BalloonTextChar1
{}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailemailstyle22 {}#yiv4283550327
.yiv4283550327aolReplacedBody p.yiv4283550327aolmail_msonormal0, #yiv4283550327
.yiv4283550327aolReplacedBody li.yiv4283550327aolmail_msonormal0,
#yiv4283550327 .yiv4283550327aolReplacedBody
div.yiv4283550327aolmail_msonormal0
{margin-right:0in;margin-left:0in;font-size:11.0pt;color:black;}#yiv4283550327
.yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_aolmailaolmailapple-converted-space {}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_EmailStyle74
{color:windowtext;}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_EmailStyle75 {color:#1F497D;}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_EmailStyle76
{color:#1F497D;}#yiv4283550327 .yiv4283550327aolReplacedBody
span.yiv4283550327aolmail_EmailStyle77 {color:#1F497D;}#yiv4283550327
.yiv4283550327aolReplacedBody span.yiv4283550327aolmail_SpellE {}#yiv4283550327
.yiv4283550327aolReplacedBody .yiv4283550327aolmail_MsoChpDefault {} _filtered
#yiv4283550327 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv4283550327
.yiv4283550327aolReplacedBody div.yiv4283550327aolmail_WordSection1 {}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, PEO: 913-829-0888H: 913-397-9605C: 913-915-3137 From: karentellef via
RBASE-L [mailto:[email protected]]
Sent: Tuesday, April 25, 2017 12:44 PM
To: [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]>
To: rbase-l <[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
Javier, Javier Valencia, PEO: 913-829-0888H: 913-397-9605C: 913-915-3137 From:
karentellef via RBASE-L [mailto:[email protected]]
Sent: Monday, April 24, 2017 3:50 PM
To: [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]>
To: rbase-l <[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 #TIMEFirst, set the format to:SET TIME FORMAT HH:MM:SS.SSSSThen
at various places set various variablesSETVAR 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, PEO: 913-829-0888H: 913-397-9605C: 913-915-3137 From:
karentellef via RBASE-L [mailto:[email protected]]
Sent: Monday, April 24, 2017 1:05 PM
To: [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]>
To: rbase-l <[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]] On Behalf Of Doug Hamilton
Sent: Monday, April 24, 2017 9:53 AM
To: [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. :)
DOn 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]>
To: rbase-l <[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.
DougOn 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]>
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]>
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]>
To: rbase-l <[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 itemsCreate 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 loopSELECT 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]]
Sent: Monday, April 24, 2017 6:14 AM
To: [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
| | 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 [email protected].
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 [email protected].
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 [email protected].
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 [email protected].
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 [email protected].
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 [email protected].
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 [email protected].
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 [email protected].
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 [email protected].
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 [email protected].
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 [email protected].
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
