DataReader and Master-Detail

[Christian Schmidt]

Hi all,
I need to work through a master and its details table. My first attempt
was to open a DataReader on the master and for each record get a new
DataReader on the detail, restricted to the foreign key. But this is
very slow. As the tables are very large I cannot use DataSet's
functionality.
So my idea was to use a stored procedure for the detail reader that
returns details in the same order as the master.
1) Can this be done with a stored procedure? How?
2) Is there a better way?

Thanks for any advice,

Christian

details.read();
while (master.Read()) {
int key = master.GetInt32(masterPrimaryKeyCol);
while (details.GetInt32(detailForeignKeyCol) == key) {
// process details
// ...
details.read();
} // while details
} // while master

 

[Marina Levit [MVP]]

What are you trying to actually accomplish?

If you have a lot of rows, then it will take time. This all also depends on
what sort of processing you are doing with the retrieved data.

You can sort the detail data using the same sort as the master day, so all
the details rows for master 1 will be first, followed by all detail records
for 2, and so on.

 

[William \(Bill\) Vaughn]

Rules for an efficient application:
1) Do not recreate (download) the entire database or entire tables to the
client.
2) See 1.

When working with relational databases we use the SQL engine to return
relational "products" from SELECT statements that use the JOIN operator to
build correlated sets of rows--some columns drawn from several related
tables.

While the ADO.NET DataSet/DataTable approach appears to mimic this, it only
works efficiently if you return a small subset of the rows to the DataTable.
Sure, you can use the DataReader but why? The DataAdapter Fill method can
return selected rows from several tables in a single operation if you aren't
accessing JET.
Trying to pull rows from a parent table and related rows from a child table
is precisely what SQL is designed to do. No, you don't have to do a JOIN,
you can simply write two SELECT statements: one that returns _selected_
parent rows and a second that returns children from that subset of parent
rows. Yes, this can all be encapsulated in a SP that returns multiple
resultsets. If you use the Fill method to execute it, ADO.NET will
automatically create two DataTable objects in the DataSet. You can add
relationships to those afterwards.

All of my books since the first Hitchhiker's Guide to the ADO.NET books
discuss this concept in some detail. So do many others. I suggest you do
some reading...

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Christian Schmidt]

Hi Marina,
thanks for your interest.

What are you trying to actually accomplish?

I need to do calculations (that cannot be expressed in SQL) on master
detail structures, e.g. given a set of shipments (~1E6) with containers
(~5E6), I need to determine which of these shipments fit on which
transporter.

If you have a lot of rows, then it will take time.

Running through the records with a DataReader takes acceptable time
(using SqlConnection to a SQL 2000 Server). What does not take
acceptable time is opening a DataReader on the details for each master
record. So that's why my idea is to run through the details in the same
order (regarding the foreign key) as the master's primary key occur.

You can sort the detail data using the same sort as the master day, so all
the details rows for master 1 will be first, followed by all detail records
for 2, and so on.

That's exactly what I want, but: The select-statement for the master has
already an order by, so I cannot simply sort the details by the foreign
key. Any ideas?

Cheers,
Christian

 

[Marina Levit [MVP]]

I guess it all depends on your query for the detail data. You must be
joining on the primary key of the master - so why not sort on that for both
result sets.

Opening a datareader isn't what isn't acceptable. It is the time to run the
query - that is what opening a datareader is. So if your query takes too
long, then perhaps you need to optimize it so it runs faster, or make sure
you have the right indexes on your tables, etc.

 

[Christian Schmidt]

Hi Marina,

I guess it all depends on your query for the detail data. You must be
joining on the primary key of the master - so why not sort on that for both
result sets.

Because I need to run through the master in a special order (shipments
ordered by address, ...)
But indeed for the details, I could "select details.* from details inner

Opening a datareader isn't what isn't acceptable. It is the time to run the
query - that is what opening a datareader is. So if your query takes too
long, then perhaps you need to optimize it so it runs faster, or make sure
you have the right indexes on your tables, etc.

That's what I guessed first, too, but I have a clustered index on the
foreign keys in the detail table.

Thanks for your help.

 

[Christian Schmidt]

Hi William,

Rules for an efficient application:
1) Do not recreate (download) the entire database or entire tables to the
client.
2) See 1.

I knew that someone would say this, but I dont't see an other option:
I need to find shipments (=master) consisting of containers (=details)
that fit on a transport. As I need to run through all shipments anyway,
don't see an other way than using a DataReader.

When working with relational databases we use the SQL engine to return
relational "products" from SELECT statements that use the JOIN operator to
build correlated sets of rows--some columns drawn from several related
tables.

I could join the master and details, but this is more data in this flat
table than in two hierarchical tables. The only thing is I need to get
the details in the right order. Marina gave me a good hint to solve the
problem, I think.

While the ADO.NET DataSet/DataTable approach appears to mimic this, it only
works efficiently if you return a small subset of the rows to the DataTable.
Sure, you can use the DataReader but why? The DataAdapter Fill method can
return selected rows from several tables in a single operation if you aren't
accessing JET.

So you mean I should use DataAdapter to select 1000 masters, and let it
automatically select the corresponding details. But finally, the
DataAdapter uses a DataReader with "Select * From details Where
details.fk in (...)" to get it's records, sorts them by the fk and
builds a hashtable to relate them to the master records, right?
Therefore I don't see an advantage for using a DataTable over direct
usage of DataReader - assuming the details come in the right order.

Thanks.

 

[William \(Bill\) Vaughn]

If the calculations are too hard for TSQL, write a CLR executable (in any
CLR language including VB.NET) to handle the computations and do them
in-situ on the server. Your performance should soar. This is what CLR
functions and sps are for.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Christian Schmidt]

Hi William,

If the calculations are too hard for TSQL, write a CLR executable (in any
CLR language including VB.NET) to handle the computations and do them
in-situ on the server. Your performance should soar. This is what CLR
functions and sps are for.

Can these CLR executables be uploaded during runtime? Do you have some
good weblinks on this stuff?
Will it work for the express/MSDE editions, too?

So for data-intensive calculations you would advise against multi-tier?

Thank you very much fo your patience

 

[William \(Bill\) Vaughn]

As I describe in Chapter 13 of my new book (due out in early November) CLR
executables become part of the database--like a TSQL stored procedure. They
are supported in any SQL Server 2005 SKU--including SQL Server Express
Edition.
I cannot endorse applications that use the client as a replacement for code
that should be executed on the server. If the only purpose of the
application is to perform bulk calculations, then that work needs to be done
on the data where it resides. It's far too expensive to transport the data
to the client, change it and put it back. It unnecessarily increases network
traffic for both trips, locks on the server, load on the client and client
dissatisfaction with your approach.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Christian Schmidt]

As I describe in Chapter 13 of my new book (due out in early November) CLR
executables become part of the database--like a TSQL stored procedure. They
are supported in any SQL Server 2005 SKU--including SQL Server Express
Edition.
Fine.

I cannot endorse applications that use the client as a replacement for code
that should be executed on the server. If the only purpose of the
application is to perform bulk calculations, then that work needs to be done
on the data where it resides. It's far too expensive to transport the data
to the client, change it and put it back. It unnecessarily increases network
traffic for both trips, locks on the server, load on the client and client
dissatisfaction with your approach.

I think for each application a tradeoff between network load and
database server load has to be made. In our case the calculations can
sometimes be simple assigments and sometimes include (mathematical)
optimization procedures. So I think, we'll install GBit LAN and keep the
load on the database server low.
I wonder if CLR executables can be executed on other databases like
Oracle, ..., too?

Cheers,
Christian

 

[William \(Bill\) Vaughn]

Each vendor has the opportunity to design and implement their own brand of
server-side executables. Given the flexibility of the .NET Framework, they
are certainly welcome to include this as one of their options, but I doubt
if any have done so.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________