OSNews

It would indeed be interesting to benchmark the various OS databases: Firebird, Postgres, and MySQL.

yes and whats really interesting is that firebird comes in several flavours:

1) The superserver is a multithreaded design (much like Mysql) and this is great for raw speed (threads typically being a lot faster to create and considerably more lightweight on memory than seperate processes)

2) The Classic Server. This is a mutli process design like Postgres

3) Embedded - this is a dll or shared object instead of a daemon that you can embed in any application (much like Sqlite)

4) Vulcan - Teh next generation firebird that contains a generic engine that can be any of the above!

I would expect the superserver to be on a par with Mysql for performance but faster than postgres especially with loads of connections due to threads massively outperforming mutliple processes when connection count is high.

I don't know if benchmarks would be interesting at all since speed is the one and only strength of MySQL. Fetcher completeness in respect to the SQL standard should be an issue and that's where MySQL sucks. They have a special kind of fetcher creep, the fetcher creeps from one todo list to the next since they can't implement it.

"speed is the one and only strength of MySQL"

Only if you turn off transactions in mySQL. Using InnoDB engine with mySQL is dog slow. We have done lots of benchmarks of Firebird and mySQL (InnoDB) and Firebird wins out easily. Only time I have ever seen mySQL beat Firebird is when mySQL is using non-transactal myISAM, and the queries are very simple (no joins etc...), but why test this way? Only the simplest of apps would not need transactions or joins.

Can you tell us more about this oracle-mode? The docs or url? I am interested. Thank you very much.

Oh, never mind.
A little googling found http://www.janus-software.com/fb_fyracle.html.

Seems interesting. Might port our oracle apps to firebird.

AFAIK he is busy working on FB stuff for BroadView Software, a Canadian company with big investment into Firebird, and one from big Firebird & Foundation sponsors. I suppose that he would show up on conference along with others from BroadView.

As long as you don't mind paying for it, or open sourcing any application that touches it.

I'm referring to the fact that mySQL 5 no longer has an LGPL driver.

Most of your points are valid specially numbers 6 and 9.

But some others are kinda innacurated like

Number 1.- I think the dialect is plaint simple.
Number 3.- Is coming in firebird 2 before at the end of this year.
Number 4.- Is that really necesary?
Number 12.- There are alias for them so you dont have to use the path.
number 13.- Your DB will be as secure as you secure your server.

And you forgot one, the documentation is to poor.

But mean while PG in windows keep being to damn slow I will have no choice.

1. It's historical, and all DB products have their SQL quirks don't they?

2 & 3 should not be a problem if FB comes prepackaged with the frequently used functions.

8. Most other SQL DBs have one stored procedure language either, and that doesn't seem to be too crippling. But I admit, being able to use Ruby (e.g. in Postgre) or .NET (e.g. in SQL Server) should be fun and joyful compared to those weird, COBOL-like procedure languages.

9 & 10. This is one of the main problem with FB.

12. Aliases.

---

My main problem with FB is that DDL (CREATE TABLE, ALTER TABLE, DROP TABLE, etc) are weird and not really transactional. You need to COMMIT after each DDL. Well sometimes there are use cases where I need DDL to be inside a transaction so it is rollback-able if things go wrong.

---

But as long as there's no embedded Postgres (which will be the case for a long time) FB will have its niche. SQLite doesn't come close in feature set.

> 1. Has weird sql dialects

The only quirky thing about Firebird is the Stored Proc language - this is NOT part of the SQL dialect, and all comparable products have similar quirks (e.g. T-SQL in SQL Server and Oracle's PLSQL which is basically ADA.)

> 2. Limited number of built in functions
> 3. Because of small amount of built in functions,
> you must resort to UDFs that can be flakey.

UDF, UDF, UDF. Regression testing. I have written countless UDF functions for Interbase, and it aint hard. In fact, it is so easy as to be stupidly easy.

If you test your code, you have nothing to worry about!!

> 3.No temp tables at all

Who needs temp tables? What are they used for?

create procedure NOTEMPTABLENEEDED
returns (id integer, text varchar (20), num integer)
declare variable t1 varchar (7);
declare variable t2 varchar (10);
begin
for select id, text1, text2, num
from atable
into :id, :t1, :t2, :num do
begin
text = :t1 || :t2;
suspend;
end
end

then:

select * from NOTEMPTABLENEEDED;

TADA! No temp table needed.


> 4.No tablespaces

Explain why that is a problem?

> 5.You can use all SQL in stored procs(except via
> EXECUTE command)

That doesn't even make any sense. You can call "execute procedure" or you can call "select whatever from..." depending on whether the SP returns one result or multiple lines. What is the problem with that?

> 6. Transactions are stored in the same file as the
> DB, which results in huge file growth.

Which can be removed instantly if you back up and restore the database... you do backup your database periodically, dont you?

gbak -b mydatabase.gdb mydatabase.gbk -user XXX - pass YYY

gbak -r mydatabase.gbk mydatabase.gdb -user XXX - pass YYY

Will recover all surplus space.


> 7. Because there is no seperate transaction table,
> you can't do point in time recovery.

And why is that an issue? Do regular backups (which can be done inline whilst the database is being used and accessed) and this is purely moot.

> 8. Only one stored proc language

LOL. SQL Server 7 and 2000 only had one too.

> 9. Development is pond water slow...

Rather it was stable.

> 10. The developer community at times can be cranky
> (and that is putting it mildly)..

Because they complained when someone stole their name? What would you do?

> 11. Can't do cross server/database queries/joins

Good. Maybe then people will design better systems.

> 12. DBs are referenced by file name and path

Un true. Interbase, and by virtue Firebird up to at least 1.5, supports 4 different access methods. Local (by path), TCP/IP, SPX/IPX and Named Pipe.

Equally, you can pump either of the following paths into a Windows Firebird TCP-IP connection:

127.0.0.1:c:mydatadata.gdb
127.0.0.1:/mydata/data.gdb

either will work.

> 13. Password security is limited and weak

Why? How secure is your server? You do not have to use the default admin user, in fact we used to delete that user when I worked for a company that used FB1.5 a while back.

> When you look close it's a very limited database and
> these are just a small number of the problems with
> it.

You can always bet this post will arrive on OS News when there is a Firebird story. YAWN!

> If you need somthing that can be embedded, then
> Firebird is a good choice, however it is not ready
> for the Enterprise yet. Postgresql is a much better
> choice IMHO.

Oh crap, well we better let all out customers know.... Seriously, you are spouting complete rubbish.

Multiple process DBs like Postgres are not scalable when compared to threading ones like Firebird and mysql.

Threaded ones can easily support 10x and even 20x the no of connections of a process based one (due to lighter memory requirements).

The cache is shared amongst all threads hence hotter cache and faster queries.

So I am right - threaded RDBMS easily out perform process based ones when the no of conections exceed about a dozen.

um..wrong. The only time you use execute procedure is when you want to *run* a procedure. As previously noted, you don't even need to do that if your procedure calls suspend after it has filled the returned variables - you can just call it as if it was a built in function via select.

>Even with aliases you must manually build them on the
> server side. You don't have to do any of this with
> Postgresql, the more you use PG, the more you wonder
> why you used to like FB.
> PG keeps a system table called pg_database and it
> contains info on all databases created. With PG all you
> need is the name, the rest is taken care of.

I prefer the FB way, it's far better than central dictionary in system table. I frequently have many copies of the same database with different data and structures (some apps. work that way, sometimes is just for development). Handling that in PG is very painful (each must have different name etc.), while in FB I don't even think about it, just type in the right path and go. When I need db location independence (only on deployment), I can set up aliases, which is very simple. The fact that under FB, databases are selfcontained, easily transportable beasts is a great advantage over such behemoths like PG, that have many unnecessary layers of complexity, that add to the cost of db management. While PG has some nice features, it carry too many "database stone age" design flaws.

> As far a security goes Postgres has built in SSL
> support, encrypted/md5 passwords and access control
> via host/IP.

I don't need that at the db server, because I can easily do that by other means when necessary (ZeBeDe etc.), so my server will not carry along a dead weight when I don't need it.