[RBASE-L] - Views, Networks and Speed Issues

[Patrick Murphy]

RBasers:   I thought the group might find this interesting.  My client runs RBase at several remote locations around a 8,000 acre real estate development.  Locations are connected via T1 lines.  Each user has RBase loaded on their C: drive and the scratch file is also located there.    They recently advised that RBase was running slow on owner name lookups.  The lookup uses a Choose statement and a View and the "Containing" relationship.  The View data comes from a 4,400 record, 217 column/field Owner table and a 4,500 record, 8 column/field Related table.   The view combines 17 fields from the Owner table with 8 fields from the Related table.   As a good consultant, I immediately blamed their network but told them I would check out how RBase is handling this this task.  (I do plan to have them check several network settings and ideas I mined from this group.)   At a remote location, it was taking 4 minutes 20 seconds to pull up a list of names containing "Smith".  It took 4 seconds to pull up an individual record using any owner's unique number.  The 4 minutes was totally unacceptable, as you can guess.   Since the Owner table had increased in recent months from 150 columns to the 217, I wanted to see what impact this had.  These results testing at a remote location were:   1 min 30 sec from an 6-month old version of the database with the 150 column Owner table (rows similar) 4 min 20 sec from the current database and the 217 column Owner table   While there may have been some other factors at work, it seemed that the extra columns/fields contributed to the slowdown they were experiencing.   So I decided to create a Name table with the 8 fields used by the Choose statement.  This result was:   4 seconds to pull up all the Smith records (about 15 records)   A very acceptable result.  Sooo, I've decided to refresh the data in that 8-field Name table every day when the first user logs on via a command in Rbase.dat.  If they are located at the server location, then that happens within a few seconds, at a remote location it will take several minutes (see below for test results).    Unfortunately, I don't see any alternative, but am always open to suggestions from this knowledgeable group!   Conclusion: Using a View involving large tables across a remote network can be very time consuming!   Patrick Tucson, AZ   Footnote: Home testing   To try and replicate the effect of the remote network, I set up a test on my home 802.11b network.  This proved satisfactory for testing alternative coding and structure schemes.  Starting RBase on a laptop and then accessing the computer containing the database resulting in these times:   8.5 minutes - Choose statement using View 4 seconds - Choose statement using 8-field Name table.   Refreshing 8-field Name table by deleting existing rows and loading again:   Single index on 8-field table: 11 minutes - Append View data to Name table (takes 2 seconds on local computer) 8.5 minutes - Export data from View to file and Load into Name table (takes 1 second on local computer)   No index on 8-field table: 8.5 minutes - Append View data to Name table (takes 2 seconds on local computer) 4 minutes - Export data from View to file and Load into Name table (takes 1 second on local computer)   Conclusion: Reloading the data into the 8-field Name table will be very quick by a user in the office where the network server is located, but will delay startup by a remote user by 4 minutes.