SQLCE Insert Performance - Assistance?

[Paul Eden]

Hi all

Firstly, yes, I have searched google on this and they all say basicly
the same - SQLCE is slow. This I already know.

Secondly, a desktop/server based SQL Server install is *not* an option.

Thirdy, this solution is based in the CF, hence my posting here - so
non-CF based/supporting solutions are of no use.


Right.


This is my table design:

CREATE TABLE PDASalesCustomer (
CustomerID int IDENTITY (1, 1) NOT NULL,
CustomerCode nvarchar (10) NOT NULL ,
CurrencyCode nvarchar (6) NULL ,
Balance NVarChar (10) NULL ,
CurrentBalance NVarChar (10) NULL ,
Balance1 NVarChar (10) NULL ,
Balance2 NVarChar (10) NULL ,
Balance3 NVarChar (10) NULL ,
Balance4 NVarChar (10) NULL ,
Balance5 NVarChar (10) NULL ,
Balance6 NVarChar (10) NULL ,
CreditLimit NVarChar (10) NULL ,
Hold bit NULL ,
DefaultPriceStream int NOT NULL ,
UsrField1 nvarchar (20) NULL ,
UsrField2 nvarchar (20) NULL ,
PriceRef nvarchar (6) NULL ,
CompanyID int NOT NULL ,
IsNewCustomer bit NOT NULL
)

All fields are strings where possible (used to be numeric type) as I saw
a post that said it reduced the processing overhead to properly format
the data. This, however, made ~no~ difference to insert speed.
I currently get 1 insert/second (or there abouts).

PDA is a iPaq 3700 serise with 400Mhz Strongarm processor (according to
PocketCOntroller) and PPC2002, which states the processor as ARM SA1110.

I use parameters in my insert command and do not use Prepare.

I'm pretty sure it's the actual insert statment that causes the
significant protion of the 1 second (through single stepping), sooo
finally, here's the question:

I'm confident that my code is doing everything correct and as
efficiently as possible, but can anybody suggest any improvements that I
could apply that I've not done or ruled out already?



Many thanks


Paul

 

[Jon Skeet [C# MVP]]

I'm confident that my code is doing everything correct and as
efficiently as possible, but can anybody suggest any improvements that I
could apply that I've not done or ruled out already?

You could do it in native code. If you search for SSCEDirect you'll
find a company which will sell you a library to make it easy. I managed
to come up with my own mini-library (handling fewer types etc, but
still robust) in about a day.

(If I ever get the chance, I'll open-source it; currently it's
commercial though.)

 

[Jon Skeet [C# MVP]]

You could do it in native code. If you search for SSCEDirect you'll
find a company which will sell you a library to make it easy. I managed
to come up with my own mini-library (handling fewer types etc, but
still robust) in about a day.

I should have specified - the performance improvement is about 1000%
(i.e. it makes it about 10 times faster). At least, that was the case
with the particular table I was using.

 

[Steven Licciardi]

I've no idea why it takes 1 second/insert, that is incredibly slow. But
have you tried creating a DataAdapter, populating the data set then using
adapter.update? Or is that how you are already doing it? Also, is it
possible that you are opening/closing the db connection between insert
commands?

Steven

 

[Paul Eden]

My source data is XML - I started off by loading it into a dataset (but
not making use of adapter as there were field incosistencies with the
database which are currently being ironed out), but that was insanely
slow (some potentially larger-than-life files). I now use an XMLReader
and stream though it. I may look into the adapter approach.

As for the Open/Close overhead, I have that coverd already - one open,
insert a files worth of data then close and next file.



Re: Jon

Native could be akward - I'm a VB'er and I don't have the experience,
but thanks for the post.


Paul

I've no idea why it takes 1 second/insert, that is incredibly slow. But
have you tried creating a DataAdapter, populating the data set then using
adapter.update? Or is that how you are already doing it? Also, is it
possible that you are opening/closing the db connection between insert
commands?

Steven

 

[Jon Skeet [C# MVP]]

Native could be akward - I'm a VB'er and I don't have the experience,
but thanks for the post.

Well, you can always plump for the "buy" option then, buying licences
for SSCEDirect. It's very easy to use. It's definitely worth giving a
go, just so you know what's available.

If it's any consolation, I didn't know any eVC before writing the
library...

 

[Ilya Tumanov [MS]]

You do not need to use strings instead of whatever type it actually is.
If you using parameters, there's no conversion, it only take place if you
supply values in the command itself.

Device you're using actually has 206MHz StrongARM CPU, but even so it
should not be that slow.
In our tests we're getting 1200 records (about twice more complex than the
one you have) inserted in 33 seconds on Toshiba E750 (which is probably
twice as fast as your iPAQ).
Data is coming from DataSet. DataAdapter with manually created insert
command with parameters is used.

You've stated you've been using DataSet, but it was too slow to load XML
into it...
Do you have schema loaded in this DataSet prior to data? Do you have CF SP2
installed?

Now, using XmlTextReader to parse XML and insert data into SQL CE is a very
good idea.
Using XmlDocument to do the same is, however, a very bad one. Which one are
you using?
Can you post some code samples, please? Thanks.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------

 

[Pete Vickers [eMVP]]

Hi,
I just tested out our RemoteSQLCe inserting 930 records (nvarchar 20,
nvarchar 255 and datetime) and that did it in 24 seconds.
That is almost 40 per second, and that is loading the records using TCP/IP
over activsync, and just doing a straight 'insert into'.

Moving the script file to the PPC and reading the 930 records and doing
inserts completes in less than 10 seconds, yielding almost 100 records per
second. The program simply reads the file, and does an insert.

This is on an XDAii (400mhz processor (i think) with 128mb ram).

Judging from this, you should be getting far better results than you are

HTH

Pete

--
Pete Vickers
Microsoft .NET Compact Framework MVP
HP Business Partner
http://www.gui-innovations.com

Do have an opinion on the effectiveness of Microsoft Windows Mobile and
Embedded newsgroups? Let us know!
https://www.windowsembeddedeval.com/community/newsgroups

 

[Paul Eden]

Ilya

An enlightening response - really 206Mhz? That would explaine why XDAs
are so much faster (actually, pretty much every device has been
faster!). Also, thanks for clearing up about the usage of strings etc.

With the dataset, initially I was not reading in the schema, but after
seeing a post that advised much the same as yourself I corrected my
code, but was disappointed to see that no ~noticeable~ increase was
gained - and service pack 2 is installed (and re-installed a couple of
times through the process of things).

I am indeed using the XmlTextReader as I had read bad press on the
XmlDocument method. The Xml reading is faster than the inserting but it
took me a few attempts to get it right as I'm relatively new to the
whole area - about 1 year with CF and 6 months with XML.

My basic psudo-ish code for the process is:

While XmlReader.Read
While Node type isn't an element
XmlReader.Read

If end of Xml
Exit

Select Case XmlReader.Name
'// my record tag is <Customer>
Case "Customer"
'// I found that this method caused a requirment
'// to add to my data arraylist before the start
'// of the next record, so the first execution
'// of the following occurs just before the
'// second xml record

If not first record
add any default field values that not _
present in xml



'// Each field that I want in the database appears as a
'// case statement
Case <named case>
Collect the field name and store in collection_
for dynamic commandtext and parameter generation

Store element value via XmlReader.ReadString



That is the basic description of how I do it, as I have nested tables in
the Xml, but I feel I should give reasons:

Data from each file is put into an arraylist of araylists (one
arraylist, each index being a separate arraylist acting as a record
whose indexes are the field values), so that in a separate Sub I can
open DB, then set parameter values and execute insert for each inedx in
the top-most arraylist then close the database.

The field names are collected in order to have one Sub for each filetype
(customer data, product data etc..) to enable dynamic CommandObject
creation to cater for optional XML fields.

It's possibly not the most elegant solution, but the XML reading and
storing in the arraylists, per record, is faster then the insert to
database per record (at a rough guess, I'd say 2-fold).

As an overall timing, inserting approximately 5000 records into 2 tables
(the one I already posted as well as another of about 7 VarChar fields
for contact details), as well as the associated xml processing, took 30
minutes to complete.


If you need any more info, just ask.


Regards

Paul

 

[Paul Eden]

Assuming I punched buttons in the right order, a very rough estimate
would be that with the total number of fields that I use (37 - see reply
to Ilya) I should acheive 1 insert per 0.2 seconds on a similar device
(using the generalisation that you were geting 1 insert /0.008 sec based
on 3 fields).

Hopefully I should be able to get my grubby mits on an XDAii the tail
end of this week to see if it rings true.

Thanks for your input.


Paul

 

[Ilya Tumanov [MS]]

Correct, 206 MHz. I've no idea why it's 206, not 200.
May be Intel was trying to kill several birds with one stone just like IBM
developers did 20 years ago (remember 8088 running at 4.77MHz?).

206MHz is actually not that bad. First XScale processors (PXA250@400MHz)
were generally slower than 206MHz StrongARM you have.
Also, PPC 2003 OS is significantly faster compared to PPC 2002.

I think using an array lists to store data negates the benefits of using
XmlTextReader.
It would be way better if you load data for a single row and insert it
right away without storing bulk of data in memory.
Also, it's not clear to me if you recreating insert command for each row or
reusing existing one.

I would suggest trying this code:
http://groups.google.com/groups?hl=en&lr=&selm=PT87MQ5oEHA.752@cpmsftngxa0
6.phx.gbl
It takes a DataSet and creates matching SQL CE database.
If you're using VB you should be able to create a DLL in C# and reference
it in VB project.

You should use schema to load data into the DataSet before you call that
code. Please let me know how it works for you.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------

 

[Guest]

IT [Tue, 19 Oct 2004 22:40:53 GMT]:

206MHz is actually not that bad. First XScale processors (PXA250@400MHz)
were generally slower than 206MHz StrongARM you have.

You can find a comparitive benchmark, database processing
as a matter of fact, for these three iPAQs (including that
first XScale CPU you mentioned):

2215: 2003, PXA255-400
3970: 2002, PXA250-400
3650: 2000, SA1-203

here:

http://gt40.40th.com/bench_gt40_arm.html

with a summary below. You may notice that the PXA250/400 is actually
faster than the older SA1/206. You can download the BM app and run
it for yourself (link to that is on the page above) if you want.

Also, PPC 2003 OS is significantly faster compared to PPC 2002.

I hear 15% overall. And only on some devices. But it is
very noticable/useful on the 3970 (going from 2002 -> 2003).
HP/Compaq still sells this upgrade CD.


Benchmark summary:

Performance summary
------------------------------------------------------------------------------

PXA255/400, Insert: 16,000 items per second (insert rate: 960,000 items/min
Read: 333,000 items per second (read rate: 20+ million items/min

PXA250/400, Insert: 7,000 items per second (insert rate: 425,000 items/min
Read: 200,000 items per second (read rate: 12+ million items/min

SA1110/206, Insert: 3,900 items per second (insert rate: 234,000 items/min
Read: 185,000 items per second (read rate: 11+ million items/min

Notes
------------------------------------------------------------------------------
o The index data structure remains balanced during inserts and deletes (66%
node utilization typical)
o Deletes physically remove the key and data items
o Deleted space is immediately available for reuse
o Optimize can produce node utilization > 95%

 

[Paul Eden]

Ilya

Will look at the example when I get a chance, but it won't have any
effect on the slow insert speed, which is my current bottle-neck. To
this end I assume there is nothing I can do?


Paul

 

[Ilya Tumanov [MS]]

I actually hope it will speed up insert.
That's the actual code we use in performance tests and we do see higher
performance.

By the way, did you considered using CF V2? Performance was nearly doubled
and you can create DB on desktop.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

From: Paul Eden <[email protected]>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.3) Gecko/20040910
X-Accept-Language: en, en-us
MIME-Version: 1.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
Subject: Re: SQLCE Insert Performance - Assistance?
References: <[email protected]>

<[email protected]>

In-Reply-To: <[email protected]>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 334
Message-ID: <[email protected]>
Date: Wed, 20 Oct 2004 15:25:44 GMT
NNTP-Posting-Host: 81.99.72.221
X-Complaints-To: http://www.ntlworld.com/netreport
X-Trace: newsfe4-gui.ntli.net 1098285944 81.99.72.221 (Wed, 20 Oct 2004 16:25:44 BST)
NNTP-Posting-Date: Wed, 20 Oct 2004 16:25:44 BST
Organization: ntl Cablemodem News Service
Path: cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!tiscali!newsfeed1.ip.tiscali.net!border2.nntp.ams
.giganews.com!border1.nntp.ams.giganews.com!nntp.giganews.com!news-in.ntli.n
et!newsrout1-win.ntli.net!ntli.net!newspeer1-win.ntli.net!newsfe4-gui.ntli.n
et.POSTED!53ab2750!not-for-mail
Xref: cpmsftngxa10.phx.gbl microsoft.public.dotnet.framework.compactframework:63538
X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Ilya

Will look at the example when I get a chance, but it won't have any
effect on the slow insert speed, which is my current bottle-neck. To
this end I assume there is nothing I can do?


Paul

Correct, 206 MHz. I've no idea why it's 206, not 200.
May be Intel was trying to kill several birds with one stone just like IBM
developers did 20 years ago (remember 8088 running at 4.77MHz?).

206MHz is actually not that bad. First XScale processors (PXA250@400MHz)
were generally slower than 206MHz StrongARM you have.
Also, PPC 2003 OS is significantly faster compared to PPC 2002.

I think using an array lists to store data negates the benefits of using
XmlTextReader.
It would be way better if you load data for a single row and insert it
right away without storing bulk of data in memory.
Also, it's not clear to me if you recreating insert command for each row or
reusing existing one.

I would suggest trying this code:
http://groups.google.com/groups?hl=en&lr=&selm=PT87MQ5oEHA.752@cpmsftngxa0
6.phx.gbl
It takes a DataSet and creates matching SQL CE database.
If you're using VB you should be able to create a DLL in C# and reference
it in VB project.

You should use schema to load data into the DataSet before you call that
code. Please let me know how it works for you.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------

From: Paul Eden <[email protected]>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.3)
Gecko/20040910

X-Accept-Language: en, en-us
MIME-Version: 1.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
Subject: Re: SQLCE Insert Performance - Assistance?
References: <[email protected]>

In-Reply-To: <[email protected]>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 213
Message-ID: <[email protected]>
Date: Tue, 19 Oct 2004 22:04:25 GMT
NNTP-Posting-Host: 81.99.72.221
X-Complaints-To: http://www.ntlworld.com/netreport
X-Trace: newsfe1-win.ntli.net 1098223465 81.99.72.221 (Tue, 19 Oct 2004

23:04:25 BST)

NNTP-Posting-Date: Tue, 19 Oct 2004 23:04:25 BST
Organization: ntl Cablemodem News Service
Path:

cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!news.glorb.com!border1.nntp.dca.giganews.com!nntp
giganews.com!peer01.cox.net!cox.net!news-out.ntli.net!newsrout1-gui.ntli.ne
t!ntli.net!newspeer1-win.ntli.net!newsfe1-win.ntli.net.POSTED!53ab2750!not-f
or-mail

Xref: cpmsftngxa10.phx.gbl
microsoft.public.dotnet.framework.compactframework:63478

X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Ilya

An enlightening response - really 206Mhz? That would explaine why XDAs
are so much faster (actually, pretty much every device has been
faster!). Also, thanks for clearing up about the usage of strings etc.

With the dataset, initially I was not reading in the schema, but after
seeing a post that advised much the same as yourself I corrected my
code, but was disappointed to see that no ~noticeable~ increase was
gained - and service pack 2 is installed (and re-installed a couple of
times through the process of things).

I am indeed using the XmlTextReader as I had read bad press on the
XmlDocument method. The Xml reading is faster than the inserting but it
took me a few attempts to get it right as I'm relatively new to the
whole area - about 1 year with CF and 6 months with XML.

My basic psudo-ish code for the process is:

While XmlReader.Read
While Node type isn't an element
XmlReader.Read

If end of Xml
Exit

Select Case XmlReader.Name
'// my record tag is <Customer>
Case "Customer"
'// I found that this method caused a requirment
'// to add to my data arraylist before the start
'// of the next record, so the first execution
'// of the following occurs just before the
'// second xml record

If not first record
add any default field values that not _
present in xml



'// Each field that I want in the database appears as a
'// case statement
Case <named case>
Collect the field name and store in collection_
for dynamic commandtext and parameter generation

Store element value via XmlReader.ReadString



That is the basic description of how I do it, as I have nested tables in
the Xml, but I feel I should give reasons:

Data from each file is put into an arraylist of araylists (one
arraylist, each index being a separate arraylist acting as a record
whose indexes are the field values), so that in a separate Sub I can
open DB, then set parameter values and execute insert for each inedx in
the top-most arraylist then close the database.

The field names are collected in order to have one Sub for each filetype
(customer data, product data etc..) to enable dynamic CommandObject
creation to cater for optional XML fields.

It's possibly not the most elegant solution, but the XML reading and
storing in the arraylists, per record, is faster then the insert to
database per record (at a rough guess, I'd say 2-fold).

As an overall timing, inserting approximately 5000 records into 2 tables
(the one I already posted as well as another of about 7 VarChar fields
for contact details), as well as the associated xml processing, took 30
minutes to complete.


If you need any more info, just ask.


Regards

Paul


Ilya Tumanov [MS] wrote:

You do not need to use strings instead of whatever type it actually is.
If you using parameters, there's no conversion, it only take place if
you

supply values in the command itself.

Device you're using actually has 206MHz StrongARM CPU, but even so it
should not be that slow.
In our tests we're getting 1200 records (about twice more complex than
the

one you have) inserted in 33 seconds on Toshiba E750 (which is probably
twice as fast as your iPAQ).
Data is coming from DataSet. DataAdapter with manually created insert
command with parameters is used.

You've stated you've been using DataSet, but it was too slow to load
XML

into it...
Do you have schema loaded in this DataSet prior to data? Do you have

CF

SP2

installed?

Now, using XmlTextReader to parse XML and insert data into SQL CE is a
very

good idea.
Using XmlDocument to do the same is, however, a very bad one. Which

one

are

you using?
Can you post some code samples, please? Thanks.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no
rights.

--------------------


From: Paul Eden <[email protected]>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.3)

Gecko/20040910


X-Accept-Language: en, en-us
MIME-Version: 1.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
Subject: SQLCE Insert Performance - Assistance?
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 62
Message-ID: <[email protected]>
Date: Tue, 19 Oct 2004 13:47:57 GMT
NNTP-Posting-Host: 81.99.72.221
X-Complaints-To: http://www.ntlworld.com/netreport
X-Trace: newsfe2-gui.ntli.net 1098193677 81.99.72.221 (Tue, 19 Oct 2004

14:47:57 BST)


NNTP-Posting-Date: Tue, 19 Oct 2004 14:47:57 BST
Organization: ntl Cablemodem News Service
Path:

cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin

e.de!newsfeed.wirehub.nl!border1.nntp.ams.giganews.com!nntp.giganews.com!new

s-in.ntli.net!newsrout1-win.ntli.net!ntli.net!newspeer1-win.ntli.net!newsfe2

-gui.ntli.net.POSTED!53ab2750!not-for-mail


Xref: cpmsftngxa06.phx.gbl

microsoft.public.dotnet.framework.compactframework:63402


X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hi all

Firstly, yes, I have searched google on this and they all say basicly
the same - SQLCE is slow. This I already know.

Secondly, a desktop/server based SQL Server install is *not* an option.

Thirdy, this solution is based in the CF, hence my posting here - so
non-CF based/supporting solutions are of no use.


Right.


This is my table design:

CREATE TABLE PDASalesCustomer (
CustomerID int IDENTITY (1, 1) NOT NULL,
CustomerCode nvarchar (10) NOT NULL ,
CurrencyCode nvarchar (6) NULL ,
Balance NVarChar (10) NULL ,
CurrentBalance NVarChar (10) NULL ,
Balance1 NVarChar (10) NULL ,
Balance2 NVarChar (10) NULL ,
Balance3 NVarChar (10) NULL ,
Balance4 NVarChar (10) NULL ,
Balance5 NVarChar (10) NULL ,
Balance6 NVarChar (10) NULL ,
CreditLimit NVarChar (10) NULL ,
Hold bit NULL ,
DefaultPriceStream int NOT NULL ,
UsrField1 nvarchar (20) NULL ,
UsrField2 nvarchar (20) NULL ,
PriceRef nvarchar (6) NULL ,
CompanyID int NOT NULL ,
IsNewCustomer bit NOT NULL
)

All fields are strings where possible (used to be numeric type) as I
saw

a post that said it reduced the processing overhead to properly format
the data. This, however, made ~no~ difference to insert speed.
I currently get 1 insert/second (or there abouts).

PDA is a iPaq 3700 serise with 400Mhz Strongarm processor (according to
PocketCOntroller) and PPC2002, which states the processor as ARM SA1110.

I use parameters in my insert command and do not use Prepare.

I'm pretty sure it's the actual insert statment that causes the
significant protion of the 1 second (through single stepping), sooo
finally, here's the question:

I'm confident that my code is doing everything correct and as
efficiently as possible, but can anybody suggest any improvements

that

I

could apply that I've not done or ruled out already?



Many thanks


Paul

 

[Paul Eden]

I would love to be able to use V2 - unfortunately I have to get a
version up and running before my company will consider upgrading to
VS2005... unless I'm missing something about versions - can it run with
VS2003?

I toyed with the concept you mentioned for another file that I need to
load and wow.. yup OK, definately faster!
I'll be incorporating it for the customer and product files when I get a
chance - many thanks for your input!



Paul

I actually hope it will speed up insert.
That's the actual code we use in performance tests and we do see higher
performance.

By the way, did you considered using CF V2? Performance was nearly doubled
and you can create DB on desktop.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

From: Paul Eden <[email protected]>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.3)
Gecko/20040910

X-Accept-Language: en, en-us
MIME-Version: 1.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
Subject: Re: SQLCE Insert Performance - Assistance?
References: <[email protected]>

<[email protected]>
<[email protected]>

In-Reply-To: <[email protected]>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 334
Message-ID: <[email protected]>
Date: Wed, 20 Oct 2004 15:25:44 GMT
NNTP-Posting-Host: 81.99.72.221
X-Complaints-To: http://www.ntlworld.com/netreport
X-Trace: newsfe4-gui.ntli.net 1098285944 81.99.72.221 (Wed, 20 Oct 2004

16:25:44 BST)

NNTP-Posting-Date: Wed, 20 Oct 2004 16:25:44 BST
Organization: ntl Cablemodem News Service
Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.s
ul.t-online.de!t-online.de!tiscali!newsfeed1.ip.tiscali.net!border2.nntp.ams
giganews.com!border1.nntp.ams.giganews.com!nntp.giganews.com!news-in.ntli.n
et!newsrout1-win.ntli.net!ntli.net!newspeer1-win.ntli.net!newsfe4-gui.ntli.n
et.POSTED!53ab2750!not-for-mail

Xref: cpmsftngxa10.phx.gbl
microsoft.public.dotnet.framework.compactframework:63538

X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Ilya

Will look at the example when I get a chance, but it won't have any
effect on the slow insert speed, which is my current bottle-neck. To
this end I assume there is nothing I can do?


Paul

IBM

(PXA250@400MHz)

using

row or

reusing existing one.

I would suggest trying this code:

http://groups.google.com/groups?hl=en&lr=&selm=PT87MQ5oEHA.752@cpmsftngxa0

6.phx.gbl
It takes a DataSet and creates matching SQL CE database.
If you're using VB you should be able to create a DLL in C# and

reference

it in VB project.

You should use schema to load data into the DataSet before you call

that

code. Please let me know how it works for you.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no

rights.

--------------------


From: Paul Eden <[email protected]>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.3)

Gecko/20040910


X-Accept-Language: en, en-us
MIME-Version: 1.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
Subject: Re: SQLCE Insert Performance - Assistance?
References: <[email protected]>

<[email protected]>

In-Reply-To: <[email protected]>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 213
Message-ID: <[email protected]>
Date: Tue, 19 Oct 2004 22:04:25 GMT
NNTP-Posting-Host: 81.99.72.221
X-Complaints-To: http://www.ntlworld.com/netreport
X-Trace: newsfe1-win.ntli.net 1098223465 81.99.72.221 (Tue, 19 Oct 2004

23:04:25 BST)


NNTP-Posting-Date: Tue, 19 Oct 2004 23:04:25 BST
Organization: ntl Cablemodem News Service
Path:

cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.s

ul.t-online.de!t-online.de!news.glorb.com!border1.nntp.dca.giganews.com!nntp

giganews.com!peer01.cox.net!cox.net!news-out.ntli.net!newsrout1-gui.ntli.ne

t!ntli.net!newspeer1-win.ntli.net!newsfe1-win.ntli.net.POSTED!53ab2750!not-f

or-mail


Xref: cpmsftngxa10.phx.gbl

microsoft.public.dotnet.framework.compactframework:63478


X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Ilya

An enlightening response - really 206Mhz? That would explaine why XDAs
are so much faster (actually, pretty much every device has been
faster!). Also, thanks for clearing up about the usage of strings etc.

With the dataset, initially I was not reading in the schema, but after
seeing a post that advised much the same as yourself I corrected my
code, but was disappointed to see that no ~noticeable~ increase was
gained - and service pack 2 is installed (and re-installed a couple of
times through the process of things).

I am indeed using the XmlTextReader as I had read bad press on the
XmlDocument method. The Xml reading is faster than the inserting but

it

took me a few attempts to get it right as I'm relatively new to the
whole area - about 1 year with CF and 6 months with XML.

My basic psudo-ish code for the process is:

While XmlReader.Read
While Node type isn't an element
XmlReader.Read

If end of Xml
Exit

Select Case XmlReader.Name
'// my record tag is <Customer>
Case "Customer"
'// I found that this method caused a requirment
'// to add to my data arraylist before the start
'// of the next record, so the first execution
'// of the following occurs just before the
'// second xml record

If not first record
add any default field values that not _
present in xml



'// Each field that I want in the database appears as a
'// case statement
Case <named case>
Collect the field name and store in collection_
for dynamic commandtext and parameter generation

Store element value via XmlReader.ReadString



That is the basic description of how I do it, as I have nested tables

in

the Xml, but I feel I should give reasons:

Data from each file is put into an arraylist of araylists (one
arraylist, each index being a separate arraylist acting as a record
whose indexes are the field values), so that in a separate Sub I can
open DB, then set parameter values and execute insert for each inedx in
the top-most arraylist then close the database.

The field names are collected in order to have one Sub for each

filetype

(customer data, product data etc..) to enable dynamic CommandObject
creation to cater for optional XML fields.

It's possibly not the most elegant solution, but the XML reading and
storing in the arraylists, per record, is faster then the insert to
database per record (at a rough guess, I'd say 2-fold).

As an overall timing, inserting approximately 5000 records into 2

tables

(the one I already posted as well as another of about 7 VarChar fields
for contact details), as well as the associated xml processing, took 30
minutes to complete.


If you need any more info, just ask.


Regards

Paul


Ilya Tumanov [MS] wrote:


You do not need to use strings instead of whatever type it actually

is.

If you using parameters, there's no conversion, it only take place if

you


supply values in the command itself.

Device you're using actually has 206MHz StrongARM CPU, but even so it
should not be that slow.
In our tests we're getting 1200 records (about twice more complex than

the


one you have) inserted in 33 seconds on Toshiba E750 (which is

probably

twice as fast as your iPAQ).
Data is coming from DataSet. DataAdapter with manually created insert
command with parameters is used.

You've stated you've been using DataSet, but it was too slow to load

XML


into it...
Do you have schema loaded in this DataSet prior to data? Do you have

CF

SP2


installed?

Now, using XmlTextReader to parse XML and insert data into SQL CE is a

very


good idea.
Using XmlDocument to do the same is, however, a very bad one. Which

one

are


you using?
Can you post some code samples, please? Thanks.

Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no

rights.


--------------------



From: Paul Eden <[email protected]>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.3)

Gecko/20040910



X-Accept-Language: en, en-us
MIME-Version: 1.0
Newsgroups: microsoft.public.dotnet.framework.compactframework
Subject: SQLCE Insert Performance - Assistance?
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 62
Message-ID: <[email protected]>
Date: Tue, 19 Oct 2004 13:47:57 GMT
NNTP-Posting-Host: 81.99.72.221
X-Complaints-To: http://www.ntlworld.com/netreport
X-Trace: newsfe2-gui.ntli.net 1098193677 81.99.72.221 (Tue, 19 Oct

2004

14:47:57 BST)



NNTP-Posting-Date: Tue, 19 Oct 2004 14:47:57 BST
Organization: ntl Cablemodem News Service
Path:

cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onlin
e.de!newsfeed.wirehub.nl!border1.nntp.ams.giganews.com!nntp.giganews.com!new
s-in.ntli.net!newsrout1-win.ntli.net!ntli.net!newspeer1-win.ntli.net!newsfe2

-gui.ntli.net.POSTED!53ab2750!not-for-mail



Xref: cpmsftngxa06.phx.gbl

microsoft.public.dotnet.framework.compactframework:63402



X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework

Hi all

Firstly, yes, I have searched google on this and they all say basicly
the same - SQLCE is slow. This I already know.

Secondly, a desktop/server based SQL Server install is *not* an

option.

Thirdy, this solution is based in the CF, hence my posting here - so
non-CF based/supporting solutions are of no use.


Right.


This is my table design:

CREATE TABLE PDASalesCustomer (
CustomerID int IDENTITY (1, 1) NOT NULL,
CustomerCode nvarchar (10) NOT NULL ,
CurrencyCode nvarchar (6) NULL ,
Balance NVarChar (10) NULL ,
CurrentBalance NVarChar (10) NULL ,
Balance1 NVarChar (10) NULL ,
Balance2 NVarChar (10) NULL ,
Balance3 NVarChar (10) NULL ,
Balance4 NVarChar (10) NULL ,
Balance5 NVarChar (10) NULL ,
Balance6 NVarChar (10) NULL ,
CreditLimit NVarChar (10) NULL ,
Hold bit NULL ,
DefaultPriceStream int NOT NULL ,
UsrField1 nvarchar (20) NULL ,
UsrField2 nvarchar (20) NULL ,
PriceRef nvarchar (6) NULL ,
CompanyID int NOT NULL ,
IsNewCustomer bit NOT NULL
)

All fields are strings where possible (used to be numeric type) as I

saw


a post that said it reduced the processing overhead to properly

format

the data. This, however, made ~no~ difference to insert speed.
I currently get 1 insert/second (or there abouts).

PDA is a iPaq 3700 serise with 400Mhz Strongarm processor (according

to

PocketCOntroller) and PPC2002, which states the processor as ARM

SA1110.

I use parameters in my insert command and do not use Prepare.

I'm pretty sure it's the actual insert statment that causes the
significant protion of the 1 second (through single stepping), sooo
finally, here's the question:

I'm confident that my code is doing everything correct and as
efficiently as possible, but can anybody suggest any improvements

that

I


could apply that I've not done or ruled out already?



Many thanks


Paul

 

[Guest]

Hi Paul!!!

I'm actually suffering from the same problem here. However, I saw your
table definition and I thought about suggesting changing one of your columns
and test if it helps:
....
CustomerID int IDENTITY (1, 1) NOT NULL Primary Key,
....

That will make your insterts slower, BUT IT MAY dramatically improve
your updates and selects if you are using the CustomerID as your filter
criteria (which I'm pretty sure you're using).

I can tell you that in my opinion the problem is SQLCE. In my case, I'm
doing direct inserts using rsCommand.ExecuteNonQuery(), and I'm getting a
performance of 10 rows per second. It kind of looks bad, however, for every
record I insert I first check if it is already there, and if so I just update
the information (don't ask why). That overhead justifies my low performance.

Hope it helps!!!

Tarh ik

 

[Darren Shaffer]

Paul,

This suggestion is a little off topic and doesn't answer the question you
asked,
but I have the same device you mentioned and found that the difference with
PPC 2003 and the 400Mhx processor compared to what it was with PPC 2002
was *significantly* different. You might think about upgrading to PPC 2003
with
the upgrade available from HP (it's like 30 bucks).

-Darren