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

[J. Stephen Wills]

Couple of questions for Patrick, Et Al. ...   Do I read from your message that you allow a user-session on the server box?  It's my impression that this tends to slow down performance on all other clients, and not just in RBase.   Also, may I ask, why so many fields?  As I recall, RBase, while pretty fast "across the wire", is not truly client-server in its approach to delivering data across the wire.  That is, it pumps the entire global-set down the wire to the locally running RB engine, then that client-side session process everything, ultimately delivering the requested result-set.  Also, with regard to view processing, I'd suspect that this is also true.  Given this (I think it's still true), would you be able to monitor any network statistics between the server-side and the client-side, especially as you test different approaches to reduce response-time?   Now, some possible thoughts ...   My wires, at least for now, are all L(ocal)AN stuff, but my boxes are fairly slow and some of my wires still aren't 100base-T and/or I have improper segmentation via hubs, etc.  So, my total throughput (net & boxes) ain't all that fast.  As such, I've been experimenting with different approaches to architecting a kind of "partitioning" of my client-side tables, at least where quick response-time is (in my case) CRITICAL, I think along the lines of what David is talking about.  If I discover (or just have happened to come upon) anything new and helpful, I'd be happy to share it.   For example, although a great deal of what my users need, simplicity and SPEED, is handled pretty darn well, all within RBWINv6.5++, I have one UPDATE that reaches across the network and is interminably slow - well, up to 10 seconds or so.  However, it is really a very important piece for real-time quality control.  I've been doing it in an ad hoc/batch fashion until now, but that approach is far from perfect.  I'll be experimenting with different ways to execute this UPDATE, but I won't commit to an implementation until I get a SUB-SECOND response-time.   (I've found that an old programming principle of testing all implementations on the slowest box you got has proven a pretty stern, but beneficial, task-master.  We actually still have a P133 with a slow HDD and it's my "Vince Lombardi/Knute Rockne/Lou Holtz" keep-the-coach happy challenge.)   Don't know if any of this actually helps, but maybe it gives you some food for thought.   Thanks, Steve in Memphis   ----- Original Message ----- From: David M. Blocker To: RBASE-L Mailing List Sent: Monday, November 24, 2003 8:19 AM Subject: [RBASE-L] - Re: Views, Networks and Speed Issues Patrick   1.  Try doing this in a TEMP table created on each workstation 2.  Load the table, 3. THEN create the index   See it that helps at all   David Blocker [EMAIL PROTECTED] 781-784-1919 Fax: 781-784-1860 Cell: 339-206-0261 ----- Original Message ----- From: Patrick Murphy To: RBASE-L Mailing List 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.