PostgreSQL vs FirebirdSQL in Windows Desktop Applications

[ptarver]

Last week I started a small XBasic script project to prove to myself that I could accomplish what I wanted to accomplish with Alpha Anywhere. I am currently evaluating AA for purchase and I have over 15+ years experience developing in Visual Foxpro. The script needed to create a temporary DBF table, import data from a CSV file into the temporary DBF table, then make a SQL connection to a backend database, loop through the DBF table, issue a Select query to determine whether the next step for the script is to INSERT a new record in the SQL database or compare each field in the record to the ResultSet to locate any required updates and submit them to the database. The processing included using a WaitDialog to update the user on the progress and the clean up included deleting the temporary DBF file when the process finished.

I initially tried to use FirebirdSQL as the backend database and became concerned that some of the problems might somehow be related Firebird. So, I switched to PostgreSQL as the back end database and discovered that the problems were with the logic and not the databases. So when I got the script working with PostgreSQL, I decided to see if Alpha was really SQL portable and so I switched back to the FirebirdSQL backend. The switch between different Named Connections was flawless and required NO CODE CHANGES to switch database engines.

My next thought was to try and do some benchmarking to compare PostgreSQL and FirebirdSQL and I thought someone here might want to see my results.

The full description of the script project and the working script itself can be found here:

http://msgboard.alphasoftware.com/al...es-amp-inserts

Using the exact same code published above and changing only from the native AlphaDAO PostgreSQL Named Connection to an AlphaDAO ODBC Named Connection to FirebirdSQL and both database engines were downloaded and installed using the default out of the box settings with no tweaking. I ran three tests on both database engines and came up with the following results:

498 Records Processed	                  PostgreSQL	FirebirdSQL
Empty Selects & All Inserts	          01:17.3	00:25.8
All Selects Tested; No Updates       	  01:42.6	00:55.2
39 Inserts & 20 Field Updates	          01:39.5	01:04.9

FirebirdSQL was the clear winner in this small, unscientific performance test. I'm sure the difference is directly related to PostgreSQL's ACID compliance, but the most interesting result to me was the second test where all we did was a single select statement to test all records for any required updates. No changes were written to the table in this test, so what we are seeing is the pure time it takes to select 498 records one at a time and then comparing each field within our temporary table to the SQL ResultSet and then move on to the next one. Amazingly, PostgresSQL was 47 seconds slower than FirebirdSQL. Again, all we were performing were reads, string comparisons and no writes. FirebirdSQL slows down a bit when we do the mix of 39 inserts and 20 field updates but only about 10 seconds, but PostgreSQL actually ran slightly faster with the inserts and updates. When performing all inserts, it wasn't even a race as FirebirdSQL was almost 3 times as fast.

Not sure if anyone cares about this type of comparison as many of us are happily committed to our favorite database engine and I'm sure that tweaks could be made to optimize different systems and that your mileage will vary.

But, if you are posting large quantities of data to a SQL database, FirebirdSQL is a great choice if you are interested in high speed inserts and updates.

I would like to hear from both PostgreSQL and FirebirdSQL proponents to hear your thoughts on the subject of which database engine is better. I'd even like to hear back from those using other SQL engines to get your takes as well.

Thanks!
PT