SQLCE performance from .NET CF v2.0

[Christopher Fairbairn]

Hi,

I am quite new to the use of the compact framework for application
development on primarily Pocket PC 2003 and (now with the emergance of
suitable devices) Pocket PC 2005 based PDAs. I have however spent a long
time (many years) doing development using unmanaged C++ code in such an
environment.

As part of evaluating a switch to C# development on the Compact Framework
for a new version of my application, I am evaluating the use of SQLCE for
local database functionality. At present I am using another thirdparty
database technology and I am considering switching to SQLCE at the same time
I make the switch to C#.

At present I have made a proof of concept application which performs some of
the basic functions required for my application in order to evaluate
concerns over speed and development effort etc.

When this application is using a CDMA data connection it can transfer 21,000
records worth of "static data" (lookup tables of business specific data used
in various parts of the handheld UI) from the server to the mobile handheld
in approximatly 20 seconds.

My problem is that it is then taking approximatly the next 10 minutes using
SqlCeCommand's ExecuteNonQuery() to perform the nesscary INSERT and/or
UPDATE statements in order to place this data into the SQLCE database. I
would like to attempt to decrease this timeframe, although by itself an
insert/update every 28ms or so on average isn't too bad I guess if you only
need to do an insert of one or two records.

Does anyone have any performance related tips/advice in order to attempt to
increase the performance of inserting data into the SQLCE database? Not
having much experience with SQLCE I am not too sure about how to fine tune
it's performance etc. Is there any mechanism/api targeted specifically for
bulk data transfers? Things I have tried include:

1) Reusing SqlCeCommand objects (I have two command objects for each table
of static data, one doing a parameterised insert and one doing a
parameterised update) rather than creating new SqlCeCommand objects for each
of the 21,000 rows.

2) Using parameterised statements and calling Prepare() on them before
entering the loop which will generate 21,000 new rows. These loops update
the parameter values and then re-invoke ExecuteNonQuery() in order to
execute the prepared statement again.

3) When specifying the parameters for the statements I explictly set their
datatypes and max lengths etc.

4) The data comming from the server tells me if I need to do an update vs an
insert. Hence I don't need to do a query on the handheld database for each
row to detect what type of SQL statement I must use for a given row.

Any ideas, tips, or SQLCE performance numbers greatly appreciated.

Thanks,
Christopher Fairbairn

 

[chris-s]

Try using the RDA 'Pull' methodology, in our experience this is far
faster than other approaches without using other third party tools. You
effectively submit a 'select' statement to your server and the results
are stored in a table in your mobile database.

Cheers

Chris

 

[Joseph Byrns]

Are you using SQL Mobile (3.0) or SQL CE 2.0? SQL Mobile should be
considerably faster.

Also you could try using a DataAdapter to fill a table with no data (i.e.
use a select query from your local table that returns no rows), then loop
through all your new data and add it to the table. The rows that should be
marked as needing updated rather than inserted can be changed to modified by
row.SetModified() (assuming you are using CF2.0). Then you can use
adapter.Update(...) to update the local database.

I don't know if any of the above will work but would be interested to know
what you find out.

 

[Christopher Fairbairn]

Hi,

I am using SQL Mobile 3.0. Since I am looking at jumping onto the C# managed
code environment for PDAs with CF 2.0, so SQL Mobile 3.0 should always be
available for the devices I intend to target. I didn't see much point in
evaluating SQL CE 2.0 in this light, is there something I am missing, which
should make me also want to compare SQL CE 2.0?.

I have avoided DataSets during this evaluation due to initial testing which
showed that they took significantly longer to deserialise from a stream (and
used more memory) than what I could easily achieve via other mechanisms. I
will however give your suggested scenario a go, of using a DataTable
directly.

Thanks,
Christopher Fairbairn

 

[Ginny Caughey [MVP]]

Christopher,

SQL Mobile has been faster than SQL CE 2.0 in my testing, so I'd certainly
recommend continuing to work with SQL Mobile. You might also want to
consider using the SqlCeResultSet for direct access to the data.
SqlCeResultSet is almost as fast as using a DataReader (in fact
SqlCeResultSet is a DataReader) but it is also data bindable with controls.

Ilya Tumanov posted some code a couple of days ago on this newsgroup that
illustrated the different speeds you get with SQL Mobile using different
approaches. The thread is titled "SQL Server 2005 Mobile - first query so
slowly -why?" I'd suggest taking a look at his sample.

 

[Carlos Alejandro Pérez]

Hi

Take a close look to Push-Pull in order to find out if this approach can be
used in your problem. When using RDA, the result set is retrieved in the
device and any changes to this result set is tracked by using a bit flag, so
when you issue UPDATE only the marked rows are considered against the
back-end, this would save you *many* seconds.

If you stick with local databases on the device, aka SQL CE, I strongly
recommend to switch to SQL Mobile. The team have made significative changes
in the engine itself.

However, regarding SQL CE, you can try to prepare a big dataset with bulk
segments from your 21k records. Say, define your datasets -programatically-,
divide your data and prepare several datasets with 2k records each. Populate
every dataset. Once done, you can try to issue a single UPDATE using each
2k-row dataset. This may be, perhaps, a rude approach to your solution but
it might work. Try to avoid a row-by-row operation, think in "batch mode"
when dealing with the engine.

Please remember when bulk inserting is being executed against SQL CE, the
engine enforces defined constraints for every single inserted row (key and
domain constraints most commonly). Provided domain constraints cannot be
avoided, you can only deal with key ones. If you depend on such bulk
inserts, consider to (a) ensure your 21k records have no repeated key rows,
and if so (b) execute your command against a table- having every index
erased-, in this case the engine doesn't waste time to verify such
constrainsts. Once the data is inserted you can recreate your index. If this
approach cannot be used, you can consider to minimize your indexes, think
minimalist.

If you have some few bucks to spend, please take a look to
http://www.fitiri.com/F_Wireless_Apps2.html and to
http://www.knowdotnet.com/articles/sscedirect.html

Hope this help,

Carlos

 

[Christopher Fairbairn]

Hi Ginny,

Thank you for your response. In between my original post and reviewing yours
I had discovered SqlCeResultSet's ability to add records efficiently while
using a SqlCeCommand of type TableDirect. Initially I had thought this was
only capable of reading data, not efficiently inserting additional data.

Using SqlCeResultSet has more than halfed the time it takes to insert 21,000
records, even with no additional optomisation effort. I am now confident
that I can rule out database related issues as a technical risk factor and
am confident that with additional optomisation efforts we will be able to
further reduce this timeframe.

The code posted by Ilya Tumanov was mainly related to data binding i.e.
extracting data from a SQL Mobile database and displaying it in a UI, my
major concern was insertion speed. So although I have reviewed the source
code he provided I doubt it is of much use directly. We have never had a
problem with query speeds.

Many posts have suggested looking into RDA based replication, am I correct
in saying that this will be a difficult solution to utilise when also
utilising some PDA devices which utilise non Microsoft based OSes, i.e.
where SQL CE / SQL Mobile edition is not available? Obviously we won't be
able to utilise C# in those situations either, but that is a different
discussion/topic entirely.

Thanks,
Christopher Fairbairn

 

[Ginny Caughey [MVP]]

Christopher,

RDA can be an attractive option but only with Microsoft-based mobile
devices. Since SqlCeResultSet looks promising and query performance is not a
problem, I'd just continue on that path.

You're correct that Ilya's code showed the speed of data binding, but it
also showed using a DataReader (more like SqlCeResultSet in performance)
compared with a DataSet if I recall. No matter - since inserts were the
issue for you, SqlCeResultSet with direct table update will be the fastest
performer.

 

[Kevin.dhb]

If you are intending to work with Windows Mobile 5.0 devices, you may
have to recheck all of your performance metrics. Pocket PC 2003
devices use RAM for all of the storage, including the file system.
This is not so in Windows Mobile 5.0. RAM is used for the execution of
programs and ROM (flash) for the file system - presumably the database
included. See
http://blogs.msdn.com/windowsmobile/archive/2005/08/19/453784.aspx for
a good description of the issues involved.

If you require a Windows Mobile 5.0 device to do a comparison on I may
be able to assist.

Regards,
Kevin Holland