Speed Performance In Recursive SQL Requests

I have so category and this categories have unlimited sub category. In Database Table, Fields are ID, UpperID and Title.

If I call a category and its subcategory in DataTable with recursive method in program(ASP.NET project开发者_如何学C) performance is very bad. And many user will use this application so everything goes bad. Maybe All categories Fill to A Cache object and then we musnt go to Database. But category count is 15000 or 20000. So I think isn't a good method.

What can I do for fast performance? Are you give me any suggestion?

---

caching or other in-memory-persistance is by far better than doing this on a relational system :) ... hey... it's oop!

just my 2 cents!

eg.

var categories = /* method for domain-objects*/.ToDictionary(category => category.ID);
foreach (var category in categories.Values)
{
    if (!category.ParentCategoryID.HasValue)
    {
        continue;
    }
    Category parentCategory;
    if (categories.TryGetValue(category.ParentCategoryID.Value, out parentCategory))
    {
        parentCategory.AddSubCategory(category);
    }
}

et voila ... your tree is ready to go!

edit:
do you exactly know where your performance bottle-neck is?...

to give you some ideas, eg:

• loading from database
• building up the structure
• querying the structure

loading from database:
then you should load it once and be sure to have some changetracking/notifying to get changes (if made) or optimize your query!

building up the structure:
the way i create the tree (traversal part) is the wastest you can do with a Dictionary<TKey, TValue>

querying the structure:
the structure i've used in my example is faster than List<T>. Dictionary<TKey, TValue> uses an index over the keys - so you may use int for the keys (IDs)

edit:

So you use DataTable to fix the problem. Now you've got 2 problems: me and DataTable

what do you have right now? where are you starting from? can you determine where your mudhole is? give us code!

---

Thanks All,

I find my solution with Common Table Expressions(CTE) fifty- fifty. Its allow fast recursive queries.

WITH CatCTE(OID, Name, ParentID) 
AS 
( 
   SELECT OID, Name, ParentID FROM Work.dbo.eaCategory
   WHERE OID = 0   
       UNION ALL 
   SELECT C.OID, C.Name, C.ParentID FROM Work.dbo.eaCategory  C JOIN CatCTE as CTE ON C.ParentID= CTE.OID
) 
SELECT * FROM CatCTE