Building a windows explorer like application...

[UJ]

I have an app that I've already written that works just great. It's a
window's explorer like app for our data. Problem is, to build the treeview
takes too long (30 secs and upward for less than 1000 records). And the
database is only going to be getting bigger.

Here's essentially what I do:

Get three tables that are related (I don't make the relationship locally)
Start stepping through table 1.
Find all of the records from table 2 that have a value from the table 1
record.
Start stepping through those records.
Find all of the records from table 3 that have the value from the
record in table 2.

Now the way I pare down the records is with filters. I assume that's what's
taking so long because the records don't have any indexes.

How can I speed this up? Would setting up a relationship between the tables
speed it up? Is there a way to create an index on the records so they will
take much less time to find the records they need?

TIA - Jeff.

[rhaazy]

Don't populate the treeview with all the data at once, only populate it
as the user expands each node.

UJ wrote:

I have an app that I've already written that works just great. It's a
window's explorer like app for our data. Problem is, to build the treeview
takes too long (30 secs and upward for less than 1000 records). And the
database is only going to be getting bigger.

Here's essentially what I do:

Get three tables that are related (I don't make the relationship locally)
Start stepping through table 1.
Find all of the records from table 2 that have a value from the table 1
record.
Start stepping through those records.
Find all of the records from table 3 that have the value from the
record in table 2.

Now the way I pare down the records is with filters. I assume that's what's
taking so long because the records don't have any indexes.

How can I speed this up? Would setting up a relationship between the tables
speed it up? Is there a way to create an index on the records so they will
take much less time to find the records they need?

TIA - Jeff.

[Grant Frisken]

UJ wrote:

>
How can I speed this up? Would setting up a relationship between the tables
speed it up? Is there a way to create an index on the records so they will
take much less time to find the records they need?

There are probably two main factors that will affect performance.

The first factor is the time taken to load the dataset. Because
datasets use a disconnected in-memory model you have to load your
complete set of data into memory before using the dataset. For large
databases this can take considerable time as well as costing you are
large amount of memory.

The second factor is the time take to create the visual representation
of your data by creating tree nodes. Using the standard treeview
control you can improve this somewhat by creating dummy child nodes for
each node at a given level and then when the node is expanded you
retrieve the actual children for the node. This doesn't help you if
you have a large number of children at any given level. For instance
if you have potentially a million customers as your base level nodes
then you'd still be stuck with creating a million tree nodes which
would be very time consuming and memory expensive. It also means that
you will get the disappearing expansion indicator phenonoma - ie if a
node doesn't have children you still see an expansion indicator which
then disappears when you try to expand it.

I'd suggest doing some basic profiling to find out what the major issue
is for your application. Infralution has some products which you might
want to look at which address both these issues.

Virtual Data Objects allows you to create connected recordsets (just
like in old ADO) and bind .NET controls to them. Connected recordsets
only load data into memory as it is requested by the application - this
means your application data is loaded almost instantly regardless of
the database size and has a minimal memory footprint.

Virtual Tree is a data driven tree/listview .NET custom control that
creates the visual representation (rows or nodes) on demand. This
means it only creates row (or node) objects for those data items that
are currently displayed. So even when displaying a million base level
items VirtualTree only needs to create row objects for the actual
visible rows (say 30). This means that it is near instantaneous.
You can also avoid the disappearing expansion indicator issue. Virtual
Tree can be bound to standard .NET datasets or you can use it in
conjunction with Virtual Data Objects to get the maximum in
performance.

You can get more information and download evaluation versions from:
www.infralution.com/virtualtree.html
www.infralution.com/virtualdata.html

Regards
Grant Frisken
Infralution