Improving Performance

[Guest]

I have a table called tblOrg in a SQL Server 2000 database.

It has PK = Org_ID and a field called ParentOrg_ID so an Organization can
have 0, 1 or many children but only one parent.

I have a recursive function that builds a tree view of this and populates a
tree control with Org_ID as the key and OrgName as the Text. Everything
works great and performance is great as long as the data base and the
application are on the same machine. But performance is unacceptable on a
remote machine connected via a LAN.

This is to be expected because the application is using sqlcommands to build
the tree in the recursive function. Thus, the same sqlcommand is executed
once for each organization.

Obviously to get the most update version of the data you have build it from
the database. But what other ways are there, that once built, one can reuse
it rather that rebuild the tree?

For example, the user selects OrgType from a dropdown (College, High School,
Middle School) which populates the tree control with organization of that
type, he then can select the Organization and populate the form with the
details of the selected organization.

When changing the OrgType, the control is completely rebuilt via the
recurisive function which goes to the database. Instead, it would be nice to
persist the previously clicked OrgTypes in memory so that the tree control is
populated from memory if that OrgType is reselected.

Since this tree control is used in other places in the application, it might
be better to populate a dataset in memory so that all projects within the
application can reference and populate the tree control that way or, better
yet, all instances of the application can use it. For example, a user A
opens his application which causes the Tree to be built. But user B opens
his application but the application has access to the already populated tree
because of User A’s work (This organization structure does not change often).

How can I do this? Any VB or C# code examples would be nice?

WR

 

[grant]

If the total amount of data in the tblOrg is not too large (ie < 500
rows) then the simplest solution would be load the entire table into a
DataSet upfront. The thing that is probably killing your performance
on the LAN is all the network round trips you are doing as you populate
your tree. Loading the DataSet upfront reduces this to a single
(although somewhat larger) network request. Once the data is loaded
into the DataSet you can query the DataSet to build the tree.

If you are dealing with larger DataSets then sending the whole table
over the wire will become prohibitively expensive (in terms of
performance). At the very least you would then need to consider
populating the tree control "on demand". To do this you add a dummy
node to each node which may have children and handle the NodeExpand
event to dynamically remove the dummy node and add the real child
nodes. This will mean the SQL queries (and network roundtrips) are
only done when you really need them (not all upfront). This still
won't help you if a node of the tree can have a large number of
children - as you will have to load all the children of a node when the
user expands it. If the node has 1000 children this may still be
pretty slow.

If you are willing to consider a commercial solution then our
VirtualTree control can help solve these issues. You can download a
fully functional trial version at:

http://www.infralution.com/products.html
Regards
Grant Frisken
Infralution

 

[Guest]

I actually took the approach of loading the data into a dataset and selecting
off the dataset. This works great.

Additionally, since this tree is used in more than one form, I made it in
its own form that can float around or be minimized so that it is only built
once per application session. Plus it would take up a lot of room on each
form otherwise.

Finally, I would be glad to look at your product. For my larger customers,
this may be needed.

wr