1. Database Battle to the Death
- Posted by euphoric (admin) Oct 09, 2010
- 1503 views
I'm needing a light weight yet robust database, and the contenders are:
Firebird (Euphoria wrapper by Steve Baxter)
SQLite (Euphoria wrapper, in particular the one from ChrisB)
I would use EDS, but I'm probably going to have tens of millions of records. Maybe more. It's my understanding that EDS is not rated for that many records. I think it would also be slower than the above options, but I'm not completely confident in that regard.
Any suggestions, thoughts, or opinions?
2. Re: Database Battle to the Death
- Posted by mattlewis (admin) Oct 09, 2010
- 1504 views
I'm needing a light weight yet robust database, and the contenders are:
Firebird (Euphoria wrapper by Steve Baxter)
SQLite (Euphoria wrapper, in particular the one from ChrisB)
Personally, I'd stay away from Firebird. I'd go with SQLite, assuming this is a DB that goes with some sort of a desktop app? If server based, I'd say MySQL.
Matt
3. Re: Database Battle to the Death
- Posted by DerekParnell (admin) Oct 09, 2010
- 1486 views
I'm needing a light weight yet robust database, and the contenders are:
Firebird (Euphoria wrapper by Steve Baxter)
SQLite (Euphoria wrapper, in particular the one from ChrisB)
I would use EDS, but I'm probably going to have tens of millions of records. Maybe more. It's my understanding that EDS is not rated for that many records. I think it would also be slower than the above options, but I'm not completely confident in that regard.
Any suggestions, thoughts, or opinions?
EDS is suitable for this many records, but it depends on how they are going to be used. If the database is only going to be opened by a single process at a time, and the database is local to that process's machine, and you have only 1, 2 or 3 types of tables in the database, and you don't need multiple indexes on any table then EDS would be ok. Otherwise consider mySQL.
4. Re: Database Battle to the Death
- Posted by euphoric (admin) Oct 09, 2010
- 1462 views
Personally, I'd stay away from Firebird. I'd go with SQLite, assuming this is a DB that goes with some sort of a desktop app? If server based, I'd say MySQL.
EDS is suitable for this many records, but it depends on how they are going to be used. If the database is only going to be opened by a single process at a time, and the database is local to that process's machine, and you have only 1, 2 or 3 types of tables in the database, and you don't need multiple indexes on any table then EDS would be ok. Otherwise consider mySQL.
Yes, desktop app, not server based. Single process and local. Two tables (so far; this might change).
Thanks for the input guys. :)
5. Re: Database Battle to the Death
- Posted by Bellthorpe Oct 09, 2010
- 1490 views
Personally, I'd stay away from Firebird. I'd go with SQLite, assuming this is a DB that goes with some sort of a desktop app? If server based, I'd say MySQL.
I'm not arguing with you ... but why would you recommend staying away from Firebird?
6. Re: Database Battle to the Death
- Posted by ChrisB (moderator) Oct 09, 2010
- 1514 views
I'm needing a light weight yet robust database, and the contenders are:
Firebird (Euphoria wrapper by Steve Baxter)
SQLite (Euphoria wrapper, in particular the one from ChrisB)
I would use EDS, but I'm probably going to have tens of millions of records. Maybe more. It's my understanding that EDS is not rated for that many records. I think it would also be slower than the above options, but I'm not completely confident in that regard.
Any suggestions, thoughts, or opinions?
Hi
Its not by me, its originally by Ray Smith, I just took it over, collate any updates, and keep it at http://eusqlite.wikispaces.com/
I use it for my vet database. Its really simple to use, uses industry standard sql (or at least a large subset), its really quick, is regularly updated, and maintained, has a huge userbase. However, I've done a few speed trials, and surprisingly EDS compares quite favourably - surprisingly because sqlite uses an external shared library, and I would have thought that alone would have made it quicker.
Chris
Forked into: Using SQLite with Euphoria 4.x
7. Re: Database Battle to the Death
- Posted by mattlewis (admin) Oct 09, 2010
- 1457 views
Personally, I'd stay away from Firebird. I'd go with SQLite, assuming this is a DB that goes with some sort of a desktop app? If server based, I'd say MySQL.
I'm not arguing with you ... but why would you recommend staying away from Firebird?
My experience was a few years ago, but I recall being frustrated with it. Although, looking at it, that wasn't with Embedded Firebird, which may be what CK wants. So maybe that's not an issue.
Matt
8. Re: Database Battle to the Death
- Posted by CoJaBo Oct 10, 2010
- 1407 views
EDB is lightweight but is not at all robust. I would imagine it would also be quite slow for heavy uses as it has to read the entire index into memory.
SQLite and MySQL are both extremely robust and can operate efficiently on huge tables; SQLite is lightweight (single file/single process) whereas MySQL is pretty heavy (client/server based). Firebird is probably comparable to MySQL/PostgreSQL, etc.
EDB is best for simple data storage where it is feasible to keep a backup copy of the database after every important change or where the data is non-critical (e.g. a high-scores list in a game).
SQLite is best for applications that need robust, reliable storage and/or run SQL queries (e.g. desktop app).
MySQL is best for anything that requires a high level of multiple access (e.g. web app).
(note that its still a good idea to backup periodically even if using SQLite/MySQL)
SQLite is the clear winner in your case- EDB is under-powered and MySQL/Firebird would be overkill.
9. Re: Database Battle to the Death
- Posted by Bellthorpe Oct 10, 2010
- 1400 views
EDB is lightweight but is not at all robust.
Can you expand on what you mean by not robust? I've been using EDB for some years in production applications, with tens of millions of records, without one single failure.
MySQL is best for anything that requires a high level of multiple access (e.g. web app).
I also use EDB for CGI applications, some of which have reasonable traffic (tens of thousands of visitors a day). The locking mechanism is sound, and again I've never had a data access failure.
I'm not suggesting that it's perfect for all uses. And I don't try to use it in any 'relational' way. I'm suggesting however that it's sometimes under-rated.
10. Re: Database Battle to the Death
- Posted by DerekParnell (admin) Oct 10, 2010
- 1386 views
EDB is lightweight but is not at all robust.
Can you expand on what you mean by not robust? I've been using EDB for some years in production applications, with tens of millions of records, without one single failure.
EDS does not (yet) support transactioning, which means that if the application crashes during a write to the database, it may leave the database file corrupted. This is a highly unlikely event but if you are running a mission critical app then this is a risk that might not be acceptable. In database jargon, transactioning ensures that a commit to the database will either be totally successful or have no effect, thus you cannot get a partial transaction recorded in the database - even if the application crashes during the commit.
MySQL is best for anything that requires a high level of multiple access (e.g. web app).
I also use EDB for CGI applications, some of which have reasonable traffic (tens of thousands of visitors a day). The locking mechanism is sound, and again I've never had a data access failure.
I'm not suggesting that it's perfect for all uses. And I don't try to use it in any 'relational' way. I'm suggesting however that it's sometimes under-rated.
I agree, it is under-rated but it does have some 'api' issue which we will address, to make it easier to use. Also, it does load the complete index for the current table into RAM, which is 4 bytes per record minimum.
11. Re: Database Battle to the Death
- Posted by Bellthorpe Oct 10, 2010
- 1363 views
EDS does not (yet) support transactioning, which means that if the application crashes during a write to the database, it may leave the database file corrupted. This is a highly unlikely event but if you are running a mission critical app then this is a risk that might not be acceptable. In database jargon, transactioning ensures that a commit to the database will either be totally successful or have no effect, thus you cannot get a partial transaction recorded in the database - even if the application crashes during the commit.
A very good point.
For that reason my programs keep their own transaction journals. Fairly easy to implement. There's no rollback facility, but a corrupted database can be re-created in moments, automatically. The journals are regularly backed up using rsync to another location. Not perfect, if a crash takes out the database and the current journal, but I'm fairly happy with it.
12. Re: Database Battle to the Death
- Posted by CoJaBo Oct 11, 2010
- 1355 views
"Robust", in the context of a DBMS, refers to being ACID-compliant.
EDB has no such mechanism, so if your program crashes during a database write- even once- your data is gone. Game over.
The chance of this happening on a small or infrequently updated database is insignificant, as your program will probably spend most of its time waiting for input vs milliseconds updating the database. In contrast, even a moderately loaded database stands a poor chance of surviving a crash while in use.
SQLite and MySQL are both ACID-compliant- short of a catastrophic hardware failure (or a rare bug in the database engine itself), your data is completely safe no matter how many times your program or OS crashes or power fails. SQLite can handle an unbounded number of such events. EDB (under load) averages about 3-8 before the index is completely hosed.
