First of all a disclaimer: I ran these tests as part of unit testing for AdMentor.NET and
performance was NOT the most important factor I measured - my main concerns was to make sure
- data was being correctly updated
- performance was *good enough*
Meaning I have not optimized the SP:s used, but used a rather standard and simple to understand sql, nor have I tweaked the server engines for optimal performance. Basically I have ran them in their standard install mode. So don't make too much assumptions out of the result I am presenting, I don't wanna read somewhere that "Stefan at ASPCode.net says Postgresql is X times faster/slower than MySQL" for example.
All I am saying is, I am presenting the numbers I got and to me they do say a lot.
Environment:
Database server: Windows 2003, 1 GB RAM, single 10000 RPM SCSI disk.
Workstation: Windows XP SP2, 1 GB RAM, single 15000 RPM SCSI disk.
Type of test: just a plain throughput test - consequtive calls of
a) calls to stored procedure (function) looking up country from ipaddress (randomly generated from pretty large input file of ip addresses).
The source datatable had around 150000 records
b) calls to stored procedure (function) simulating a "click" on an ad. The SP logic involves
- updating a column (clicks) in campaign table
- checking if a certain row exists in a statistics table
- depending on existance above either insert new row or updated existing
- return a single varchar(255) field
I used pretty much the same C# code - but against their specific ADO.NET drivers of course. Sofor Postgresql I used Npgsql.NpgsqlCommand, for MySQL MySql.MySqlCommand etc.
All calls were used using
oCommand.CommandType = System.Data.CommandType.StoredProcedure;
and with parameter collections:
oCommand.Parameters.Add(new Npgsql.NpgsqlParameter("in_ip", DbType.Int64)).Value = ip;
oCommand.Parameters.Add(new Npgsql.NpgsqlParameter("in_ipverno", DbType.Int32)).Value = lIPVerNo;
etc.
I did run some tests against MySQL4 as well - and those are of course made up of queries instead of stored procedures (since SP:s are just supported from version 5.x and up)
I ran the tests 6 times in a row and discarded the first.
The overall results were (MySQL 4 is set as norm = 1.0 so a value of 2.0 would mean twice as FAST as
MYSQL 4):
MySQL 4 - no stored procedures
1.00
MySQL 5 - using stored procedures
2.83
Postgresql
1.27
SQL 2005 Express
3.71
From these tests I found that I got best performance from SQL 2005 Express.
Now, I wont go as far as saying - SQL 2005 is in general this much faster than MySQL5 and Postgresql.
It was for me in my specific case and more: lets look at my skill list:
MS SQL product family : worked with and more or less mastered for 10 years
MySQL family : worked with but not at master level for 5 years
Postgresql family : installed first time about a month ago...
I AM PRETTY SURE that has a lot to do with my results...So take these numbers with a grain of salt - but at least they might function as some sort of indicator for you.
