Huge table needs to be loaded

Question

I'm implementing a treeview control whose labels are grabbed from a very very large database table. This is going to be an embedded ActiveX control. I don't think it'd be very efficient to get the user to download all the data in the table the first time the control loads it will take too long. Is it a good idea to grab the data little by little? For example I have a path like this in my menu: Universe --> Galaxy --> Solar System --> Mercury Venus Earth Mars (Assume there are many galaxies and within each galaxy there are many many solar systems.) So let's say when the control first loads the top two levels are loaded -- that is universe and galaxies. When the user clicks on one of the galaxies the subsequent solar systems from that galaxy will be grabbed from the db table. Is this a good way to load the data? Or is there a better way? And would I store this info in a DataSet object? How do you append new info to the DataSet? I don't think I can overwrite any info because I need to keep the data from the upper layers. Would another option be to grab everything from the table once store it in a cache somewhere on the server and then distribute that to all users a little bit at a time? But the problem with this option is that I have no idea how to implement it. Thanks in advance.

spgilmore · Answer

I think the quote was misleading. By no means did I mean to suggest to finish the application and then find that it was done wrong then rewrite the thing the other way. Surely if you can see a problem coming it should be addressed. I only suggested to verify the cause of the problem before you write implement its solution. To do otherwise would be like fixing a flat tire before you remove the nails in your driveway. Obviously some problems just don't need to be proven. But the reason I brought it up here is because I once made this mistake in a very similar circumstance. I had to retrieve between 1MB and 10 MB of blobs from multiple rows in a database. First I was querying for a single row with a PK that the blobs were linked to. Then I queried the blobs that pointed to this PK. I revised my code to load the blobs one at a time. I was still looking up that PK every time. It took almost 8 minutes. The query for the PK was taking the majority of the time because there was no index on that field (and that db server version didn't permit enough space to put one there). I had only made the wait 100 times longer. I didn't properly identify the cause because I didn't prove the problem. I've also encountered a scenario where I populated a treeview and found that it took a very long time. After tweaking the database for 2 days I realized that it took almost as long to fill the treeview with random numbers. I had wasted my time assuming that the database was the problem. I upgraded to a better treeview and the problem was solved.

weird_littlegirl · Answer

So I built the treeview using Microsoft.Web.UI.WebControls.TreeView and now I have another problem. I don't want it to contact the server everytime a plus/minus is clicked I only want to contact the server if the +/- sign has not been clicked before. Is there a way to accomplish this? Thanks.

weird_littlegirl · Answer

Thank you for the reply. I figured out how to do it in a Windows form... I wasn't (still not) very familiar with the DataSet API but after studying it a bit more I noticed a method called Merge(). Anyways I was able to first load the top level and its children. This fixed the problem of not having the plus/minus sign appear. So everytime the user clicks on a + the children would already be in memory what I would be getting would be the grandchildren of that child. I hope I'm making sense. Anyways now my problem is I need to move this to the web. I was thinking of just throwing what I did as a control in a webpage (using the tag). For some reason that is not working I'd just get a blank box in my web page. I'm not sure if it's because of some database access permissions or not. Now that I've looked around the net a bit more I noticed that Microsoft has a Web Control TreeView which is built specifically for the web. The only problem with this is that the page seems to refresh everytime the menu is expanded and the database needs to be contacted. I read that there is the SmartNavigation that can take care of this with double buffering but I also heard it's buggy... So again I'm not sure how I can approach this. I figure I can do it the old-school way.... which is load the treenodes using javascript but it seems tedious and I'm wondering if there's an easier way to do it. Any suggestions? Thanks.

sjones · Answer

I have to dissagree with this. If you can foresee a problem fix it now. The example above is obviously going to be a problem and fixing it later would require extensive rework. Including major changes to how the user interacts with the app. One way for you to do this is like you suggested load the first two levels. Then when you get a BeforeExpand event go to the database and populate the children of just that node. The down side is that you have to do without the + and - images that let a user know there is a reason to expand a node. You also need to figure out how to let the user know they actually did doubleclick to expand a node when there turns out to be not children. One way I have gotten around this is the past is to change the font of an empty node. You can make parents bold and empty nodes light. This is like the problem Windows Explorer has with network drives. It sometimes doesn't read the contents of a remote directory to know it has sub-directories and therefore does not show the + to let us know it is expandable. Once you doubleclick the directory though Windows reads the contents finds the subdirectories and adds the child nodes. Then we have our + indicator. Some would argue this is the way Tree controls should work anyway when you are working with dynamic data. If you load the tree at the start of an app the data may become stale. But if you are refreshing everytime a node is expanded you get a more current view. Explorer also has issues with this sometimes on remote drives. Ever have to manually hit the Refresh button to see new files that you know should be there? If you want to employ a strategy like this to always have fresh data you need to be concerned with expanded nodes that remain expanded for too long. The TreeView control used to have the ability to automatically collapse a node when another was expanded. This would help to to avoid that problem. But I don't see it anywhere in the .NET's version.

spgilmore · Answer

You seem to be trying to solve a problem that you haven't run into yet. How can you know what the solution is? You should first try it and see how long it takes. If it takes too long find out WHY. If the time is spent populating the treeview then you should lead the database and store it locally populating the treeview only as necessary. If it it the database query that takes too long you should QUERY it piece by piece. Knuth says premature optimization is the root of all evil. Don't fix it until it's broken.