Monday, October 09, 2006

PostgreSQL, FirebirdSQL and MySQL(subjective view)

(Disclaimer: I am a PostgreSQL fan, this is my subjective personal experience)
Had a interesting experience with 3 of these DBMS'es.
Needed to write an application that opperates on 7000000 row table.
Need was:
- querry the table
- calculate some data, based on the row data
- update that row

Platform Java 1.5. Latest versions, latest drivers. Containers transactional(1 MySQL run on MyISAM).
Methods:
a) Updateable resultSets
b) execution of update statements same trx
c) execution of update statements another trx

Funny observations:
In method a MySQL had no speed difference between InnoDB and MyISAM.
Method a on PostgreSQL was slower than method b
Firebird does not support updatable resultSets.
MySQL and PostgreSQL were on par in method b.
MySQL was 15% faster than PostgreSQL in method a.
Firebird was 25-50% slower, even on beter conditions(memory and priority).
Allthough Firebird required 50% less disk space.
Some versions of MySQL with MyISAM did: drop the changes, partial update on 1 row. InnoDB had no such problem.

There will be no conclusion to this post. All DBMS'es are evolving. I just hope that they do not stop.

-----

Based on a cooment I should add that most of the time taken up was waiting for update and select queries.
Data store should be a data store not an application.
Do not keep logic(except referential integirty and query simplification) on the DBMS side.

And in my example this was a telco billing application, so it was impossible to put all logic into the DB.

1 comments:

Yoda said...

I think, that you measured speed of network protocols here. I wonder, what the result would be, if you tryed to write a stored procedure for that, so everything will be performed at servers? If calculations are too complex, you can write a UDF.