Dis-/Advantages with Datasets on SQLite

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Hi together,

we are planning to use DataSets on our SQLite Database on Compact
Framework 2.0. I am quite new to ADO.NET and also the Wrapper from R.
Simpson for SQLite.

Can anyone tell me about the advantages and disadvantages of DataSets.
Some simple Code-Snippet ( in use of the wrapper) would I appreciate.

Thanks a lot
Daniel
 
Daniel - I'll gladly help but not sure i follow you on what you're looking
for. When you say the advantages/disadvantages, what is the baseline you
want to compare against? As far a using DataSets go, the whole architecture
is such that it doesn't really know or care where the data came from, it
could come from a web service, filling it programatically, a SqlMobile
database, a Oracle database or anything else. So once it's populated,
there's no difference between a dataset filled against SqlLite vs Oracle vs
SqlMobile vs Comma Separated files etc.. If you can let me know a little
more aobut what you're looking for, hopefully I can give you a more precise
answer.

HTH,
Bill
 
There was a good article on this very subject a couple of weeks ago. Try
searching the newsgroup server for text : "portable databases".

Cheers
Simon.
 
Hi Bill,

thanks for your help. Ok, more precise, I did setup a DbDataReader on
SQLite. My first question is, what is the advantage of a DataSet
compared to a DataReader. My second is, if there is a tutorial
available using a DataSet (at best with SQLite Wrapper!).

Daniel
 
SQLite has no bearing on the differences. A DataSet is a DataSet and a
DataReader is a DataReader. A DataSet is scrollable and updatable, but it's
got a large memory footprint and is slow. A DataReader is small and fast,
but is not scrollable nor updatable.

--
Chris Tacke
Co-founder
OpenNETCF.org
Are you using the SDF? Let's do a case study.
Email us at d c s @ o p e n n e t c f . c o m
http://www.opennetcf.org/donate
 
SQLite, being an ADO.NET wrapper, behaves pretty much exactly the way every
other ADO.NET provider behaves. Therefore it would be more precise and
beneficial to explain ADO.NET behavior rather than SQLite behavior.

A DataReader is what ADO.NET uses to populate a DataSet/DataTable. When you
use a DataAdapter and call the Fill() method, the DataAdapter is actually
opening up a DataReader and loading all of the columns and data into the
destination DataSet/DataTable.

The major differences between a DataReader and a DataSet are:

The DataReader is a stream, whereby you read one row from the stream and
then move to the next row. There is no concept of moving backwards or
randomly from one row to another. As someone else stated in the forums, a
DataReader is a firehose.

A DataSet is an in-memory grid-like structure that holds all the rows and
all the columns from a DataReader. Once the DataSet is filled up you can
dispose of the DataReader entirely and work with the contents of the DataSet
in an offline fashion.

Think of a DataSet as a bucket. You aim the firehose at the bucket and the
bucket collects all the water. You can then stick your cup in the bucket
and grab whatever amounts of water you want from anywhere in the bucket --
but the firehose has been turned off and put away and all you're left with
is the isolated bucket.

Any changes made to data in a DataSet are tracked. It becomes possible then
to use a DataAdapter to actually trigger UPDATE/INSERT/DELETE statements
that can synchronize the changes you've made back to the underlying
database.

The DataReader has no such capacity. It is read-only. If you want to
change data you have to generate your own UPDATE/INSERT/DELETE commands and
execute them.

As for performance ... all the convenient DataSet features that track
changes, raise events and store all the data come at a cost. DataSets are
memory hogs and are much slower than handmade code that iterates and updates
from a DataReader.

Robert
 
DataSet's do use noticeable amount of memory to store user's data.

As to "much slower than handmade code that iterates and updates from a
DataReader", it's not actually that much.

Our performance tests for SQL Mobile indicate what looping through records
in DataReader, accessing all fields and discarding of data takes about
75-80% of DataAdaper.Fill() time.


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 Tumanov said:
DataSet's do use noticeable amount of memory to store user's data.

As to "much slower than handmade code that iterates and updates from a
DataReader", it's not actually that much.

Our performance tests for SQL Mobile indicate what looping through records
in DataReader, accessing all fields and discarding of data takes about
75-80% of DataAdaper.Fill() time.
Click to expand...

For reading, it's not that bad. It's when you start doing
updates/inserts/deletes in bulk using a Dataset that things start to go
downhill -- usually due to all the events being raised.

Robert
 
I don't have performance data for DataAdapter.Update() vs. executing update
commands in a loop. However, I would have to disagree with you.

DataSet's overhead for reading data from it is lower than for inserting - no
constraint checks and no storage allocation.

At the same time inserts are usually slower that selects for the same amount
of data in DB server. That would lead to lower DataSet overhead, not higher.

As to events, it's the factor in loading data as well.


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).
 
thanks for this very pictured explanation of datasets and datareaders,
it helps me understand much more! I will try it out!

Daniel
 
Back
Top