Which database can I use?

[Zomis]

I have asked before, but I didn't get any reply. So I try to ask again, but hopefully being a little bit clearer.
Please, any replies are welcome! If you know some database which has support for only some of these things, let me know!

First of all, I'd like a database.

Second, the database application should be able to run in both Linux and Windows (which is the reason why I'm using Lazarus)

Third, I'd like the database to be quite fast. I'm not sure which databases are slow or not, but I guess most of them are quite fast. The database will contain two tables, one which will contain about 150 000 entries, and one with about 300 entries.

Fourth, I'd like it to be able to use JOINS in SELECT statements. Like "SELECT * FROM table LEFT JOIN table2 ON ..."

Now for the less important things, but I'd be very happy if there's some kind of database which has it all:
It would be veeerry nice if the database could run without the need of any external files (.dlls, .sos, .exes...) - so that it doesn't need any external server

It would be nice if the database could run both as stand alone (both server and client in my Lazarus application) and connect to some server.

It would be nice if the database could import and export to external files, for example - import all entries from another file which doesn't already exist in database.

[g-r-g]

[...]
Please, any replies are welcome!
[...]

http://www.firebirdsql.org/
http://www.postgresql.org/

[eden159]

I'm using Firebird and it's quite fast - 2 nested queries for a table with more than 100 000 000 records is executed for 0.05 sec on a desktop computer.

[Zomis]

Wow, that's very impressive speed. But can you tell me a bit more about Firebird, which of the things I mentioned has it support for, and which does it not have support for?

More tips, and any replies, still welcome.

[eden159]

Wow, that's very impressive speed. But can you tell me a bit more about Firebird, which of the things I mentioned has it support for, and which does it not have support for?

More tips, and any replies, still welcome.

Firebird covers all of your requirements - you can install it as an external server or you can use the embeded version (a few DLL files - you don't need to install a sever).

The database itself is one file (in some case it may be more) and you can use it on windows or linux with no need to change anything.

You can import external files with various tool - my favorite is IBExpert - http://www.hk-software.net/ibexpert.com/content/doc_122943-0-0-00.php.

Hope that helps.

[Zomis]

I wasn't thinking about an external tool. It'd be great if I could merge database files using my application somehow. But that isn't so important.

However, another thing has come to my mind. Auto-calc fields or custom SQL-functions.
http://www.lazarus.freepascal.org/index.php?name=PNphpBB2&file=viewtopic&t=3396
Is there any database which supports that?

[beli0135]

Firebird is BAD database server. More complex queries get him stucked. Optimizer is more than bad.

PostgreSQL, is quite another story, even if free, lousy looking interface, it is awfully fast (rang of oracle/MSSQL), handles complex queries with ease, works fast even with tables over 3 million records (our system has 6200 tables) and works like swiss watch.
My recommendation

[bee]

Please don't spread FUD about any open source products in an open source community. Firebird is not that BAD, in fact it's very far from bad. Which version of Firebird have you tried? Have you tried the latest version (v.1.5.4 and v.2.0.1)?

I use both Firebird and PostgreSQL in production environment. Both are FAST for common operations. For some specific operations, each has its own advantages and disadvantages. That's why I use both.

Since you (beli0135) said Firebird is bad, now I will be on Firebird side to stop this FUD. Firebird is fast, has small footprint, officially supports embedded mode, easy installation and configuration, almost zero maintenance, etc. But it does perform pretty slow on very huge hits especially when involving very complex queries. I don't count a left join as a complex query. I have a Firebird (v.1.5.3) database with hundreds of tables and over 6 million records on a particular table, runs very stable with number of hits over 100 users (daily use).

PostgreSQL is also a good product and performs as good as Firebird. It does performs better (and more stable) on very huge hits involving complex queries, compare to Firebird. But the installation is not as easy as Firebird, the configuration is more complex, no support for embedded mode, and requires slightly higher CPU and memory.

So, I think it's just a matter of requirement. For easy maintenance with low to medium hits, I prefer Firebird. But for a high load transaction, I prefer PostgreSQL since it performs better than Firebird. In fact, I have more Firebird databases compare to PostgreSQL databases on my production zone.