Re: SQLCE Insert Performance - Assistance?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Paul Eden (a_at_b.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 15:25:44 GMT

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] wrote:
> 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%40cpmsftngxa0
> 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 <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: Re: SQLCE Insert Performance - Assistance?
>>References: <hW8dd.63$t31.11@newsfe2-gui.ntli.net>
>
> <7ADpZdgtEHA.4028@cpmsftngxa06.phx.gbl>
>
>>In-Reply-To: <7ADpZdgtEHA.4028@cpmsftngxa06.phx.gbl>
>>Content-Type: text/plain; charset=us-ascii; format=flowed
>>Content-Transfer-Encoding: 7bit
>>Lines: 213
>>Message-ID: <Jbgdd.231$la5.15@newsfe1-win.ntli.net>
>>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 <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: SQLCE Insert Performance - Assistance?
    ... Ilya ... The Xml reading is faster than the inserting but it ... > storing in the arraylists, per record, is faster then the insert to ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: SQLCE Insert Performance - Assistance?
    ... The Xml reading is faster than the inserting but it ... whole area - about 1 year with CF and 6 months with XML. ... storing in the arraylists, per record, is faster then the insert to ... > supply values in the command itself. ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Problems Serializing A Collection Object
    ... For examples of serializing arrays and arraylists see ... > I'm trying to create xml seriaizable collection class, ... > Namespace MyCompany ... > Public Sub New ...
    (microsoft.public.dotnet.xml)
  • Re: New to XML. Need help reading XML.
    ... However I have more XML reading coming up so this is sure to come into play. ... Microsoft MSDN Online Support Lead ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.dotnet.xml)
  • XML::LibXML Issue
    ... I wanted to get some information regarding XML reading ... If I wanted to read Mass value from this XML. ... Anish ...
    (perl.beginners)