Larry, Wow, I really am surprised at the speed when I created indexes!.
Incidentally I also noticed that case# does exist in each table, and the form that was created seems to page through records correctly, so implicit joins explain that. I am glad you warned me about differing data from table to table causing data to disappear...I wouldn't have figured that out so quickly. Thanks! On Fri, Jul 18, 2008 at 9:37 AM, Lawrence Lustig <[EMAIL PROTECTED]> wrote: > << > What I've also found is that there are no indexes, fk, pk to speak of, > so I have no clue as to how R:Base decides on table relationships, > since there are no views, simply a form that pulls from these tables >>> > > Well, there's your problem -- no indexes. Create indexes on the related > columns and you'll start flying. > > For multi-table forms, R:Base will perform an implicit join using ALL > IDENTICALLY NAMED columns in the tables on the form. In general, as a matter > of database design, no more than one column (the identifier) should be shared > between any two tables. A common mistake in R:Base is to use the same column > name in header and detail tables but to have different data there -- that > causes rows to disappear on forms. This applies to forms only, regular views > still use the explicit joins you write into the CREATE VIEW statement. > > If you're used to Access you may not realize that it automatically creates > indexes on many columns without asking you. This is one reason why it > appears fast on small databases but begins to bog down on anything larger > than 50 or 100 megabytes. R:Base (in common with most other engines) expects > you to explicitly declare indexes. > > Because R:Base is really very, very fast on non-indexed searches the problem > with the missing indexes was hidden by R:Base's high performance until you > began to get into the 100K record range. > > A couple of CREATE INDEX commands from the R> prompt (or the same operations > performed in RBDEFINE) will solve your speed problem with less than 5 minutes > of work. > -- > Larry > > > -- John Croson [EMAIL PROTECTED] http://pcnorb.blogspot.com/ http://pcnorb.homelinux.org/
