1. EDS... finding records w/matching values in data not key?
- Posted by jeremy (admin) Aug 02, 2008
- 829 views
I am wondering from some EDS gurus the best way to accomplish this task. I have a table that has the potential for 100,000 rows. The key is important as it links to many other tables, however, I must search this table some times by fields that are not the key. Further, these fields *will* have duplicate values through out the table. For instance, last name. There may be 20 people in the database with the same last name, but I must find them all.
I was thinking of making an "index" table, but that seems to be a bit more to maintain. How you you accomplish the task? I hate to do a for loop through the whole table. That does not sound efficient at all.
Jeremy
2. Re: EDS... finding records w/matching values in data not key?
- Posted by mattlewis (admin) Aug 02, 2008
- 817 views
I am wondering from some EDS gurus the best way to accomplish this task. I have a table that has the potential for 100,000 rows. The key is important as it links to many other tables, however, I must search this table some times by fields that are not the key. Further, these fields *will* have duplicate values through out the table. For instance, last name. There may be 20 people in the database with the same last name, but I must find them all.
I was thinking of making an "index" table, but that seems to be a bit more to maintain. How you you accomplish the task? I hate to do a for loop through the whole table. That does not sound efficient at all.
EuSQL uses an index table and allows an index to be created on any field (except for the entire keysubfields of keys are allowed). It uses associative lists with the indexed values as the 'key' and a sequence of the actual record keys as the values. After eu4.0 is released, it may change to use maps instead.
Matt
3. Re: EDS... finding records w/matching values in data not key?
- Posted by GeorgeWalters Aug 02, 2008
- 794 views
Why don't you just do an SQL statement like.
Select * from dataBase where lastName = 'somename';
MySql can return this faster that you could read the records and find them.
4. Re: EDS... finding records w/matching values in data not key?
- Posted by jeremy (admin) Aug 02, 2008
- 801 views
Why don't you just do an SQL statement like.
Select * from dataBase where lastName = 'somename'
MySql can return this faster that you could read the records and find them.
Due to the simplistic nature of the application, I wanted to avoid using a SQL server. I normally use PostgreSQL for all of my advanced applications as I use triggers, store procedures and such.
Jeremy
5. Re: EDS... finding records w/matching values in data not key?
- Posted by jeremy (admin) Aug 02, 2008
- 812 views
EuSQL uses an index table and allows an index to be created on any field (except for the entire keysubfields of keys are allowed). It uses associative lists with the indexed values as the 'key' and a sequence of the actual record keys as the values. After eu4.0 is released, it may change to use maps instead.
Sounds like I should just go the index route and use something possibly like:
Table: idx_last_name
Key: last_name
Data: { person_key_1, person_key_2, ... }
Not having much experience with EDS, how fast is it compared to, say SQLite? I am much more at home in SQL than EDS, however, I wish to make it simple, small and as much Euphoria as possible. How much overhead does EuSQL add on?
Jeremy
6. Re: EDS... finding records w/matching values in data not key?
- Posted by jacquesd Aug 02, 2008
- 807 views
EuSQL uses an index table and allows an index to be created on any field (except for the entire keysubfields of keys are allowed). It uses associative lists with the indexed values as the 'key' and a sequence of the actual record keys as the values. After eu4.0 is released, it may change to use maps instead.
Sounds like I should just go the index route and use something possibly like:
Table: idx_last_name
Key: last_name
Data: { person_key_1, person_key_2, ... }
Not having much experience with EDS, how fast is it compared to, say SQLite? I am much more at home in SQL than EDS, however, I wish to make it simple, small and as much Euphoria as possible. How much overhead does EuSQL add on?
Jeremy
In your data you better keep the records numbers instead of keys, that way you
have direct access to data
Data: { rec_number1, rec_number2, ...}
if you keep the keys in your index table you have to find record number before accessing data.
-- with index of keys integer rn rn = db_find_key(key) -- key gotten from index table data = db_record_data(rn) -- with index of rn you save 1 step data = deb_record_data(rn)-- rn gotten from index table
Jacques
7. Re: EDS... finding records w/matching values in data not key?
- Posted by mattlewis (admin) Aug 02, 2008
- 829 views
In your data you better keep the records numbers instead of keys, that way you
have direct access to data
Data: { rec_number1, rec_number2, ...}
if you keep the keys in your index table you have to find record number before accessing data.
If your data doesn't change much, this would definitely help access times, especially for a 100K record table. But you'd have more work to keep them up to date if you ever insert or update the table. The keys always work, and you only need to update for those particular records. So it just depends on what you're going to do.
Matt
8. Re: EDS... finding records w/matching values in data not key?
- Posted by mattlewis (admin) Aug 02, 2008
- 808 views
How much overhead does EuSQL add on?
I think that it's fairly light. You can prepare your statements and use parameters. I've spent a fair amount of time improving the speed for joins. If you have to do any table joining, it will probably be faster than whatever you're thinking about doing, assuming you set up good indices.
If you don't have any joins, it might not be worth it. But on the other hand, there's wxEDB, which can be useful during development, too. The dialect of SQL is somewhat limited, so don't expect to do a lot of fancy stuff (e.g., subqueries).
Matt
9. Re: EDS... finding records w/matching values in data not key?
- Posted by euphoric (admin) Aug 02, 2008
- 840 views
EuSQL uses an index table...
Not having much experience with EDS, how fast is it compared to, say SQLite? I am much more at home in SQL than EDS, however, I wish to make it simple, small and as much Euphoria as possible. How much overhead does EuSQL add on?
If you want to use EDS and SQL, use EuSQL. It is definitely fast enough for simple applications. I have never really tested its limits.
Jeremy, you could always set up a test platform for SQL databases and compare speeds. 
