Re: Finding data in Euphoria databases
Julio C. Galaret Viera wrote:
>
> ZNorQ wrote:
> >
> > I have plans to make use of Euphoria's database.e (EuDB) archive, and I've
> > done some minor testing, but I haven't gotten around to really stress test
> > it.
> >
> > But I've come as far as to see how it is build up - using 'keys' and 'data'
> > sections for each record.
> >
> > Generally I create a key by using the db_table_size()+1 function, usually by
> > adding some kind of prefix - like "COMP" for "COMPANIES", etc., but I found
> > out that the database only have a built in search function on the key, and
> > not the data itself.
>
> If you create keys that way and at a given moment delete a record, the next
> key you attempt to create will fail with DB_EXISTS_ALREADY.
>
Yeah, I know, so for now I'm not deleting records, but rather flagging them
deleted. I was thinking of maybe using something like
NewKey = db_record_key(db_table_size()) + 1
(of course, it isn't as simple as that, as NewKey would be a sequence, but
it's just to show what my intentions are - to get the keypart of the last
record and add one to that.. As far as I can see, this would always give me
the next id. Unless the records in a table is sorted on something other than
record id / key, this should work fine.. Am I right?
>
> >
> > This makes me think that in order to find information effectively in typical
> > fields where people would search for information should really be part of
> > the key..? AFAIK, keys can't be changed after it's creation (which I see as
> > a good thing), which again would make it difficult to use since this
> > information can't be corrected if any punching mistakes where made.
>
> You will have to delete the old record and insert the updated one.
>
Jepp, and I really don't like that solution...
> >
> > The only way I see to search for information in records data section now,
> > is to loop through each record and 'find'ing or 'match'ing each data section
> > with what-ever one would need to search for.
> >
> > }}}
<eucode>
> > atom SearchIdx
> > for cntrec = 1 to db_table_size() do
> > if match("rds", db_record_data())
> > then SearchIdx = cnt
> > exit
> > end if
> > end for
> > </eucode>
{{{
> >
> > This is quite slow. Of course, one could limit the search by looking at
> > the specific sequence element (field) taken from db_record_data() so that
> > it dosen't search through the whole record, but it would still require the
> > code to read the whole record before searching for the information.
> >
>
> Right, too slow.
And don't I know it... Did a test on a million records - had to cancel that, as
I had a better chance of dying of old age than finding the data. (Well, it took
abit long anyway.) Finding the key part didn't take a second though!
(Yes, yes, I know - a million records is a large amount of data... Might'a over-
done that abit.. :) )
>
> > Question 1;
> > Anyone got any experience using large amounts of data? How effective is
> > EuDB in finding, inserting and deleting records?
>
>
> I have very large databases and for me are fine. Since my key is only one
> atom,
> finding, inserting and deleting is ok.
> What is really a pain for is when you delete a large number of records all at
> once, so my option is to copy the records I want to keep into a new database,
> skipping those I would delete. But again this is a painfull process:
> rebuilding
> that way a database with 150.000 records takes me 2 hours on a P4 2.66!
>
Atom as key; You say finding is ok using just an atom as a key part, so this
means that the key is the only field you need to find what you want?
Mass deletion; You do this manually, or using some sort of flagging of the
records
you want to delete?
>
> >
> > Question 2;
> > Finding; What solution do you use?
> >
> > Kenneth
>
> It depends on what you want to do. If you are doing very complex queries
> (perhaps
> simultaneously), change to another db system (MySQL, SQLite, etc).
>
> If your queries are not very complex you can design some type of relational
> database. Split one table into several. Let say you have a table with
> information
> about movies. If you know people will search for titles, create another table
> with movie title as a key and the key from the first table where you store all
> the information about the movie, as the data. That way you can create as many
> tables as indexes you need with a reference to key of the main table.
>
> JG
Sounds good, already received a link to EuSQLite - which I'll have a look
through.
Thanks, JG for your valuable feedback.
Kenneth
|
Not Categorized, Please Help
|
|
