""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   GET DRAMATIC SPEED INCREASES
   """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   PRODUCT   :  R:BASE                  VERSION      :  3.1
   CATEGORY  :  PROGRAMMING             SUBCATEGORY  :  TOOLS
   """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
   By modifying your R:BASE 3.1 programs, you may be able to attain
   phenomenal increases in speed.
 
 
   The Key to Speed
   """"""""""""""""
   The key to faster applications is a willingness to experiment. Each
   new attempt brings new knowledge. That's how we discovered the
   following tips.
 
 
   Reduce Code - Think Global
   """"""""""""""""""""""""""
   Reduce the code by using a global command like INSERT, UPDATE, SELECT,
   or CREATE VIEW instead of using a program that goes row by row through
   the data. You may be able to replace an entire program with a single
   global command. Global commands operate on all the rows in a single
   pass, so they're faster.
 
   If you must use a program, you can still reduce the code by taking
   full advantage of the SQL SELECT clause in the SELECT, INSERT, CREATE
   VIEW, and DECLARE CURSOR commands. A single SELECT clause can group
   rows with GROUP BY, summarize data with SELECT functions, append
   values together with UNION SELECT, and join tables together.
 
   You may not need a program. For example, a single INSERT command can
   group by department and employee, compute a count and sum, and save
   the result in another table:
 
     INSERT INTO summary +
       (deptid, empid, totnum, sumcosts) +
       SELECT deptid, empid, (COUNT(*)), +
         (SUM(costs)) FROM depts +
         GROUP BY deptid, empid
 
 
   Optimize DECLARE CURSOR
   """""""""""""""""""""""
   When you must go row by row with a DECLARE CURSOR structure, optimize
   the DECLARE CURSOR command to do as much of the work as possible. Even
   if you fold just one of the WHILE-loop commands into the SELECT clause
   on the DECLARE CURSOR, you'll eliminate hundreds of commands - for
   example, if you have 1,000 rows, you'll eliminate 1,000 commands. See
   "Get More Out of a Single DECLARE CURSOR Command" in this issue for an
   example.
 
 
   Correct DECLARE CURSOR Format
   """""""""""""""""""""""""""""
   Never put DECLARE CURSOR in a WHILE loop. To use a multi-level DECLARE
   CURSOR structure, put the OPEN command in the WHILE loop, and put all
   DECLARE CURSORs above the WHILE loop, as in this example:
 
     SET VAR vcustid INTEGER = 0
     DECLARE c1 CURSOR FOR +
       SELECT custid FROM customer +
       ORDER BY custid
     DECLARE c2 CURSOR FOR +
       SELECT transid FROM transmaster +
       WHERE custid = .vcustid
     OPEN c1
     FETCH c1 INTO vcustid IND ivcustid
     WHILE SQLCODE <> 100 THEN
       OPEN c2
       FETCH c2 INTO vtransid
       WHILE SQLCODE <> 100 THEN
         *( ...Commands using VTRANSID...)
         FETCH c2 INTO vtransid
       ENDWHILE
       CLOSE c2
       *( ...Commands using VCUSTID...)
       FETCH c1 INTO vcustid IND ivcustid
     ENDWHILE
     DROP CURSOR c1
     DROP CURSOR c2
 
   Make sure all your multi-level DECLARE CURSORs are built like this.
 
 
   Add SQL to Converted Applications
   """""""""""""""""""""""""""""""""
   If you converted an application from an earlier version of R:BASE,
   optimize it. The converter that came with your new R:BASE version
   didn't optimize your code. To speed up your converted applications,
   eliminate SET POINTER routines. Replace them with global SQL commands
   if you can. If you must continue to go row by row, convert all SET
   POINTERs and R:BASE 2.11 DECLARE CURSORs to R:BASE 3.1 DECLARE CURSOR
   structures.
 
 
   Use Indexes to Find a Few Rows
   """"""""""""""""""""""""""""""
   Indexes can quickly find a few rows in a table that has thousands of
   rows. For example, to use a form to edit five rows in a table (CUST)
   that has 6,000 rows, you might use this command:
 
     EDIT USING custform WHERE cid IN (3557, 3560, 3472, 3545, 3556)
 
   But R:BASE doesn't use indexes with the IN or OR operator. Instead,
   R:BASE searches row by row through the table sequentially. If the
   table had 100 rows and you wanted 10 of them, a sequential search
   would be fast, so you'd want to use the IN operator. But if you only
   want a few rows out of thousands, an indexed search is faster, so use
   UNION SELECT to find the rows:
 
     DECLARE c1 CURSOR FOR +
     SELECT cid FROM cust WHERE cid = 3557 +
     UNI SEL cid FROM cust WHERE cid = 3560 +
     UNI SEL cid FROM cust WHERE cid = 3472 +
     UNI SEL cid FROM cust WHERE cid = 3545 +
     UNI SEL cid FROM cust WHERE cid = 3556
     OPEN c1
     FETCH c1 INTO vcid
     WHILE SQLCODE <> 100 THEN
       EDIT USING custform WHERE cid = .vcid
       FETCH c1 INTO vcid
     ENDWHILE
     DROP CURSOR c1
 
   This may be up to 10 times faster, but there is a disadvantage; you
   can't press [F8] and [F7] to move from row to row. You must exit from
   the form before R:BASE will bring up the next row. But if you can live
   with this limitation, you may be able to get dramatic speed increases
   (up to 10 times faster) with UNION SELECT.