Limiting record numbers retrieved

  • Thread starter Thread starter Bob Dufour
  • Start date Start date
B

Bob Dufour

Scenario:

50000 Customers in table Customers, Each customer has 5 Contact records In
related table Contacts, each contact has 5 Records In table ContactPhones
related to table Contacts. The total number of records in this scenario is
50000 + 250000 + 1250000 = 1550000 records.

We want to create a dataset with the objective of displaying 1 Customer
record, and ONLY it's related Contacts, and ONLY the related ContactPhones
of the currently selected Contact. That is a total of 11 records at any one
time. The interface consists of textboxes for the fields bound to Customers,
a Grid for the Contacts and a grid for the ContactPhones. When we change row
in the Contacts Grid we want to retrieve the 5 ContactPhone records for that
newly selected contact.

We created a strongly typed dataset in which we place the correct
relationships and tables but we realize that even though we filter the
Customer for a given customer, all of the contacts and all of the
contactphones get loaded in the dataset. This is ridiculously slow as it
well should be. How do you limit the loaded records to what I described
above, ie only those required for the current viewing and editing need? The
number of customers is expected to increase dramatically over what we are
currently testing and the default way is just plain unuseable.

Any help appreciated.

Bob
 
Bob,

Only fill each datatable with the records you need to display by using for
each of the dataadapters an SQL-statement with a WHERE-clause based on the
result of the previous selection.

Regards,
Jan
 
Hi Bob,

You will have to adjust dataadpeter.selectcommand.CommandText accordingly -
you might use WHERE sql clause to filter the records.
 
Back
Top