----- Original Message -----
Sent: Sunday, November 23, 2003 2:31
PM
Subject: [RBASE-L] - Views, Networks and
Speed Issues
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.
