RE: Data Insertion
From: Ilya Tumanov [MS] (ilyatum_at_online.microsoft.com)
Date: 03/31/04
- Next message: gfeighny: "URGENT! Create Database Error"
- Previous message: micha: "Mobile Devcon 2004 San Francisco"
- In reply to: Dave: "RE: Data Insertion"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 31 Mar 2004 19:38:38 GMT
Well, it's not a stellar performance, but it's not that bad considering
you're using entry level device and dealing with XML which is the most
inefficient way to store and transfer data ever invented (but the most
flexible).
VS 2003 you're using includes RTM version of a Compact Framework, so you're
not using SP2.
You might be able improve loading time it by a minute or two by upgrading
your device to SP2.
You can download it here:
http://www.microsoft.com/downloads/details.aspx?familyid=10600643-09b3-46d8-
ba28-bc494bc20d26&displaylang=en
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
> Thread-Topic: Data Insertion
> thread-index: AcQXR10g3+NFlYUSRpyAC69mYpr3Mw==
> X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
> From: "=?Utf-8?B?RGF2ZQ==?=" <anonymous@discussions.microsoft.com>
> References: <8F6152A2-6CE3-4887-876A-85349EE4AE69@microsoft.com>
<lVUadJdEEHA.3924@cpmsftngxa06.phx.gbl>
<06F32F48-C6C7-4097-9882-A6726F00BECE@microsoft.com>
<jFDCcVqEEHA.564@cpmsftngxa06.phx.gbl>
<3B85E9D8-03CA-4BEB-BACB-B6480F8DEEB2@microsoft.com>
<KBNqrd2EEHA.756@cpmsftngxa06.phx.gbl>
<38524CE5-0415-4A1E-B073-6C81C27DD395@microsoft.com>
<h89a7TrFEHA.616@cpmsftngxa06.phx.gbl>
> Subject: RE: Data Insertion
> Date: Wed, 31 Mar 2004 09:41:14 -0800
> Lines: 136
> Message-ID: <CDEE3DE2-CDE0-4225-95BD-90FA44306E88@microsoft.com>
> MIME-Version: 1.0
> Content-Type: text/plain;
> charset="Utf-8"
> Content-Transfer-Encoding: 7bit
> X-Newsreader: Microsoft CDO for Windows 2000
> Content-Class: urn:content-classes:message
> Importance: normal
> Priority: normal
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> Newsgroups: microsoft.public.dotnet.framework.compactframework
> Path: cpmsftngxa06.phx.gbl
> Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:49989
> NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
> X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
>
> Ilya,
I have upped the spec of the PDA.
The resulting time is just over 6 minutes.
But the spec of the machine is still inferior to that described by you as
standard.
I have a Dell Axim 300 mz with 32 mb ram(same as the old machine but this
one is new and running pocket pc 2003).
I know it's a poor machine but the statistics are looking better now and
aren't too inconsistent with those you provided.
I am using 1.1 of the .net framework , version 1.1.4322.
I am using a parametrised query:
cmd.CommandText = "INSERT INTO test(col_1 ,col_2....) VALUES (?,?.....)";
cmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar, 255,
"col_1"));
cmd.Parameters.Add(new SqlCeParameter("p2", SqlDbType.NVarChar, 50,
"col_2"));
As those in charge of the development want perhaps even larger data I may
have to go with an XMlTextreader solution or even accessing the base tables.
Thanks Ilya for all your help.
----- \"Ilya Tumanov [MS]\" wrote: -----
Please see inline...
Best regards,
Ilya
This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------
> Thread-Topic: Data Insertion
> thread-index: AcQWkb4hx5KE3N2cSMGF3sTfJ9I/KA==
> X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
> From: "=?Utf-8?B?RGF2ZQ==?=" <anonymous@discussions.microsoft.com>>
References:
<8F6152A2-6CE3-4887-876A-85349EE4AE69@microsoft.com><lVUadJdEEHA.3924@cpmsft
ngxa06.phx.gbl><06F32F48-C6C7-4097-9882-A6726F00BECE@microsoft.com><jFDCcVqE
EHA.564@cpmsftngxa06.phx.gbl><3B85E9D8-03CA-4BEB-BACB-B6480F8DEEB2@microsoft
com><KBNqrd2EEHA.756@cpmsftngxa06.phx.gbl>> Subject: RE: Data Insertion
> Date: Tue, 30 Mar 2004 12:01:08 -0800
> Lines: 20
> Message-ID: <38524CE5-0415-4A1E-B073-6C81C27DD395@microsoft.com>>
MIME-Version: 1.0
> Content-Type: text/plain;
> charset="Utf-8"
> Content-Transfer-Encoding: 7bit
> X-Newsreader: Microsoft CDO for Windows 2000
> Content-Class: urn:content-classes:message
> Importance: normal
> Priority: normal
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> Newsgroups: microsoft.public.dotnet.framework.compactframework
> Path: cpmsftngxa06.phx.gbl
> Xref: cpmsftngxa06.phx.gbl
microsoft.public.dotnet.framework.compactframework:49902
> NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
> X-Tomcat-NG: microsoft.public.dotnet.framework.compactframework
>> Hi Ilya,
>Thank you for your help.
>I have followed your suggestions.
>Loading a file of more than 4 mb into a dataset is using up lots of
memory.
It's not surprising considering 4 MB is from 6 to 12% of entire device
memory.
>The loading and insertion of the data is taking some twenty minutes.
>Doesn't this seem very , very long?
It is long. Few questions:
Which device are you using? Today's mainstream is PXA 255 400 MHz
based
devices running PPC 2003.
Which version of the framework do you have? SP2 includes major
performance
fix for loading XML into the DataSet.
Is your database located on a flash card? If yes, data insertion might
be
several times slower compared to RAM.
How long it takes to load data to DataSet from XML? How long it takes
to
push it from DataSet to SQL CE?
> Are there any benchmarks for data insertion with SQL CE and ADO.net?
I'm not aware of publicly available benchmarks, but we're running
internal
benchmarks constantly.
Here's sample data we have (Toshiba e750, PXA 255 400 MHz, SP2):
Load ~1 MB XML file into the DataSet with schema preloaded (3 nested
tables, 300 records in each table, 33 columns of various types
including
unique primary key in each record) - 22.8 seconds
Insert data described above from DataSet into SQL CE database in RAM
using
parameterized manually created insert command - 29.5 seconds
Load data from SQL CE into the DataSet - 7.5 seconds.
I would say, your 4.5 MB should be done in about (22.8 + 29.5) *
4.5/1= 236
seconds ~ 4 minutes.
Let's add one minute for increased garbage collection which would
result in
5 minutes.
20 minutes seems to be excessive to me, but this might be if you
running
slower device and using flash card.
If you have a fast device, running SP2 and not using flash card, that
means
something's wrong.
Please check if you're using parameterized query to insert data and
not
using command builder.
>It does not seem pratcical to load all the data into a dataset even
using
an embedded schema , reading the schema and then reading the data
>ignoring
the schema .
>The dataset solution does not seem to be a practical solution.
In this particular case whatever you do with DataSet is a pure
overhead.
You do not need DataSet per se; you just need to get data from XML to
SQL
CE.
Dumping it into the DataSet and into the SQL CE is not the most
efficient
way to do it.
>Do you have any idea if this technology is intended to be used on a
PDA
with a file of this size.
Generally, I would recommend using replication to get data into SQL
CE.
>Do you have any other suggestions?
The fastest solution is to use SQL replication. This would allow you
to
create and use database with 30-50K records, may be more.
>If I was to use the XMLTextReader method then repeatedly using a
command
object with ExecuteNonQuery and adding the records one by one >is this
going to be any slower than the dataset method?
Since SQL CE does not support batching, DataAdapter adds records from
DataSet to SQL CE one by one anyway.
So, it would be faster if carefully programmed. Before you do that,
let's
see if we can optimize DataSet path somehow.
>Thanks for all your help,
>Dave
>
>
- Next message: gfeighny: "URGENT! Create Database Error"
- Previous message: micha: "Mobile Devcon 2004 San Francisco"
- In reply to: Dave: "RE: Data Insertion"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
