Performance better with DataSet or DataReader or ...???

Hi,
I have a business-entity-classes which are constructed of few primitive 
types and one DataSet type. For Example:

Public class MyClass{
private int id;
private string name;
private DataSet childrenCollection;



In my data access layer I want to create an instance based on this class and 
fill it with data from the DB (MSDE). I have two options in mind and I want 
to know which would be the best in term of performance. In both options I 
using a single stored procedure with two select statements one for the 
single row (primitive types) and one for the children collection. For example:

SELECT ID, Name from T_Parents WHERE ID= id;
SELECT ID, Name from T_Children WHERE ParentID = id;

 The options are:

1. Fill all the data into a DataSet. As there are two select statements two 
tables will be created in the DataSet I would be able to copy the values from 
the first table to my class primitive and the other table to my class 
DataSet. 

2. Use a DataReader and, by looping over the second ResultsSet, build a 
DataTable row after row. So instead of using the DataSet.Fill method Il be 
anuallycreating the rows and inserting the values based on the results 
coming from the DataReader. 
 
I don think it matters, but I using the data access application bloc v2.

If there is another (better) option I haven thought about Il be happy to 
know. Otherwise I would like to know which one should perform better.

Thank you,
Gwenda  

Gwenda:
You haven't really been specific enough for anyone to provide a specific 
answer.  In this case, knowing more about your schema could easily impact 
the answer.  Knowing more about your retrieval code would also be 
beneficial.

Is the DataSet you've named childrenCollection the same as the DataSet that 
you describe populating in the paragraphs after the SQL Statements?  If so, 
since you're already storing the data about the parent in the other member 
variables, would it not make more sense to store just the DataTable 
containing the children?

Are id and name the only columns you're retrieving?

Assuming that "T_" means "table" (correct me if I'm wrong), do T_Parents and 
T_Children store the same data just partitioned for creating a relationship? 
If not, does T_Children have data that could/should be normalized in order 
to make the data the same?

The reason I ask these questions is because there may be a way to retrieve a 
single DataTable and DataView and do away with the overhead associated with 
the DataSet.  If you still need to create both tables though, the 
performance difference between DataAdapter.Fill and iterating through a 
DataReader to build a DataTable will be negligable and you'll also have more 
code to maintain.  Keep in mind that if your data structure ever changes, 
you'll need to update the code for building your table in addition to any 
other required changes...

I'm sorry I couldn't be more help at this time but if you answer the above 
questions and include your schema (at least as much as relates to this 
question), I should be able to provide more assistance.

Gwenda,

When I understand your question well, than is for me the answer simple,

When I can use the dataadapter to build a dataset, I do it.

I hope this helps you to find your decissions.

Cor

"gwenda" < XXXX@XXXXX.COM >

Hi Gwenda,

First of all, I would like to confirm my understanding of your issue. From 
your description, I understand that you need to know the best way to fill 
the childrenCollection DataSet. If there is any misunderstanding, please 
feel free to let me know.

In my opinion, I will try to fill the two tables to a single DataSet and 
create a DataRelation between two tables. We can use GetChildRows to get 
the children table rows according to the parent ID and put them to the 
objects. This will help to lower the workload of database server, since a 
DataReader will require an open connection to the database. If many clients 
will connection to the server simultaneously, this would be a good 
practice. It does all the object filling at client side.

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

Sorry for not getting this back yesterday.  It sounds like you may still 
have an opportunity to rework your schema a bit but without the schema I 
can't really give you any guidance on that (and depending on your progress 
on the project it might not be an option).

I'd like to extend on what Kevin has said below.  If you populate a DataSet 
with the two result sets returned from your stored procedure you will 
definately reduce the load on your DB Server (but, since you're using MSDE, 
I doubt it would make much difference overall).  Also consider this though: 
It doesn't sound like you really need to manage the relationship with the 
DataSet because (from my understanding of you're previous posts), you're 
going to populate the private fields of your class with the parent's 
information, then reference the children through the dataset.

If this is the case, If you only have the one "Collection" you can just 
replace the DataSet field with a DataTable field and call the copy() method 
of the DataTable in your DataSet to copy the DataTable into your class.  You 
could then dispose of your data set and get rid of the overhead it causes. 
Furthermore, you stated that you were using the DataSet to "track what was 
changed in them by the user,"  The individual data tables are responsible 
for managing the changes, not the DataSet.

If you do, indeed have multiple "Collections" you should probably follow 
Kevin's advice and use the DataSet with DataRelations but rather than 
populate member fields in your class, just create properties that expose 
only what needs to be exposed.  For example:

public class MyClass
{
    private DataSet peopleInfo;

    public int ID
    {
        get { return (int)(peopleInfo.Tables["Parent"].Rows[0]["ID"]); }
    }

    public string Name
    {
        get { /* code to get the name here */ }
        set { /* code to set the name here */ }
    }

    public DataTable Children
    {
        get { return peopleInfo.Tables["Children"]; }
    }
}

I hope this helps

Hi Gwenda,

Actually, the DataAdapter internally uses DataReader to read data and fill 
the DataSet. However, if we fill a DataSet and copy data to your object, 
that will be slower than your code to put records directly to the DataSet 
in myClass. So I think your code is fine. Good luck!

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