MS SQL Express » New IT Farmer

original:http://www.channeldb2.com/profiles/blogs/db2-express-c-vs-ms-sql

I found interesting blog post comparing speeds of free databases. Test used MS SQL Express (2 versions) and MySQL. I decided to port that test into IBM DB2 9.7 and test it on similar hardware. Original test used CPU Intel Core 2 1.80 GHz, 2 GB RAM, OS: MS Windows XP. My configuration is same except slightly faster CPU running on 2.2 Ghz. I executed only bechmark for DB2 Express C and PostgreSQL, results for other databases are copied from linked blog post.

About benchmark

We are creating 3 tables, one with 10M rows and two smaller tables with about 1M and 3M rows. We are testing speed of INNER JOIN between these tables using 2 SQL queries.

Table name	rows	columns
table_cislo	1 001 213	cislo integer PRIMARY KEY, datum timestamp
table_datum	2 998 946	cislo integer IDENTITY, datum timestamp PRIMARY KEY
table_zaklad	10 000 000	cislo integer IDENTITY, datum timestamp

Indexes: table_zaklad(cislo,datum) + indexes created by database because of primary key

Select 1: select count(*) as sel1 from table_zaklad tz inner join table_datum td on tz.datum = td.datum inner join table_cislo tc on tz.cislo = tc.cislo

Select 2: select count(*) as sel2 from table_cislo tc inner join table_datum td on tc.cislo=td.cislo inner join table_zaklad tz on td.datum = tz.datum

Preparations

First task is to create and fill test tables using stored procedure. Test preparation time was: 110 minutes for MS SQL and 32 minutes for DB2. In case of DB2 this task was CPU bound, one core worked at 100%, in case of MS SQL task seems to be IO bound because it used at most 50% of one core. No prepare time data are available for MySQL and PostgreSQL because data were not created using stored procedure but loaded from CSV file.

Diskspace used for storing test data

Database	Diskspace used (MB)
MS SQL 2005 Express (mdf+ldf)	647
MS SQL 2008 Express (mdf+ldf)	841
MySql 5.1 InnoDB	754
MySql 5.1 MyISAM (myi+myd+frm)	433
DB2 9.7 Express C	680
PostgreSQL 8.3.8	1 005

Benchmark

Rules for benchmark are simple: Database server can use at most 0,5 GB RAM for cache. Restart database server to flush buffer cache before running each query.

Results

Query times in seconds

Database	Query 1	Query 2
MS SQL 2005 Express	70,71	65,78
MS SQL 2008 Express	109,09	92,21
MySql 5.1 InnoDB	34,14	104,78
MySql 5.1 MyISAM	44,89	85,73
DB2 9.7 Express C (noindex)	26,24	13,83
DB2 9.7 Express C (index)	11,87	18,06
PostgreSQL 8.3.8	145,96	268,89

DB2 notes

In DB2 we are using STMM to tune memory and tablespace with flag NO FILE SYSTEM CACHING for avoiding caching data by OS between database restarts and spoiling benchmarks. During benchmark STMM configured default buffer pool with 2500 pages (8K each). This is quite low especially when compared to memory cache used by other databases. Entire database is 860MB and it will not fit into max allowed 0,5GB buffer pool; disk reads are unavoidable.

There is probably bug in DB2 optimizer, because dropping indexes will make second query run bit faster. Very important is to do runstats on benchmark tables. Without runstats db2 is just slightly faster then MS SQL Express.