NAVmoble - the pocket-sized ERP
Optimized for Microsoft Dynamics NAV and Windows Mobile powered devices

Saturday, November 12, 2005

.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]
1101122096016
286731930176
385922189796
486562096160
586541930176

Scenario 2: SqlCeDataReader
#Time[ms]Memory[bytes]
1506952548
234531051124
334941151748
43414850952
53639953004

Scenario 3-a:SqlCeResultSet,None
#Time[ms]Memory[bytes]
15368952536
239041051156
339021151692
43964850896
53875952992

Scenario 3-b:SqlCeResultSet,Insensitive
#Time[ms]Memory[bytes]
112238952536
2108481051156
3108261151692
410917850896
510877952992

Scenario 3-c:SqlCeResultSet,Scrollable,Updatable,Sensitive
#Time[ms]Memory[bytes]
19928952536
282661051156
384441151692
48538850896
58460952992

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.

7 comments:

Sriram said...

Nice post! I'm the PM for data access on devices. I've blogged a link to your post at http://blogs.msdn.com/sriram/archive/2005/11/15/492893.aspx

Anonymous said...

Hi Ruslan, I found your post interesting, and I just wanted to add additional info/clarification regarding your tests:

1. DataSets, will always perform slower than any other method, mainly because data is stored (cached) in generic objects and value types will get boxed/unboxed, and that tends to leave junk objects for the GC to collect -- meaning it causes GC latentcy more often.

2. DataReader will always perform better because there's is no storing/caching of data, unless you do it yourself (as in your example) and you can improve that with strongly-typed collections or using generic types (ie. List< T >) to avoid boxing/unboxing of value types -- resulting in less memory pressure and lower GC latency.

3. Your test doesn't measure Sql Ce Engine performance, since your query will always trigger a table scan to fetch all rows. A problem I have experienced with the Sql Ce engine is not using indexes properly, or using queries that trigger a table scan, and when going against a 200000 row table the performance to fecth a handful of rows can make Sql Ce un-usable.

--Ader.

Anonymous said...

Great article! Thanks.

Anonymous said...

Thanks for interesting article.

Anonymous said...

Nice! Nice site! Good resources here. I will bookmark!

Anonymous said...

I see first time your site guys. I like you :)

Anonymous said...

Excellent website. Good work. Very useful. I will bookmark!