When developing the new AdMentor PRO I have ran load tests (using OpenSTA) for the banner serving engine against the different drivers supported - Access, MySQL 4 or less, MySQL 5 (with SP) and MS SQL 2000 or higher.
Please note that these tests has been rather unscientificly performed - and I have been running them mostly in an unit testing purpose to verify that the drivers are working correctly, more than to make comparisons. Still I think the numbers are pretty interesting, therefore I publish them here.
Let me first give you some backgrounds. What's done in the tests is simply banner selection from a random criteria (zone and adposition) - information about active banners will almost certainly be available in the web application cache, but then I do some logging in the banner database table and a statistics table.
Computer: P3 2.8Ghz, 2GB RAM, double SCSI 15000 RPM drives. Everything is installed locally - i.e MySQL, MSSQL, webserver and OpenSTA is all ran on the same box.
Test: running 10 virtual clients requesting banners for 30 seconds. Each test is run 5 times.
Access driver: Using ADO.NET OleDBConnection. No stored queries or anything has been used.
MySQL5NoSP driver: Using latest ADO.NET MySQL driver from MySQL.com website. Ran against a 5.0 database but with no stored procedures used.
MySQL5SP driver: Using latest ADO.NET MySQL driver from MySQL.com website. Ran against a 5.0 database and using stored procedures.
MSSQL driver: Using ADO.NET SqlClient driver. Ran against a SQL 2000 database with stored procedures.
| Results |
| Access |
100 |
| MySQL5NoSP |
1087 |
| MySQL5 SP |
2016 |
| MSSQL |
2226 |
MySQL5NoSP is in other words almost 11 times faster than Access, and using SP:s in MySQL 5 almost doubles the speed. MSSQL is the fastest.
Please note that: I have worked with MSSQL SP:s for over 6 years and MySQL ditos for more like 6 days. Meaning I might not have written the most optimized SP code. However the SP:s are simple.
So, rather than saying anythng about the relationship between MySQL and MSSQL I instead want to emphasize on:
- using SP:s in MySQL really pays off
- Access is indeed really, really slow. But please note that befire ruling it out of your possible solutions, the question should not be "how slow is it compared to XXX" - but instead "Is performance good enough for my application?". Cause often it is. I have people telling me they have been running (old) AdMentor with Access with millions of monthly pageviews without trouble.
So, to sum up, please don't take these numbers as the truth when it comes to comparison: meaning you can't generally say that "Access is 22 times slower than MSSQL".
