| Subject | Re: [firebird-support] which one faster (overall) , MSSQL or Firebird ? |
|---|---|
| Author | David Johnson |
| Post date | 2004-01-25T07:34:08Z |
I have been performing performance testing with Firebird 1.0.3 and 1.5 RC8.
In a random read test, searching by pimary key returned 150 to 153 rows per second regardless of version and the table size. Table sizes measured were 4,000, 40,000, 400,000, and 4,000,000 rows. The subject database only has the one test table. The test suite also measures and accounts for its own internal performance.
This is actually not too bad - at 10ms per I/O, I am averaging 1.5 I/O's per row to select random rows via the primary key from a large table, with no performance tuning. The key is a 38 byte GUID and a 4 byte integer. Performance is essentially flat across all DBMS variables tested so far. I have discovered some issues related to Borland's connectivity options (SQLXpress works well for DB2, but fails after exactly 2080 sequential reads against a Firebird database, and IBExpress has a memory leak).
My random read test trashes the buffer pool pretty badly - the worst thing you can do to a DBMS performance wise is hit randomly throughout the tables. I have not increased ny bufferpool size over the default installation values, which are just a few K.
The Insert test results are best expressed in terms of transactions per second. With a commit granularity of 13 rows, throughputs arre approximately 625 rows per second.
In real life, where selection is generally more heavily clustered, and you increase the buffer pool size to something that can hold your working data, you have much less actual I/O happening. Performance will be determined by the efficiency of the query optimization path and the system bus.
MS-SQL's query optimizer works better for more complex queries. Interbase/Firebird offers you the option of overriding its internal optimizer's choices when you know more about the data than the system does.
MS-SQL has been successfully used to support databases as large as 64 terabytes. I believe that 7GB is the effective limit of a Firebird database.
All current relational DBMS are ultimately based on the same ISAM technology, so the query optimization and hardware selection are the two keys to performance.
In a random read test, searching by pimary key returned 150 to 153 rows per second regardless of version and the table size. Table sizes measured were 4,000, 40,000, 400,000, and 4,000,000 rows. The subject database only has the one test table. The test suite also measures and accounts for its own internal performance.
This is actually not too bad - at 10ms per I/O, I am averaging 1.5 I/O's per row to select random rows via the primary key from a large table, with no performance tuning. The key is a 38 byte GUID and a 4 byte integer. Performance is essentially flat across all DBMS variables tested so far. I have discovered some issues related to Borland's connectivity options (SQLXpress works well for DB2, but fails after exactly 2080 sequential reads against a Firebird database, and IBExpress has a memory leak).
My random read test trashes the buffer pool pretty badly - the worst thing you can do to a DBMS performance wise is hit randomly throughout the tables. I have not increased ny bufferpool size over the default installation values, which are just a few K.
The Insert test results are best expressed in terms of transactions per second. With a commit granularity of 13 rows, throughputs arre approximately 625 rows per second.
In real life, where selection is generally more heavily clustered, and you increase the buffer pool size to something that can hold your working data, you have much less actual I/O happening. Performance will be determined by the efficiency of the query optimization path and the system bus.
MS-SQL's query optimizer works better for more complex queries. Interbase/Firebird offers you the option of overriding its internal optimizer's choices when you know more about the data than the system does.
MS-SQL has been successfully used to support databases as large as 64 terabytes. I believe that 7GB is the effective limit of a Firebird database.
All current relational DBMS are ultimately based on the same ISAM technology, so the query optimization and hardware selection are the two keys to performance.
----- Original Message -----
From: Kamlesh
To: firebird-support@yahoogroups.com
Sent: Saturday, January 24, 2004 2:12 AM
Subject: [firebird-support] which one faster (overall) , MSSQL or Firebird ?
Dear Friends,
Which one is faster (overall , specially in query), MSSQL or Firebird ?
is there any benchmark details available for firebird ?
Thanks
Kamlesh
------------------------------------------------------------------------------
Yahoo! Groups Links
a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
[Non-text portions of this message have been removed]