effective data fetching in asp.net

I have this much:

A table which used to store the "Folders". Each folder may contain sub folders and files. So if I click a folder, I have to list the content of the folder.

The table to represent the folder listing is something like the following

FolderID   Name  Type  Desc ParentID

In the case of sub folders, ParentID is refer to the FolderID of the parent folder.

Now, my questions are

1. a. There 开发者_JAVA技巧are 3 type of folders, I use 3 data lists to categorize them. Can I load the entire table in a single fetch and then use LINQ to categorize the types.

OR

b. Load each category by passing 'Type' to stored procedure. Which will do 3 database calls.

2. a. If I click the parent folder, use LINQ to filter the contents of the folder(because we have the entire table in memory)

OR

b. If I click the parent folder, pass the FolderID of the parent folder and then fetch the content.

In the two cases above, which points makes more sense, which points are best in the case of performance?

There are a number of considerations you need to make.

• What is the size of the folder tree, if not currently large could it potentially become very large?

• What is the likelihood that the folder table will be modified whilst a user is using/viewing it? If there is a high chance then it may be worthwhile to make smaller, more frequent calls to the DB so that the user is aware of any changes which have been made by other users.

• Will users by working with one folder type at a time? Or will they be switching between these three different trees?

As an instinctive answer I would be drawn towards calling 1 or 2 levels at a time. For example - start with loading root folder and immediate children. As the user navigates down into the tree, retrieve more children...

When you are questioning about the performance, the only available answer is:

Measure it!. Implement both scenarios and look at them - how can they load your system.
Try to think, how will you cache your data to prevent high database load.
All works fast for small n, so we can't say something for sure.

If your data is small and changed not frequently, then use Caching and LINQ-based queries for your cache data.
If your data can't be stored in cache because it is huge, or it changes constantly, then cache the results of your queries, create cache dependensies for them, and again, measure it!