Multiple Joins in DataAdapters

[Fred Chateau]

I have a second question related to my previous post subject: Mapping Nested
XML to Relational Tables

The XML document which loads into the dataset must then be stored in
relational tables and recalled later. As mentioned in my previous posting,
the document is fairly complex and requires over 20 tables to store the
dataset record.

Is it necessary to create 20 DataAdapters to store and recall the document,
or can it be done with one DataAdapter containing multiple joins? Each XML
document represents one record in the dataset and records must be added,
recalled, and sometimes deleted from the database.

 

[Miha Markic]

Hi Fred,

I have a second question related to my previous post subject: Mapping Nested
XML to Relational Tables

The XML document which loads into the dataset must then be stored in
relational tables and recalled later. As mentioned in my previous posting,
the document is fairly complex and requires over 20 tables to store the
dataset record.

Is it necessary to create 20 DataAdapters to store and recall the document,
or can it be done with one DataAdapter containing multiple joins?

20 adapters.

 

[Kathleen Dollard]

Fred,

I think Miha is more aware of your problem, so I'd defer to his answer. But,
to clarify, you can use joins in retrieving data, and you can use one data
adpater that returns multiple data tables. When you are saving data back to
the store, you'll need a data adapter for each logical table. Occasionally,
with a stored procedure a logical table is several joined underlying tables,
and if the stored proc can sort it out, you'll only need one data adapter
for that logcial table.

Whether either of these solutions will make your life easier, I don't know.

 

[Fred Chateau]

...

I think Miha is more aware of your problem, so I'd defer to his answer.

But, to clarify, you can use joins in retrieving data, and you can use one
data adpater that returns multiple data tables. When you are saving data
back to the store, you'll need a data adapter for each logical table. . . .

Regarding performance, is there anything inherently wrong with using 20
DataAdapters? Should I be thinking in terms of finding a way to manually
execute insert, update, and delete commands?

 

[Miha Markic]

Hi Fred,

...

But, to clarify, you can use joins in retrieving data, and you can use one
data adpater that returns multiple data tables. When you are saving data
back to the store, you'll need a data adapter for each logical table. . . ..

Regarding performance, is there anything inherently wrong with using 20
DataAdapters? Should I be thinking in terms of finding a way to manually
execute insert, update, and delete commands?

Adapters execute whatever they are instructed to - either parameterised sql
commands or stored procedures.
The execute a command per row. The same resoult you would obtain by manually
invoking all those stataments.
The only drawback (if this is a drawback) is that they don't do batch
execution which would speed up a little the process.

 

[Kevin Yu [MSFT]]

Thanks for Miha and Kathleen's response.

Hi Fred,

Based on my understanding, you need to know whether to use 1 or 20
DataAdapters to update the database table from one DataSet.

If you have 20 table in the same DataSet, and the 20 tables are linked with
DataRelation objects, I think you have to use 20 DataAdapters to fill and
update the tables separately. If the 20 tables are joined to one or they
are filled by one DataAdapter, you can use only one DataAdapter to update
them. Please make sure that you have the right command text and parameter
setting.

Referring to the performance issue, I think it will be the same whether you
step through each row manually or let the DataAdapter decide how to update
it. Because the DataAdapter actually does the same.

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

 

[Kathleen Dollard]

Fred,

The only place it makes sense to use a single DataAdapter is in a Retrieve
operation and then it is likely to be significant only in some scenarios. In
some cases, such as a slow/long pipe, or a complex stored procedure it can
be significant. In the case of the retrieve operation, 20 data adapters
gives you 20 roundtrips, and the single DataAdapter gives you one roundtrip.
In the case of all other operations, the number of roundtrips is identical
wth either approach.