Re: SQLCE performance from .NET CF v2.0

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" <christopher@xxxxxxxxxxxxxx> escribió en el mensaje
news:4397d98e$1@xxxxxxxxxxxxxxxxxxxx
> 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
>
>
>


.

Relevant Pages

  • Re: Is it just me or are there BIG problems with SQLCE 3.0?
    ... One trick you might try is to keep a connection to the database open. ... If you use the Open update Close technique, the database is compressed at the final close. ... issues with SQLCE 3.0 as opposed to SQLCE 2.0. ... days whilst constantly inserting into the ...
    (microsoft.public.sqlserver.ce)
  • Re: Is it just me or are there BIG problems with SQLCE 3.0?
    ... We have experienced the same problems in our app which is in production. ... are uppdating the database very often by getting data from our SQL Server. ... issues with SQLCE 3.0 as opposed to SQLCE 2.0. ... days whilst constantly inserting into the ...
    (microsoft.public.sqlserver.ce)
  • question about how to handle if row exists
    ... Since this is not supported in SQLCE ... what is the recommended way of inserting new rows in the database with a ... customer_id is a primary key ...
    (microsoft.public.dotnet.framework.compactframework)
  • question about how to handle if row exists
    ... Since this is not supported in SQLCE ... what is the recommended way of inserting new rows in the database with a ... customer_id is a primary key ...
    (microsoft.public.sqlserver.ce)
  • Re: get SQL statement from PreparedStatement
    ... PreparedStatement to the database to execute? ... Inserting a Row into a Database Table Using a Prepared ...
    (comp.lang.java.programmer)