DB performance question

  • Thread starter Thread starter Keld Laursen
  • Start date Start date
K

Keld Laursen

Hi all.

I am currently porting an old eVB program to .NET. (Well, i am rewriting the
program... :) )
I am a little curious about performance of data access (especially selects),
so I would like to hear what others have found.
(I just read the thread about insert performance, but it isn't really my
problem as I will do quite a lot of selects, but only occational
inserts/updates, and never bulk inserts/updates).

My old program were using Pocket Access tables, and in order to get on the
road fast, we decided to stick to those for the time being.
In order to access these, we bought a copy of Peter Foots AdoCE InTheHand.
Trying out a couple of things, we found that selects work quite fast, but
that we have a bottlenect in getting data out of the resulting dataset.
We tried data adapters, recordsets etc., but ended up with reading directly
form the InTheHand.Data.Adoce.FieldCollection.
From that we could get a field value within 4 mSec (on our test machine).
But getting 10 values per record, and, say 10 records to a dataset, this
adds up to nearly half a second.

Do any of you have any experience with AdoCE InTheHand? What is your
experience with regards to speed?
Alternatively? What can I expect from SSCE with regards to speed?
(Example: Get 10 records with 10 fields from a single table with, say 100
records in it?)

We want to populate an object tree model using the data we select, and these
data will in turn be used populate a grid and a treeview.

TIA

Keld Laursen
 
Are you accessing the fields by their index or by name? How many fields does
your table have in total? Pocket Access' performance really starts to drop
once you get to around 10000 rows, although this varies depending on the
types used and indexes applied. If you are pushing the limits of Pocket
Access then you'll find Sql CE faster (Pocket Access generally beats Sql Ce
performance for small databases but isn't suited to large volumes of data).

Peter
 
In our tests SQL CE 2.0/CF V1 loads 1200 records of 33 fields of various
types into the DataSet in under 8 seconds on PXA255-400 MHz.



SQL Mobile/CF V2 does the same in 3.5 seconds. Also, new SqlCeResultSet
class allows you to bind SQL Mobile database directly to UI controls without
creating a copy of data in memory.


Best regards,

Ilya

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

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).
 
Ilya,

Could you please point to some resource (online, in the SDK, newsgroup
posting, etc.) that details how to bind UI controls to SQL Mobile databases?

--
_________________________________
Jared Miniman
Accenture Mobile Solutions Group
 
Hi, Peter.

Peter Foot said:
Are you accessing the fields by their index or by name? How many fields
does your table have in total? Pocket Access' performance really starts to
drop once you get to around 10000 rows, although this varies depending on
the types used and indexes applied. If you are pushing the limits of
Pocket Access then you'll find Sql CE faster (Pocket Access generally
beats Sql Ce performance for small databases but isn't suited to large
volumes of data).
Click to expand...

I tried writing to InTheHand (which is you, I presume ;) ), but haven't
heard anything from them.

We have tried a lot of different approaches. It doesn't seem to affect
performance to a measurable degree whether we address fields by name or by
index.
The database is identical to what I used under eVB, and the selects
themselves should perform more or less like they did there. Or so is my
expectations.

The worst table I have is around 7500 records, and I haven't seen bad
performance there.

My real problem is getting the data from the result set (the select performs
quite well, taking very few milliseconds). It is the loop where we pull data
out of the result set that slows things down. Of course; I don't know if
data are pulled directly from the database at this point, but I'd expect
them to be an an ADOCE recordset.

Is it the conversion from variant data to the expected datatype that's
eating up the processor time?

Re SSCE: Originally I did some tests on both ADOCE and SSCE using eVB. I
weren't able to get a better performance out of SSCE than of ADOCE at that
time, but maybe this is (partly) because the tables and resulting data sets
were too small?

/Keld Laursen
 
Ilya Tumanov said:
In our tests SQL CE 2.0/CF V1 loads 1200 records of 33 fields of various
types into the DataSet in under 8 seconds on PXA255-400 MHz.



SQL Mobile/CF V2 does the same in 3.5 seconds. Also, new SqlCeResultSet
class allows you to bind SQL Mobile database directly to UI controls
without creating a copy of data in memory.
Click to expand...

???? I thought I could bind data already. I once heard that if my
dataset/structure/whatever implemented the iList interface it should be
bindable.

Our reason for creating an underlying object model is that we get a greater
control over how we represent what, and also being able to re-use the data
in different representations. It is also somewhat easier to tag along some
data that hasn't anything to do with the representation.

But thanks for your input.

/Keld Laursen
 
Peter!

Peter Foot said:
Are you accessing the fields by their index or by name? How many fields
does your table have in total? Pocket Access' performance really starts to
drop once you get to around 10000 rows, although this varies depending on
the types used and indexes applied. If you are pushing the limits of
Pocket Access then you'll find Sql CE faster (Pocket Access generally
beats Sql Ce performance for small databases but isn't suited to large
volumes of data).
Click to expand...

I just noticed that I forgot to give you some information in the last post.

The table we have doen timing tests on are built like this (omitting
fieldnames for simplicity):
int, int, double, string, string, int, string, int, int, int, int, string,
int, string.
I use one int as a filter (and has an index on that field).
I select all 14 fields (select * from...), but only uses around 10 fields.
The data set I am testing is used to populate a grid, and will always be
fairly small. Standard sizes will be between 20 and 50 records, and for
"large" databases it will be around 200 or so. So quite small datasets.
(I have other tables that will contain much more data than this).

/Keld Laursen
 
The new feature with Sql Mobile is the SqlCeResultSet - this is somewhat
similar to the Recordset object in ADO (or ADOCE) in that it provides direct
access to the database and exposes IList etc interfaces. Currently with
SqlCe you must use the SqlCeDataAdapter to fill a DataSet and then bind this
to a DataGrid, this is creating a copy of the data in memory, therefore the
SqlResultSet is a huge improvement both in performance and resource
consumption.

Peter

--
Peter Foot
Windows Embedded MVP
http://www.inthehand.com | http://www.peterfoot.net |
http://www.opennetcf.org
 
Peter Foot said:
The new feature with Sql Mobile is the SqlCeResultSet - this is somewhat
similar to the Recordset object in ADO (or ADOCE) in that it provides
direct access to the database and exposes IList etc interfaces. Currently
with SqlCe you must use the SqlCeDataAdapter to fill a DataSet and then
bind this to a DataGrid, this is creating a copy of the data in memory,
therefore the SqlResultSet is a huge improvement both in performance and
resource consumption.
Click to expand...

Thus shortcircuiting a large part of what we have to do today. I see!

Thanks for the info.

/Keld Laursen
 
Back
Top