SQLCE

  • Thread starter Thread starter Mats Boberg
  • Start date Start date
M

Mats Boberg

Hi All,

I'm developing an application that is based on a SQLCE database.

When the application starts it creates the database (.sdf file) and creates
all tables that will be used.

In the database i have a table with 30000+ products.

The products is transferd to the sqlce via a dataset and webservice in
batches of 500 at a time.

Today the products is written to the table by inserting one and one
itterating the dataset. This takes like 5-7 h depending on the amount of
products.

Is there a way to store the 500 items by inserting the dataset instead of
inserting the products one by one?

The products is uppdated once a month. When this happends all products shall
be updated.

Today I drop the tProducts table and creates a new one. The problem is that
the size of the database doesn't go down when i drop and recreate the table.
I think this has to do with the index that is created...

Is thera an easy way to clear the tProducts table and index. I've tested
delete * from tproducts and droping the table... but as I said befor the
size of the .sdf file doesn't go down.

/Mats Boberg
 
You could also reconcider the webservice method and go to RDA or Merge
Replication. I have a sqlce db with over 50000 records which I download over
a phone connection in less then 10 minutes and less then 3 on high speed.

You can have a dataset insert records in a table with one single update
statement as long as you have set the right update, insert, delete commands
before calling update. I am not sure if that would speedup your application
as the dataset is probably only calling looping through each records and
calling the corresponding statement anyhow.

Again... think about replication if possible if you want more speed.

Buzz is correct... compact will reclaim unused space

Note: apparently sqlce3 will reclaim space automatically.

Regards,

éric
 
Hi Eric,

50000 records in 10 min over phone connection!!!

With my solution 50000 records with the pda docked with USB connection takes
about 7 h via webservice and insert one by one...

I must do something very wrong or the SQLCE classes provided by the
compactframework sux....

Doesn't i have to pay license money for the SQLServer 2000 and all SQLCE
clients, if I use RDA or Merge Replication?

/Mats.
 
The difference is that you're using insert statements one at a time. Eric
is using either RDA or Replication (unclear which). Replication is probably
the case becasue it only transferrs changes, not all the data again, and
you're bypassing the entire SQL parsing engine. This yields substantially
faster performance.

And yes, with RDA or Replication, you must pay for a license. Seems that if
it cuts 6hrs and 50mins off your data transfer time, it would be well worth
it.

-Chris
 
Sorry I was not so clear
Yes I use Merge Replication... Initial download takes < 10 minutes... that
is all 50000 records
future merge changes take about 1 minute for 6000 or so updates

Éric
 
Hi,

The 50000 records is a productdatabase that will be installed once and
updated with new prices 1-2 times a month so it's not that big problem.

But I find it absurd, that the performance loss is so large by geting the
products via a webservice and using insert. My knowledge about SQL is not
the best but it's not an advanced process.

It seems that this is a strategic move from M$. You give them a finger and
they take your arm...

Our application is for small businesses with 5-10 pocket pc clients. They
have no money for a SQLServer licens so we use a MSDE server.

/Mats.
 
No, it actually is a gigantic difference. Replication allows direct binary
transfer of the data in the table format, so basically the receiver simply
writes the incoming binary data. It doesn't do indexing, it doesn't do
parsing, it just writes. All "work" is done by the publisher.

Wehn you pull down data froma Web Service, you pull down the data, turn it
into a text SQL statement, the text is then parsed, the data is inserted,
indexes are handled, leaf rearrangement is handled - there's no way to get
it to not be *way* slower. The desktop is no different, nor is any other
DB. If you did the two on a desktop, even using MySQL, you'd see similar
differences because once is just a hell of a lot more work than the other.

-Chris
 
yes you are right... it can be expensive... in our case... only about 50
devices...
We were "lucky" however as the client already had a sql server cpu license
for their accounting software so we just leveraged what they had.

Regards,

éric
 
As to the question of licensing: if you are using data that came from a SQL
Server on your device, then that device will need a CAL (or the server needs
to be licensed in per processor mode). This is true even if you are not
using Sql CE.
Put another way, you will never have to pay more to use SQL CE. If your
solution uses Sql Server you'll need to pay the same licensing fees whether
you use a webservice, Replication, or a different mobile database. If your
solution does not include Sql Server then Sql CE can be used free of charge.
As far as the performance difference between Repl/RDA and custom inserts, as
Chris points out for Repl/RDA we can directly take the data from
connectivity and insert it into base tables without ever invoking managed
code. Using a webservice will require multiple inserts through the Query
Processor requires much more work, and is therefore slower. You can get
performance comparable to Repl/RDA if you're willing to P/Invoke to the Sql
Server CE Oledb layer to do the inserts. Or you could look at a third party
tool that does this like http://www.fitiri.com/SSCEDirect.html.
 
Hi Brian,

Please give me link where i can read about the licensing rules and prices
for SqlCe.

http://www.microsoft.com/sql/ce/howtobuy/20eula.asp

This link sais that it is forbidden to use sqlce with .Net.

NOTE: YOU MAY ONLY USE MICROSOFT SQL SERVER 2000 WINDOWS CE VERSION 2.0
DEVELOPER AND TEST EDITION IN CONJUNCTION WITH EITHER OR BOTH OF THE
FOLLOWING MICROSOFT PRODUCTS:MICROSOFT EMBEDDED VISUAL TOOLS 3.0 OR
MICROSOFT EMBEDDED VISUAL C++4.0. IF YOU DO NOT HAVE A VALID LICENSE FOR
EITHER OF THE PRODUCTS SPECIFIED IN THE PRECEDING SENTENCE, YOU ARE NOT
AUTHORIZED TO INSTALL, COPY, DOWNLOAD, ACCESS OR OTHERWISE USE SQL SERVER
2000 WINDOWS CE EDITION - DEVELOPER AND TEST EDITION.


This new information might force me to use MySql on the server and another
relational database on the client.

/Mats Boberg
 
Some of us just don't have an opportunity to use merge or replication (for
example our app synchronizes with server over different transport mediums
2Way radio, TMR radio etc) Still we have some 10000 records in our database
(mobile warehousing) and full records (drop table and insert all) update is
less then 5 min on PPC 2003 and about 10 min on PPC 2002 by using
transactions and parameterized query's before without transactions and
parameterized queries it would take more than an hour.



Daniel
 
Back
Top