(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.
Monday, October 09, 2006
Subscribe to:
Post Comments (Atom)

1 comments:
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.
Post a Comment