efficient way to retrieve master-details data

  • Thread starter Thread starter Trapulo
  • Start date Start date
T

Trapulo

I've an application that internally manages data as user-defined business
objects (classes). This objects are saved and readed to/from a sql server
db. My DAL layer executes SPs using datareaders, and creates objects that
the application uses from result sets.

My problem is about the best way to retrieve data in master-details
scenarios.
Eg:
public class Object1
public p1 as int16
public d1 as detailsCollection
public d2 as detailsCollection
end class

DetailsCollection is a class that inherits collectionbase and that contains
a set of other objects.

On my db this is mapped as
table object1 (ID, p1)
table d1 (ID, object1_ID, other data)
table d2 (ID, object1_ID, other data)

I need an efficient way to read this data. I cannot read a set of objects
from table "object1" and then, for each row, read corresponding data from
tables "d1" and "d2", because this will result in a lot of different select
commands to sql server, and very bad performance.
I've tried to use a single query with a join, and then "split" result to
different objects. This works (anyway doesn't result in a very beautiful
code) and it is a lot faster, but I've problems when my object has a lot of
"details" (details from different tables).

What is an efficient solution I can use for this scenario?

thanks
 
hi

if you are using DataTables then you can link the details datatables with master datatable using DataRelation object

and use the foreach () and fetch the getChildRows() from the master. and assign this to another DataRow and display this

this is a scenario where based on the ListBox1 selection Listbox2 and Listbox3 will populated

or

if you want just pull the combined records then use (join the tables using ID reference
alliraja

----- Trapulo wrote: ----

I've an application that internally manages data as user-defined busines
objects (classes). This objects are saved and readed to/from a sql serve
db. My DAL layer executes SPs using datareaders, and creates objects tha
the application uses from result sets

My problem is about the best way to retrieve data in master-detail
scenarios
Eg
public class Object
public p1 as int1
public d1 as detailsCollectio
public d2 as detailsCollectio
end clas

DetailsCollection is a class that inherits collectionbase and that contain
a set of other objects

On my db this is mapped a
table object1 (ID, p1
table d1 (ID, object1_ID, other data
table d2 (ID, object1_ID, other data

I need an efficient way to read this data. I cannot read a set of object
from table "object1" and then, for each row, read corresponding data fro
tables "d1" and "d2", because this will result in a lot of different selec
commands to sql server, and very bad performance
I've tried to use a single query with a join, and then "split" result t
different objects. This works (anyway doesn't result in a very beautifu
code) and it is a lot faster, but I've problems when my object has a lot o
"details" (details from different tables)

What is an efficient solution I can use for this scenario

thank
 
allirajan said:
hi,

if you are using DataTables then you can link the details datatables with
Click to expand...
master datatable using DataRelation object.
and use the foreach () and fetch the getChildRows() from the master. and
Click to expand...
assign this to another DataRow and display this.
this is a scenario where based on the ListBox1 selection Listbox2 and
Click to expand...
Listbox3 will populated.

Thanks, but I'm using user-defined objects to manage data, and so I use
datareader (a lot faster) to read this data from db and create objects with
returned data.
I think I can read all in dataset and then copy data to object, so I can use
datarelation, but this may be very resource intensive..
or

if you want just pull the combined records then use (join the tables using ID reference)
allirajan
Click to expand...

No, I need to have an object hierarchy, not "flatten data".

Thanks
 
Do you always need the master and detail data loaded? if not, you could load
the detail on demand.
 
Thanks for allirajan's response.

Hi Trapulo,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get multiple result sets
from a database and put the values into user-defined objects. If there is
any misunderstanding, please feel free to let me know.

I agree with allirajan's advice that you have to fill data for p1, d1, d2
in to a single DataSet and create DataRelations for p1 and d1, p1 and d2.
For each p1 in the parent table, we can use GetChildRows method on each
DataRelation to get detailed information for particular p1 values and
assign the values to the detailCollection in the object.

Yes, it consumes more resources to hold all the values for the objects in a
DataSet. However, it's faster than a DataReader to read data from the
server one by one. Sometimes, we have to make our choice between space and
speed.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Yes, most time I load only master data or only details data.
But this time I need all data
 
Kevin Yu said:
Thanks for allirajan's response.

Hi Trapulo,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to get multiple result sets
from a database and put the values into user-defined objects. If there is
any misunderstanding, please feel free to let me know.
Click to expand...

That's right
I agree with allirajan's advice that you have to fill data for p1, d1, d2
in to a single DataSet and create DataRelations for p1 and d1, p1 and d2.
For each p1 in the parent table, we can use GetChildRows method on each
DataRelation to get detailed information for particular p1 values and
assign the values to the detailCollection in the object.
Click to expand...

Ok. I made a quick test and in fact I think this is an easy and almost fast
solution.
Yes, it consumes more resources to hold all the values for the objects in a
DataSet. However, it's faster than a DataReader to read data from the
server one by one. Sometimes, we have to make our choice between space and
speed.
Click to expand...

This sounds strange. All documentation says that datareader is the faster
data access object, and, in fact, I think that a dataadapter use internally
a datareader to fill datatable with data. With this schema I've a
dataadapter that retrieves data and copys them into the datatable, and then
a code (my code) that reads datatable and copys data to user-defined objects
collection. So it's strange that it's faster than a single datareader. Am I
wrong?

Anyway, I've a new question about this.
If I read all data from the tables, this is ok. But if I made a filter (a
complex filter) to select only a subset of data from the master table, what
is the best (perfomance and application's maangement) way to select
corresponding data from details table? I cannot read all details but I need
to retrieve only the right data.

Thanks
 
That's can help, if I use a dataview's based data retrieval management.
However, as I said to Kevin, how can I make to retrieve only useful details
data if I filter master table?

I think to something as:
select * from vw_master where (A LOT OF WHERES);
select * from vw_Details where masterID in (select id from vw_master where
THE SAME WHERES OF FIRST QUERY);

But in this way I think the processor will executes two time the first
select. Is this right? Can I have a way to optimize this?

thanks
 
You could keep the primary keys of the master table in a temp table or a
table variable. You can then use this list in the following requests...

Patrice
 
Hi Trapulo,

What you assumed is correct. If we use DataReader to retrieve data, we have
to go back to server each time we need to assign value an object. However,
if we use a DataSet to dump data, it will be faster at later times but
slower at first.

If you select data from SQL Server with a stored procedure like the
following, SQL server will put the result cursor in cache, and the second
query can take usage of the result in cache. So the first query will only
be called once.

select * from vw_master where (A LOT OF WHERES);
select * from vw_Details where masterID in (select id from vw_master where
THE SAME WHERES OF FIRST QUERY);

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Ok, I made some test and, in fact, performance are not degraded with this
"double select" in the SP.
On DAL I've some more work to do, but it seems to be non so bad.

I'll use this schema, that also mantains application code quite clear.

Thank for your support.
 
Hi Trapulo,

It was nice to hear that you have had the problem resolved. Thanks for
sharing your experience with all the people here. If you have any
questions, please feel free to post them in the community.

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