RE: SQLCE Insert Performance - Assistance?

From: Ilya Tumanov [MS] (ilyatum_at_online.microsoft.com)
Date: 10/19/04 

Date: Tue, 19 Oct 2004 18:13:23 GMT

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 <a@b.com>
> 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: <hW8dd.63$t31.11@newsfe2-gui.ntli.net>
> 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
>

Relevant Pages

  • Re: can I replace a string in all Matlab .m files under a folder?
    ... How to write a program in Matlab to change the strings ... This is so much easier to do in Unix and perl that it is hardly worth ... the command that follows is to be executed. ...
    (comp.soft-sys.matlab)
  • Re: can I replace a string in all Matlab .m files under a folder?
    ... How to write a program in Matlab to change the strings ... This is so much easier to do in Unix and perl that it is hardly worth ... the command that follows is to be executed. ... The -i option signals that perl is to edit the ...
    (comp.soft-sys.matlab)
  • Re: can I replace a string in all Matlab .m files under a folder?
    ... How to write a program in Matlab to change the strings ... the command that follows is to be executed. ... The -i option signals that perl is to edit the ... The -p option signals that the perl commands ...
    (comp.soft-sys.matlab)
  • Re: input stream 101
    ... JTidy can be run from the command line on its own, ... > an array of Strings, ... > this would be a very easy change from your current code. ...
    (comp.lang.java.help)
  • RE: Fax service problem win2003
    ... As the problem is not caused by the permission, let us check the registry ... If these strings are not there have the customer, back up this key to the ... This posting is provided "AS IS" with no warranties, ...
    (microsoft.public.win2000.fax)