.NET Compact Framework 2.0 Data Access Performance

Further reading .NET Compact Framework 2.0 Performance and Reflection

I took little of my time today to prepare a VS2005 SD project to test the performance differences between some of the SqlCe data access options. I have the following test environment:
Device: HTC QTEK 9090
OS: Windows Mobile 2003 SE
Dev.Env: VS2005 , CF2.0,SqlCe 3.0
I've created a table with the following Sql:

CREATE TABLE test1 ( id int IDENTITY NOT NULL, data1 nvarchar(100), data2 datetime, data3 float )

I've populated it with 10000 rows, using the following Sql:

insert into test1(data1,data2,data3)values('some string',getdate(),123)

I measured then the performance, while reading rows from SqlCe 3.0 by using the following testing scenarios:

scenario 1: DataSet, SqlCeAdapter
scenario 2: SqlCeDataReader
scenario 3-a: SqlCeResultSet, options None
scenario 3-b: SqlCeResultSet, options Insensitive
scenario 3-c: SqlCeResultSet, options Sensitive,Updatable, Scrollable

All scenarios were played using the following Sql statement:

SELECT id,data1,data2,data3 FROM test1

There is a significant difference between the first scenario and the rest ones. Scenario 1 fetches all rows into a DataSet. The rest fetch
rows into object instances defined like:

public class BObject { public int id; public string data1; public datetime data2; public double data3; }

The fetching code was like:

List<BObject> boList = new List<BObject>(); ... IDataReader reader = cmd.ExecuteReader(); ... while(reader.Read()) { BObject bo = new BObject(); bo.id = reader.GetInt32(0); bo.data1 = reader.GetString(1); bo.data2 = reader.GetDateTime(2); bo.data3 = reader.GetDouble(3); boList.Add(bo); }

Every scenario was executed 5 times and execution time and total memory reported by GC were written down. The application was restarted before each scenario test run. The results are as follows:

Scenario 1:DataSet
#	Time[ms]	Memory[bytes]
1	10112	2096016
2	8673	1930176
3	8592	2189796
4	8656	2096160
5	8654	1930176

Scenario 2: SqlCeDataReader
#	Time[ms]	Memory[bytes]
1	5069	52548
2	3453	1051124
3	3494	1151748
4	3414	850952
5	3639	953004

Scenario 3-a:SqlCeResultSet,None
#	Time[ms]	Memory[bytes]
1	5368	952536
2	3904	1051156
3	3902	1151692
4	3964	850896
5	3875	952992

Scenario 3-b:SqlCeResultSet,Insensitive
#	Time[ms]	Memory[bytes]
1	12238	952536
2	10848	1051156
3	10826	1151692
4	10917	850896
5	10877	952992

Scenario 3-c:SqlCeResultSet,Scrollable,Updatable,Sensitive
#	Time[ms]	Memory[bytes]
1	9928	952536
2	8266	1051156
3	8444	1151692
4	8538	850896
5	8460	952992

What is obvious from first sight is that SqlCeResultSet has different performance characteristics depending on the options used. Also you may see that DataSet is on the first place in memory consumed. One interesting figure is the longer time neededd to execute test run 1 and that's because of the JIT compiling proces on the first code hit.It seems from this tests that using SqlCeReder should be preferred in strict fetching scenarios, although perf differenceses from SqlCeResultSet(None) are not quite meaningful. However, if one needs scrolling and updating, SqlCeResultSet is the best choice.Unfortunately, I have not written down the perf. status of the system componentsts like SqlCe memory, total system memory used ,etc.This will help getting better picture of the data access pros and cons.