Binding sources question in Vs2005

[Bob]

I have noticed that as one adds related tables to a bindingsource and
dataset(vs2005 Vb.Net Sql Server 2005), the TODO and following code that
fills the datasets on form load are written to the formload event in the
order in which you drag the tables to the form to make a gridview, the last
table you drag is written to the top. Since you normallly start by dragging
the parent table and then the child tables and grandchild tables, what
happens is that the fill statements for the child tables get executed first
by default. The implication of this seems to be that the total contents of
the child tables get taken from the server (sql 2005 in my case) and that as
the parent record is changed by the user as he runs the form, a filter is
applied dynamically when you change the row to show only those records in
the child grids that are then pertinent, but the other unneeded records are
there taking up ressources in the background and going accross the network.

In systems with few records and on a fast LAN this is no problem. I'm
concerned however when the number of records goes up into the many
thousands, even millions. I had tested something with over 50000 patients or
customers in Vs2003 and found in VS 2003 at least this was the behaviour. It
was simple to test, you just executed a count on the table in the dataset
after it was filled and you saw that the result corresponded to the number
of records in the sql table. I found that forms designed with this behaviour
could take up to twenty seconds to load 50000 customer records and their
related info. The typical answer in this situation when I brought this up
was, thats the way ado works - use virtual mode datasets, but that in my
humble opinion is a poor substitute for really minimizing the number of
records to the ones you really need at a particular time and so minimizing
the transfer of info over the network and minimizing ressource useage on the
local machine. In 2003 we developped a dll that does this filtering and we
brought the load time down from 20 seconds to less than a second. We were
Using C1 data controls at the time but in Vs2005 we're not planning to use
those.

What I think is needed is the filling of the child datasets with filtered
neede info for that parent only downloaded from he server each time change
the selected parent is changed. In my case this would work since the
interface allows single row selection of the parent(s) only.

I would appreciate your comments on this situation and any suggestions on
how best to proceed and what pitfalls to look for when implementing this
type of approach, also of course if you think I'm totally off base, please
let me know. I may be trying to reinvent the wheel and if so I'd appreciate
knowing.

Thanks for your time.

Bob

 

[Bart Mermuys]

Hi,

I have noticed that as one adds related tables to a bindingsource and
dataset(vs2005 Vb.Net Sql Server 2005), the TODO and following code that
fills the datasets on form load are written to the formload event in the
order in which you drag the tables to the form to make a gridview, the last
table you drag is written to the top. Since you normallly start by dragging
the parent table and then the child tables and grandchild tables, what
happens is that the fill statements for the child tables get executed first
by default.

Even if the child table is loaded after the parent it will still load all
child rows and the child table should be loaded after the parent otherwise
you may get constraint errors saying that the parent row doesn't exist
(yet).

The implication of this seems to be that the total contents of the child
tables get taken from the server (sql 2005 in my case) and that as the
parent record is changed by the user as he runs the form, a filter is
applied dynamically when you change the row to show only those records in
the child grids that are then pertinent, but the other unneeded records are
there taking up ressources in the background

Yes, child rows that are potentially never seen are taking up memory.

and going accross the network.

Yes, but only once at form_load all rows would be loaded (parent & child).

In systems with few records and on a fast LAN this is no problem. I'm
concerned however when the number of records goes up into the many
thousands, even millions. I had tested something with over 50000 patients
or customers in Vs2003 and found in VS 2003 at least this was the
behaviour. It was simple to test, you just executed a count on the table
in the dataset after it was filled and you saw that the result
corresponded to the number of records in the sql table. I found that forms
designed with this behaviour could take up to twenty seconds to load 50000
customer records and their related info. The typical answer in this
situation when I brought this up was, thats the way ado works -

ADO.NET, true.

use virtual mode datasets, but that in my humble opinion is a poor
substitute for really minimizing the number of records to the ones you
really need at a particular time and so minimizing the transfer of info
over the network and minimizing ressource useage on the local machine. In
2003 we developped a dll that does this filtering and we brought the load
time down from 20 seconds to less than a second. We were Using C1 data
controls at the time but in Vs2005 we're not planning to use those.

Not sure what you mean with virtual mode dataset, virtual mode controls i
know, but afaik a bound DataGridView behaves pretty much as a virtual mode
control but eg. a ComboBox not.

What I think is needed is the filling of the child datasets with filtered
neede info for that parent only downloaded from he server each time change
the selected parent is changed. In my case this would work since the
interface allows single row selection of the parent(s) only.

You could implement this yourself, by adding a parameterized query to the
existing child TableAdapter with a fk parameter:

- Inside DataSet schema designer, right-click on the child TableAdapter and
choose "Add query", then make a query that returns the rows only for a
certain fk, eg.
SELECT * FROM child WHERE fk = @fk

The last step of the wizards will ask you for a Fill name, choose FillByFk
and finish wizard.

- Then attach an event to ParentBindingSource.CurrentChanged and make it get
the child rows:
Private Sub ParentBindingSource_CurrentChanged(...) Handles ....
Dim currentDRV As DataRowView = DirectCast( _
ParentBindingSource.Current, DataRowView)

ChildTableAdapter.FillByFk(SomeDataSet.ChildTable, CInt(
curentDRV["pk"] ) )

End Sub

Note; in the above text and code you should replace pk & fk with the actual
pk & fk column names you have.

I would appreciate your comments on this situation and any suggestions on
how best to proceed and what pitfalls to look for when implementing this

In the above code above it looks like the child DataTable isn't cleared
before filling again, but be aware of the child TableAdapter on the Form, it
has a property "ClearBeforeFill" which is true by default, but can be turned
off.

If you clear the child DataTable before filling it (again) then you must
first save the (previous) child rows otherwise you may loose changes. You
still have a choice whether you want to clear it or not (existing child rows
will be overwritten, at least if they also have a pk). Then there is
another option only load the child rows for a given parent once, but then
you'll need something to store 'for which parent rows the child rows are
already loaded', maybe an extra (dummy) boolean column in the parent
DataTable.

But because of the "lazy-loading", the UI may now freeze a little when you
browse parent rows when there are a lot of child rows for each parent. That
brings us to "asynchronous" loading of both parent and child rows and in
case of 'loading child rows' abort when user moves to another parent,
unfortunetally there isn't much support for this.

HTH,
Greetings

 

[Bob]

Bart Thanks an awfull lot, your code snippets and how tp implement them will
go in my permanent reference library.
I really appreciate your insight,

Bob

Hi,

I have noticed that as one adds related tables to a bindingsource and
dataset(vs2005 Vb.Net Sql Server 2005), the TODO and following code that
fills the datasets on form load are written to the formload event in the
order in which you drag the tables to the form to make a gridview, the
last table you drag is written to the top. Since you normallly start by
dragging the parent table and then the child tables and grandchild tables,
what happens is that the fill statements for the child tables get executed
first by default.

Even if the child table is loaded after the parent it will still load all
child rows and the child table should be loaded after the parent otherwise
you may get constraint errors saying that the parent row doesn't exist
(yet).

The implication of this seems to be that the total contents of the child
tables get taken from the server (sql 2005 in my case) and that as the
parent record is changed by the user as he runs the form, a filter is
applied dynamically when you change the row to show only those records in
the child grids that are then pertinent, but the other unneeded records
are there taking up ressources in the background

Yes, child rows that are potentially never seen are taking up memory.

and going accross the network.

Yes, but only once at form_load all rows would be loaded (parent & child).

In systems with few records and on a fast LAN this is no problem. I'm
concerned however when the number of records goes up into the many
thousands, even millions. I had tested something with over 50000 patients
or customers in Vs2003 and found in VS 2003 at least this was the
behaviour. It was simple to test, you just executed a count on the table
in the dataset after it was filled and you saw that the result
corresponded to the number of records in the sql table. I found that
forms designed with this behaviour could take up to twenty seconds to
load 50000 customer records and their related info. The typical answer in
this situation when I brought this up was, thats the way ado works -

ADO.NET, true.

use virtual mode datasets, but that in my humble opinion is a poor
substitute for really minimizing the number of records to the ones you
really need at a particular time and so minimizing the transfer of info
over the network and minimizing ressource useage on the local machine. In
2003 we developped a dll that does this filtering and we brought the load
time down from 20 seconds to less than a second. We were Using C1 data
controls at the time but in Vs2005 we're not planning to use those.

Not sure what you mean with virtual mode dataset, virtual mode controls i
know, but afaik a bound DataGridView behaves pretty much as a virtual mode
control but eg. a ComboBox not.

What I think is needed is the filling of the child datasets with filtered
neede info for that parent only downloaded from he server each time
change the selected parent is changed. In my case this would work since
the interface allows single row selection of the parent(s) only.

You could implement this yourself, by adding a parameterized query to the
existing child TableAdapter with a fk parameter:

- Inside DataSet schema designer, right-click on the child TableAdapter
and choose "Add query", then make a query that returns the rows only for a
certain fk, eg.
SELECT * FROM child WHERE fk = @fk

The last step of the wizards will ask you for a Fill name, choose FillByFk
and finish wizard.

- Then attach an event to ParentBindingSource.CurrentChanged and make it
get the child rows:
Private Sub ParentBindingSource_CurrentChanged(...) Handles ....
Dim currentDRV As DataRowView = DirectCast( _
ParentBindingSource.Current, DataRowView)

ChildTableAdapter.FillByFk(SomeDataSet.ChildTable, CInt(
curentDRV["pk"] ) )

End Sub

Note; in the above text and code you should replace pk & fk with the
actual pk & fk column names you have.

I would appreciate your comments on this situation and any suggestions on
how best to proceed and what pitfalls to look for when implementing this

In the above code above it looks like the child DataTable isn't cleared
before filling again, but be aware of the child TableAdapter on the Form,
it has a property "ClearBeforeFill" which is true by default, but can be
turned off.

If you clear the child DataTable before filling it (again) then you must
first save the (previous) child rows otherwise you may loose changes. You
still have a choice whether you want to clear it or not (existing child
rows will be overwritten, at least if they also have a pk). Then there is
another option only load the child rows for a given parent once, but then
you'll need something to store 'for which parent rows the child rows are
already loaded', maybe an extra (dummy) boolean column in the parent
DataTable.

But because of the "lazy-loading", the UI may now freeze a little when
you browse parent rows when there are a lot of child rows for each parent.
That brings us to "asynchronous" loading of both parent and child rows and
in case of 'loading child rows' abort when user moves to another parent,
unfortunetally there isn't much support for this.

HTH,
Greetings

type of approach, also of course if you think I'm totally off base,
please let me know. I may be trying to reinvent the wheel and if so I'd
appreciate knowing.

Thanks for your time.

Bob