Pre-filtering datasets

[Sam]

I am having a very difficult time with the fill statement for datasets. The
fill will always obtain all records. I have a master-detail structure in my
forms and it's ridiculous to see that loading all the records in the detail
table occurs. The filter property only filters after I have obtained all
the data when the form loads. I have done the following:

private void Customer_Load(object sender, EventArgs e)
{
//Select statments in the table adapters contain where clause of '1=0' so I
load zero records initially for the datasets
this.customerTableAdapter.Fill(this.DataSet1.customer);
this.orderTableAdapter.Fill(this.DataSet1.order);
this.orderdetailTableAdapter.Fill(this.DataSet1.orderdetail);

this.employeeTableAdapter.Fill(this.DataSet1.employee);
getCustomer("33d489a7-b1b9-487d-a5ce-0072471482ba");

}
private void getCustomer(string customer_customer_id)
{
this.customerTableAdapter._adapter.SelectCommand.CommandText = "select *
from customer where customer_customer_id = '"+ customer_customer_id +"'";
this.customerTableAdapter.Fill(this.DataSet1.customer);
getOrders(customer_customer_id);
}
private void getOrders(string order_customer_id)
{
this.orderTableAdapter._adapter.SelectCommand.CommandText = "select * from
[order] where order_customer_id = '" + order_customer_id + "'";
this.orderTableAdapter.Fill(this.DataSet1.order);
}

The initial fills above are actually based on a dummy query that I use with
a where clause that is '1=0'. I use the dummy query to load 0 records
initally. Then I proceed to fill the dataset with only one record at a
time. The above code also forces me to make the _adapter public in the
designer.cs class which is also very annoying. Is there any way to get the
master-detail relationship to actually obtain only related records for
performance reasons? Basically if I have a 1 million record customer table
and a 1 million record order table how do I build an efficient model to get
only the records I need on demand? I have tried the .Filter() command and it
does not pre filter from the datasource but rather on the results.

 

[Cor Ligthert [MVP]]

Sam.

Is it needed that you work completely disconnected. Otherwise I would in
your case file a dataset for every customer using the Where clause in the
Select.

The advantage is not only speed. Avoiding concurrency problems is a much
important deal in this, however.

I hope this helps,

Cor

 

[W.G. Ryan - MVP]

Sam:

nothing under the sun is going to make 1million record tables respond
quickly in ADO.NET, there's too much overhead. If you're doing this client
server, then I'd seriously consider paging and/or just taking what I needed
when the customer asked for it. If it's a mobile/laptop scenario where you
need everything cached, then I'd look to Sql Mobile/ Sql Server CE to help
here, it's going to be a lot faster.

If you're filtering using a rowfilter though, you may want to use
GetChildrows instead. Each change of the rowfilter causes a new view to be
created and this is very wasteful , particularly when you have such large
data sets. GetChildrows is faster in terms of smaller footprint and
efficiency.

I am having a very difficult time with the fill statement for datasets.
The fill will always obtain all records. I have a master-detail structure
in my forms and it's ridiculous to see that loading all the records in the
detail table occurs. The filter property only filters after I have
obtained all the data when the form loads. I have done the following:

private void Customer_Load(object sender, EventArgs e)
{
//Select statments in the table adapters contain where clause of '1=0' so
I load zero records initially for the datasets
this.customerTableAdapter.Fill(this.DataSet1.customer);
this.orderTableAdapter.Fill(this.DataSet1.order);
this.orderdetailTableAdapter.Fill(this.DataSet1.orderdetail);

this.employeeTableAdapter.Fill(this.DataSet1.employee);
getCustomer("33d489a7-b1b9-487d-a5ce-0072471482ba");

}
private void getCustomer(string customer_customer_id)
{
this.customerTableAdapter._adapter.SelectCommand.CommandText = "select *
from customer where customer_customer_id = '"+ customer_customer_id +"'";
this.customerTableAdapter.Fill(this.DataSet1.customer);
getOrders(customer_customer_id);
}
private void getOrders(string order_customer_id)
{
this.orderTableAdapter._adapter.SelectCommand.CommandText = "select * from
[order] where order_customer_id = '" + order_customer_id + "'";
this.orderTableAdapter.Fill(this.DataSet1.order);
}

The initial fills above are actually based on a dummy query that I use
with a where clause that is '1=0'. I use the dummy query to load 0 records
initally. Then I proceed to fill the dataset with only one record at a
time. The above code also forces me to make the _adapter public in the
designer.cs class which is also very annoying. Is there any way to get
the master-detail relationship to actually obtain only related records for
performance reasons? Basically if I have a 1 million record customer
table and a 1 million record order table how do I build an efficient model
to get only the records I need on demand? I have tried the .Filter()
command and it does not pre filter from the datasource but rather on the
results.