Re: Performance of sqlce 30 (sql Mobile) in inserting large amounts of data (60 000 rows)
- From: "Joćo Paulo Figueira [eMVP]" <joao.figueira@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 15 Dec 2005 10:46:39 -0000
The performance problem you are experiencing is not on the database engine,
but on the .NET CF wrapper code in .NET CF 1.0. When you use INSERT
statements to copy such large amounts of data, you have to open the base
table cursor, insert the data and then close the cursor again. Preparing the
command will only speed up the SQL command interpretation, not its
execution.
SQL CE and SQL Mobile both have direct means to access the underlying
storage engine via either OLE DB interfaces or via the SqlCeResultSet class
in .NET CF 2.0 (available only for SQL Mobile). My tests show that the
difference in speed may be more than 5 times faster than when using prepared
INSERT commands.
My company sells products that allow you to very quickly copy data back and
forth and these are sold using developer licenses (fixed fees). You can find
the products here: www.primeworks-mobile.com or www.primeworks.pt
To answer your final question, there is the OLE DB option which is available
both for SQL CE 2.0 and SQL Mobile. This implies using C++ to access the
database, but then that's how I carry my everyday work... ;)
--
João Paulo Figueira
Embedded MVP
http://nativemobile.blogspot.com/
<ivarklung@xxxxxxxxx> wrote in message
news:1134639509.472845.148800@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> In our application we use a webservice to download tables from a remote
> server.
> The number of rows is huge > 60 000.... (but not alot of data)
>
> In sqlce 2.0 this operation can take extremely long time - from
> 40minutes to more than an hour (using RAM or extremely fast flash
> cards). It seems that it is ADO.net performance on insert that is the
> problem.
>
> In searching for a solution I've come over a product called sscedirect,
> which seems to solve this issue. We are however developing Open Source
> so we can't pay for per unit licenses.
>
> 1. Does anybody know if sqlce 30 also has the same poor performance?
> 2. Does anybody know of a sscedirect similar product being Open Source
> or redistributable at a fixed fee?
> 3. Does anybody know of a way of connecting directly to the sqlce
> without using ado.net?
>
> Thank you very much for any input!
> Have a nice day!
> Ivar
>
.
- Follow-Ups:
- References:
- Prev by Date: Performance of sqlce 30 (sql Mobile) in inserting large amounts of data (60 000 rows)
- Next by Date: Re: Performance of sqlce 30 (sql Mobile) in inserting large amounts of data (60 000 rows)
- Previous by thread: Performance of sqlce 30 (sql Mobile) in inserting large amounts of data (60 000 rows)
- Next by thread: Re: Performance of sqlce 30 (sql Mobile) in inserting large amounts of data (60 000 rows)
- Index(es):
Relevant Pages
|
