dataset fill brings all records?

[Rick]

VS 2005 pro.

I have a strongly typed dataset which I generate using the designer. From
the xsd file I set the relations between the CONTACTS table to the ORDERS
table to the ORDERITEMS table. Then I put some DataGridViews on the form
and open.

In the Load event of the form the Fill methods are used in the three tables.
As I step through by line, I can see the CONTACTS (1500 rows) is pretty
quick and the ORDERS (55,000 rows) is slower but the ORDERITEMS (5 million +
rows) churns on and on until I run out of memory.

If I leave out the ORDERITEMS datagridview, the form opens and the
parent-child relations are good.

It seems like the Fill method is taking ALL the rows from the DB and only
displaying the subset I want based on CONTACT. I would have thought that it
would only "hit" the DB when it had a record in the CONTACTS table as a
parent.

Do I have to setup my relations with a parameter to not retreive all rows
from the DB, or is this what is happening?

Rick

 

[RobinS]

A Dataset is disconnected from the database. It loads all of the
records when you do the Fill. You should limit the records
retrieved in order to gain better performance.

Robin S.

 

[Cor Ligthert [MVP]]

Rick,

How did you configure the tableadapter (your select file)

Cor

 

[Rick]

The select is like this:

"Select <field list> from ORDERITEMS.

My incorrect assumption was that when I set the relationship between ORDERS
and ORDERITEMS that VS would take care of only bringing in the related rows
for ORDERITEMS. Not so, it bring them all in which in this case is 5MM+.

I am learning VS from a Delphi background where all data is connected, so
the above works with no problem.

In a disconnected data state I need to readjust how I do things.

Since the "norm" for retreiving rows from a database is to limit them to
only the ones you need, I assumed my setup above would do just that, however
I'm learning differently.

Robin S suggestion probably makes the most sense for me given the size of my
tables.

Rick