Re: SQLCE Insert Performance - Assistance?
From: Paul Eden (a_at_b.com)
Date: 10/19/04
- Next message: Jake: "File.Exists strangeness"
- Previous message: Ercan Turkarslan [MS]: "RE: Program Loading. Splash Screen?"
- In reply to: Ilya Tumanov [MS]: "RE: SQLCE Insert Performance - Assistance?"
- Next in thread: Ilya Tumanov [MS]: "Re: SQLCE Insert Performance - Assistance?"
- Reply: Ilya Tumanov [MS]: "Re: SQLCE Insert Performance - Assistance?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 19 Oct 2004 22:04:25 GMT
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 <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
>>
>
>
- Next message: Jake: "File.Exists strangeness"
- Previous message: Ercan Turkarslan [MS]: "RE: Program Loading. Splash Screen?"
- In reply to: Ilya Tumanov [MS]: "RE: SQLCE Insert Performance - Assistance?"
- Next in thread: Ilya Tumanov [MS]: "Re: SQLCE Insert Performance - Assistance?"
- Reply: Ilya Tumanov [MS]: "Re: SQLCE Insert Performance - Assistance?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
