Jim L.,
My experience has been just the opposite to yours. I was always
under the impression that WHILE loops were the way to go in most
cases. See Razzak's FROM THE EDGE article on how to optimize
WHILE code. There may be some cases where a WHILE construct is
not appropriate or even possible. Especially if you have a extremely
large amout of code between WHILE and ENDWHILE.
My programming experience started out on an IBM 1401 way back
in 1965. You had like 16k of memory and at my college we only
had punch card input. No disk drives. No tape drives. Only a
card reader/punch, the CPU, and a printer. You programmed in
SPS the assembly language for that computer. You sure learned
to be efficent. Later on (1973) after graduation and several
jobs. I learned COBOL on an IBM 360 model 40. I think it had
someting like 64k or 128k of memory. That job involve accessing
data in the Software Internationl General Ledger system database.
That database was based on the IBM Bill of Materials program
(BOMP) the Account Master file was based on an Item Master concempt
and the relationship file allowed you to maintain summary account
information in realtime. But to retrieve this information you
had to do a lot of "address" chaseing. This taught you to program
optimized code.
For some 27 year I was involve in Installing, maintaining, and
using Financial Information systems. Over those years I learned
several programming guidelines. Every programming language I
ever used had the equivalent of WHILE loops, IF/SELECT statements,
and GOTO constructs. From this I learned several guidlines/rules:
* Properly align and predefine all data types. If you use numeric
data maintain decimal point alingnment compatiblity. This was
expecially important in compiled languages.
* Minimize data type conversions. If you must do a lot of computations
with dissimular data types. Do a one time conversion prior to
calculations, then alfter all computations completed covert answers
as appropriate.
* When you have a loop do only what you have to inside the loop.
Remove as much as possible to before/after the loop.
* When you have a series of tests IF, SWITCH, IN know your data!!
Where possible program in tests with the most likely to occur
first. For example in the Software General Ledger each account
had a type code value:
1 = Asset
2 = Liability/Equity
3 = Revenue
4 = Expense
Inexperienced programmers might code the following
SWITCH (.AcctType)
CASE 1; some actions; BREAK
CASE 2; some actions; BREAK
CASE 3; some actions; BREAK
CASE 4; some actions; BREAK
DEFAULT; some action; BREAK
ENDSW
But the following breakdown of accouts as a Percent of total accounts
might be
1 = Asset 14 %
2 = Liability/Equity 16 %
3 = Revenue 20 %
4 = Expense 50 %
The optimal sequence for the above case statement would be 4,
3, 2, 1
* Specific to R:Base optimize SELECT statements to do more of
the work. For Example you could do the following:
Original:
SELECT FirstName, LastName FROM ... INTO
SET VAR vFullName = (.vFirstName & .vLastName)
etc.
Better:
SELECT (FirstName & LastName) as FullName FROM ... INTO
* Use more VIEWS. Let them do more of the work as above example.
They take minimal room in the database but can simplify programming
and improve efficency. Especially if you retrieve only what you
need. For reports this could make a big difference especially
if you have a lot of lookups or calculations. By creating a specialized
VIEW with the SELECT statement do the lookups and calculations
the REPORT driver has less work to do for each line printed.
* When you define a VIEW always use the CREATE VIEW viewname
(collist) AS SELECT syntax. And speaking of views avoid at all
costs SELECT * FROM syntax. always specify the columns you want.
This avoids all kinds of hidden gotyas if you make schema changes.
--
Jim Bentley
American Celiac Society
[EMAIL PROTECTED] - email
(973) 325-8837 voice
(973) 669-8808 Fax
---- Jim Limburg <[EMAIL PROTECTED]> wrote:
> Well I be bugg-eyed said da enlightned grasshopper...
>
> I see, I see, and you are oh so right, but I have
> a point or two.. I have heard over my short existence within
> the rbase community that if at all possible to avoid the use
> of WHILE loops because of things having to be optimized for
>
> them to be used without throwing problems and such, and that
> it's better to test some things in advance, especially in
> code that going to be used for Stored Procedures. Being a
> bit still a coding scardy-cat ... I like to be on the safe
> side when I'm treadin' new territory...
>
> But, I appreciate the advice, and since I deem you as one of
> those cream of the crop coders, I will highly consider your
> suggestion..
>
> P.S. Am I right in thinking that optimization for While loops
> is more along the thoughts of pre-declaring variables, and
> stream lining code to make what is happening inside while
> loops (especially any that's going to do a lot of loop-dy-loops)
> so it only does what it has to do to get the job accomplished?
>
> Thanks
> Jim Limburg
>
>
> --- "James (Jim) Bentley" <[EMAIL PROTECTED]> wrote:
> > Jim,
> > Just a point of interest. Your "IF pkn_inputtext IS NULL"
> test
> > is not needed. The value 0 (zero) is by "SET VAR pkn_stringlen
> > = (SLEN(.pkn_inputtext))" if "pkn_inputtext" is NULL. In
> such
> > a case the WHILE test will not execute since "1 <= 0" is not
> true.
> > --
> > Jim Bentley
> > American Celiac Society
> > [EMAIL PROTECTED] - email
> > (973) 325-8837 voice
> > (973) 669-8808 Fax
> >
> >
> > ---- Jim Limburg <[EMAIL PROTECTED]> wrote:
> > > Bill, Sami
> > >
> > > I have been watching this thread with some interest.. I
> have
> > > yet to
> > > fool with stored procedures much, so I decided to look into
> > > what
> > > you are talking about here... WOW... this is cool.
> > >
> > > I debugged and modified your code like this:
> > >
> > > *(
> > > Return same string with all non-numeric characters removed
> > >
> > > (CALL KeepNum(TextValue))
> > >
> > > To store this:
> > > SET PROC KeepNum LOCK ON
> > > PUT KeepNum.prc AS KeepNum +
> > > pKN_InputText TEST (48) +
> > > RETURN TEXT (48) +
> > > 'Strip non-numeric characters from a string'
> > > )
> > > CLEAR VAR MICRORIM_RETURN
> > >
> > > SET VAR pkn_returntext TEXT = NULL
> > > IF pkn_inputtext IS NULL THEN
> > > GOTO lbexit
> > > ELSE
> > > SET VAR pkn_stringlen = (SLEN(.pkn_inputtext))
> > > SET VAR pkn_counter = 1
> > > WHILE pkn_counter <= pkn_stringlen THEN
> > > SET VAR pkn_char = (SGET(.pkn_inputtext, 1, pkn_counter))
> > > IF '0123456789' CONTAINS .pkn_char THEN
> > > SET VAR pkn_returntext = (.pkn_returntext + .pkn_char)
> > > ENDIF
> > > SET VAR pkn_counter = (.pkn_counter + 1)
> > > ENDWHILE
> > > ENDIF
> > > LABEL lbexit
> > > CLEAR VAR pkn_counter, pkn_stringlen, pkn_char
> > >
> > > RETURN .pkn_returntext
> > >
> > >
> > > I hope you don't mind.. I think I will start using this
> a lot
> > > more.
> > >
> > > Thank you for a great lesson
> > >
> > > Jim Limburg
> > >
> > >
> > > --- Bill Downall <[EMAIL PROTECTED]> wrote:
> > > > Sami,
> > > >
> > > > My apologies for expecting you to do the work for me.
> > > >
> > > > Here. Your job is the debugging. I didn't do that part
> yet.
> > > >
> > > > With this, you should be able to:
> > > >
> > > > SELECT COUNT(*) INTO vcount FROM table +
> > > > WHERE (call KeepNum(columnname)) = .vtest
> > > >
> > > > or even:
> > > >
> > > > UPDATE tablename +
> > > > SET columnname = (CALL KeepNum(columnname)) +
> > > > WHERE (columnname <> (CALL KeepNum(columnname))
> > > >
> > > > Bill
> > > > =======================================
> > > > *(
> > > > Return same string with all non-numeric characters
> removed
> > > >
> > > > (CALL KeepNum(TextValue))
> > > >
> > > > To store this:
> > > > SET PROC KeepNum LOCK ON
> > > > PUT KeepNum.prc AS KeepNum +
> > > > pKN_InputText TEST (48) +
> > > > RETURN TEXT (48) +
> > > > 'Strip non-numeric characters from a string'
> > > > )
> > > > CLEAR VAR Microrim_Return
> > > >
> > > > SET VAR pKN_ReturnText TEXT = NULL
> > > > IF pKN_InputText IS NULL THEN
> > > > goto lbExit
> > > >
> > > > SET VAR pKN_StringLen = (SLEN(.pKN_InputText))
> > > > SET VAR pKN_Counter = 1
> > > > WHILE pKN_Counter <= pKN_StringLen THEN
> > > > SET VAR pKN_Char = (SGET(.pKN_InputText, 1, pKN_Counter))
> > > > IF '0123456789' CONTAINS .pKN_Char THEN
> > > > SET VAR pkn_ReturnText = (.pKN_ReturnText + .pKN_Char)
> > > > ENDIF
> > > > SET VAR pKN_Counter = (.pKN_Counter + 1)
> > > > ENDWHILE
> > > >
> > > > LABEL lbExit
> > > > CLEAR VAR pKN_Counter, pKN_StringLen, pKN_Char
> > > >
> > > > RETURN .pKN_REturnText
> > > >
> > > > ======================
> > > >
> > > >
> > > > On Tue, 11 Jun 2002 10:50:06 -0500, Sami Aaron wrote:
> > > >
> > > > >I want to be able to issue the command:
> > > > >
> > > > >SELECT COUNT(*) INTO vcount FROM table WHERE (format
> > > > (columnname,something,
> > > > >something)) = .vtest.
> > > > >
> > > > >example: SELECT COUNT(*) INTO vcount FROM table WHERE
>
> > > > 00051334944 =
> > > > >00051334944
> > > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > ================================================
> > > > TO SEE MESSAGE POSTING GUIDELINES:
> > > > Send a plain text email to [EMAIL PROTECTED]
> > > > In the message body, put just two words: INTRO rbase-l
> > > > ================================================
> > > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > > > In the message body, put just two words: UNSUBSCRIBE rbase-l
> > > > ================================================
> > > > TO SEARCH ARCHIVES:
> > > > http://www.mail-archive.com/rbase-l%40sonetmail.com/
> > >
> > >
> > > __________________________________________________
> > > Do You Yahoo!?
> > > Yahoo! - Official partner of 2002 FIFA World Cup
> > > http://fifaworldcup.yahoo.com
> > > ================================================
> > > TO SEE MESSAGE POSTING GUIDELINES:
> > > Send a plain text email to [EMAIL PROTECTED]
> > > In the message body, put just two words: INTRO rbase-l
> > > ================================================
> > > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > > In the message body, put just two words: UNSUBSCRIBE rbase-l
> > > ================================================
> > > TO SEARCH ARCHIVES:
> > > http://www.mail-archive.com/rbase-l%40sonetmail.com/
> > >
> > ================================================
> > TO SEE MESSAGE POSTING GUIDELINES:
> > Send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: INTRO rbase-l
> > ================================================
> > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: UNSUBSCRIBE rbase-l
> > ================================================
> > TO SEARCH ARCHIVES:
> > http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
