1. Db tables (was okKeypress)
George wrote:
>
> OK, that's a lot more than I probably needed to know..I certainly got the
> wrong idea in my head somehow...So we shoudn't have to worry about
changing
> tables or DB's...
>
George:
A quick test with a database of 52041 records shows a time to open the
database and
select the table containing those records to be 0.22 seconds. Changing to
another table,
and subsequently re-opening the first table takes consistently 0.11 seconds.
This seems fast enough. Writing your own indexing scheme is a bit of a
bother, but hey,
we used to have to do that all the time. Store the indexes as additional
tables in the database.
No need to have extra files scattered around.
Don't forget that you can store any amount of data in a record.
Suppose the product file above contains products made by 100 different
manufacturers.
Your Mfg_Index table would contain 100 records, keyed by manufacturer id,
and each record would contain a sequence of one or more product id numbers.
e.g:
key ACE, data {203453,104305,439322,493953,493955...........}
key BART, data {040323}
This makes selecting products by manufacturer a matter of one read from the
Mfg_Index table, and then a loop thru the list of product id's retrieved,
accessing
your product table once for each key on the list. One table change for each
index
consulted, plus one to return to the main table, and no extraneous reads
whatsoever,
just one for each product appearing on the retrieved list.
You can see that it would be no great problem to retrieve several indexes,
and then
combine and/or sort them before extracting the data.
Regards,
Irv
3. Re: Db tables (was okKeypress)
On Monday 06 August 2001 18:19, Chris Bensler wrote:
> Irv Mullins wrote:
> > Don't forget that you can store any amount of data in a record.
> > Suppose the product file above contains products made by 100 different
> > manufacturers.
> > Your Mfg_Index table would contain 100 records, keyed by manufacturer id,
> > and each record would contain a sequence of one or more product id
> > numbers. e.g:
> > key ACE, data {203453,104305,439322,493953,493955...........}
> > key BART, data {040323}
> >
> > This makes selecting products by manufacturer a matter of one read from
> > the Mfg_Index table, and then a loop thru the list of product id's
> > retrieved, accessing
> > your product table once for each key on the list. One table change for
> > each index
> > consulted, plus one to return to the main table, and no extraneous reads
> > whatsoever,
> > just one for each product appearing on the retrieved list.
>
> Do you mean a sepreate database for each item of each record?
> IE. db of manufacturers, containing indexes into a db of product ids
> or, do you mean a single db of manufacturers containing the product ids
> themselves?
Neither. We start with one file - Products.edb - with one table,
"Products". Each product is a record, keyed by it's SKU, and the
data attached to that record is the "rest" of the product info,
price, size, color, manufacturer, etc. etc...
Next, you create, in the same file, a new table - "Mfg_Index"
As products are added to the db, the manufacturer's id in that
product record is used as a key in the Mfg_Index table, and the
product's SKU is appended to that Mfg_Index record:
A manufacturer (ACE) who has one product - SKU 12345, would have an
entry in the Mfg_Index table:
key ACE , data {12345}
If ACE had 2 products, SKU 12345 and 45012, then the record in Mfg_Index
table would look like:
key ACE, data {12345, 45012}
The more products ACE makes, the longer the data sequence gets, but
that's not a problem, since you're still storing only one value, the SKU, not
the entire product record.
Now, to look up all ACE products, instead of reading thru 50,000 data
records searching for ACE as the manufacturer, you just look up ACE
in the Mfg_Index table, get the sequence {12345, 45012} back, and
then access (directly) those two products from the Product table.
Very fast. The initial indexing will take a while, but normally these indexes
would be updated as products are added to the database, which would
take only a fraction of a second.
By the way, loading a Table in Linux takes approximately half the time
it does in Windows, on the same pc. 0.11 seconds for a 52,000 record
table. Re-loading a table takes exactly the same amount of time as
it did on Windows, more or less proving Rob's point that the data is
cached. The cpu and memory access is the same regardless of the OS.
Regards,
Irv
