SQL CE Performance so slow.... [modified]
-
Hi. I'm changing an existing C#/Compact framework 2.0 from using DataSets as the data storage (backed up by XML) to SQL Mobile 2005 (as an offline DB only). I'm doing this because after changing each position of the DataSet we were writing the XML to file system (just in case of a crash, low power, etc). That became very slow under Windows Mobile 5 (RAM -> ROM change). So I've just finished rewriting the application so that it used SQL Mobile 2005. When only one entry in the DB changes its a lot faster. Great. But when I delete all data from the DB or I want to insert all records into the DB it takes 1-2 minutes (about 200 entries). This just seems to be way too slow. That means that it can only write about 3 entries per second....that seems really slow. The read performance is very good. I'm using Microsoft SQL Server Mobile Edition 3.0.5206.0 from Visual Studio 2005. The performance is bad when running it inside the debugger and using the same code started directly on the PDA (without the VS 2005 debugger running). I'm not sure how to export my tables as SQL code to code project. Any ideas? or what info can I share which could help anyone (who can help me:) ) Okay, so I ran a test with a Symbol MC70 (Intel XScale 624 Mhz processor). For 158 entries it took over 2 minutes to delete the DB (I delete each DataRow individually - is this why the delete takes so long?). The inserts took quite a while too, about 30 seconds). Why are these values so long? The DB Table has a primary key (4 columns) & the DB Table has a total of 40 columns. Thanks Martin
modified on Thursday, March 13, 2008 10:48 AM
-
Hi. I'm changing an existing C#/Compact framework 2.0 from using DataSets as the data storage (backed up by XML) to SQL Mobile 2005 (as an offline DB only). I'm doing this because after changing each position of the DataSet we were writing the XML to file system (just in case of a crash, low power, etc). That became very slow under Windows Mobile 5 (RAM -> ROM change). So I've just finished rewriting the application so that it used SQL Mobile 2005. When only one entry in the DB changes its a lot faster. Great. But when I delete all data from the DB or I want to insert all records into the DB it takes 1-2 minutes (about 200 entries). This just seems to be way too slow. That means that it can only write about 3 entries per second....that seems really slow. The read performance is very good. I'm using Microsoft SQL Server Mobile Edition 3.0.5206.0 from Visual Studio 2005. The performance is bad when running it inside the debugger and using the same code started directly on the PDA (without the VS 2005 debugger running). I'm not sure how to export my tables as SQL code to code project. Any ideas? or what info can I share which could help anyone (who can help me:) ) Okay, so I ran a test with a Symbol MC70 (Intel XScale 624 Mhz processor). For 158 entries it took over 2 minutes to delete the DB (I delete each DataRow individually - is this why the delete takes so long?). The inserts took quite a while too, about 30 seconds). Why are these values so long? The DB Table has a primary key (4 columns) & the DB Table has a total of 40 columns. Thanks Martin
modified on Thursday, March 13, 2008 10:48 AM
The dirty secret of SQL Server CE performance is that it's best not to use SQL. For simple queries that only affect one table - and INSERT, UPDATE and DELETE only can affect one table - it's better to use a
SqlCeResultSetand iterate through it manually. See for example this article[^] on insert performance.DoEvents: Generating unexpected recursion since 1991
-
The dirty secret of SQL Server CE performance is that it's best not to use SQL. For simple queries that only affect one table - and INSERT, UPDATE and DELETE only can affect one table - it's better to use a
SqlCeResultSetand iterate through it manually. See for example this article[^] on insert performance.DoEvents: Generating unexpected recursion since 1991
Hi Mike. Thanks for your answer. I ran into this same article today by chance....and enjoyed what I read. It seems quite annoying that the SQL Server CE team haven't implemented UpdateBatchSize as SQL Anywhere from Sybase has. Still I'll have to live with it.... I guess I'll have a bit of a rewrite to do, so that this works. Still a 10x performance increase would be really great. Thanks Martin