Use the following techniques in your command files and EEPs to speed up processing:
•Group similar commands
R:BASE loads into memory the information needed to process each command. If the command is already in memory, R:BASE does not need to read the disk again. For example, try grouping separate SET VARIABLE commands into one SET VARIABLE whenever possible.
When UPDATE commands are used for the same table using the same WHERE Clause, the commands should also be combined.
UPDATE Donors SET Contrib = .vAmount WHERE DonorID = .vDonorID
UPDATE Donors SET Renew = .vRenew WHERE DonorID = .vDonorID
UPDATE Donors SET GiftDate = .vDate WHERE DonorID = .vDonorID
UPDATE Donors SET Gift = .vNewGift WHERE DonorID = .vDonorID
The following command combines the four UPDATE commands into one.
UPDATE Donors SET Contrib = .vAmount, Renew = .vRenew, GiftDate = .vDate, Gift = .vNewGift WHERE DonorID = .vDonorID
•Minimize disk access
Minimize disk access by using Custom EEPs, which run from the computer's memory, and by combining small command files into command blocks within a procedure file.
•Use WHILE loops
Use WHILE loops instead of IF structures or GOTO processing whenever possible. All the commands contained within a WHILE loop are completely read and are retained in memory as long as the WHILE loop is processing. Use BREAK or change the condition for a natural exit rather than using GOTO to exit from a WHILE loop.
•Replace SET VARIABLE with SELECT INTO for Table Lookups
When assigning column values to one or more variables, it is better to use SELECT ... INTO rather than SET VARIABLE. SELECT INTO is the SQL compliant command when capturing table data into variables, and allows for specifying an INDICATOR variable which indicates if a column value is NULL.
SET VARIABLE syntax:
SET VARIABLE vPhone = EmpPhone IN Employee WHERE EmpLastName = 'Smith' AND EmpFirstName = 'George'
SELECT Syntax:
SELECT EmpPhone INTO vPhone INDICATOR ivPhone FROM Employee WHERE EmpLastName = 'Smith' AND EmpFirstName = 'George'
•Use forward GOTO searches
Use forward GOTO searches whenever possible. When R:BASE encounters a GOTO, it performs a forward search for the matching label more efficiently than by seeking it in memory, even though the labels are retained internally.
•Predefine variables
Make sure that the data type of each variable is unambiguous by explicitly assigning the data type when the variable is defined.
•Reduce redundancy
Eliminate duplication of command sections where possible. If you have a set of commands duplicated in several places within a form, use a "Custom Form Action" which can be called upon at any place in the form. If you have a set of commands duplicated in several places within the entire application, use stored procedures, which can be called upon from almost any place within the application. Like Custom EEPs, stored procedures run from the computer's memory.
•Eliminate rules checking
Set RULES OFF when not needed for data verification. This saves time by preventing R:BASE from checking the data for rule violations. It may also be possible to eliminate the rule with a constraint.
•Experiment with Manual Table-Order Optimization
By default, R:BASE uses its own internal algorithm optimizer that determines the best order for joining tables. You can turn off the automatic optimizer using the MANOPT setting. MANOPT (default: OFF) disables the automatic table-order optimization that R:BASE performs when running queries. This gives maximum control over the order in which columns and tables are assembled in response to a query. With MANOPT set to ON, R:BASE uses the order of the tables in the FROM clause and the order of the columns in the column list of the SELECT clause to construct the query.
