This thing was not noticed to be slow at first because there were not many 'tree nodes' in the database. Now it's really slow, at a glance is there anything major that is wrong with this? I really need to optimize it and before I rework the entire thing I was wondering if anything stands out as being the real pain point. I have narrowed down the slow part to a recursive repository function which is the last thing on this post, I have to define a few things leading up to it though... good luck. (NOTE: I did not write this, I was just put on damage control and I am trying to wrap my head around it.)
A few things to be understood first:
The JsTreeNode definition:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace App.Models
{
public class JsTreeNode
{
public JsTreeNode()
{
}
public Attributes attributes { get; set; }
public Data data { get; set; }
public string state { get; set; }
public List<JsTreeNode> children { get; set; }
}
public class Attributes
{
public string id { get; set; }
public string rel { get; set; }
public string mdata { get; set; }
public string href { get; set; }
public string complete { get; set; }
public string edit { get; set; }
public string title { get; set; }
public string resourceAccountID { get; set; }
}
public class Data
{
public string title {get;set;}
public string icon {get;set;}
}
}
Now the TreeNodes table definition:
CREATE TABLE [dbo].[TreeNodes](
[TreeNodeID] [int] IDENTITY(1,1) NOT NULL,
[TreeID] [int] NOT NULL,
[ParentNodeID] [int] NULL,
[ResourceID] [int] NULL,
[NodeOrder] [int] NOT NULL,
[NodeName] [nvarchar](250) NOT NULL,
[CreateBy] [int] NULL,
[CreateDate] [datetime] NULL,
[ModifyBy] [int] NULL,
[ModifyDate] [datetime] NULL,
[TreeRevisionID] [int] NULL,
The tree revisions table definition...
CREATE TABLE [dbo].[TreeRevisions](
[TreeRevisionID] [int] IDENTITY(1,1) NOT NULL,
[TreeID] [int] NOT NULL,
[Notes] [text] NULL,
[CreatedBy] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifyBy] [int] NOT NULL,
[ModifyDate] [datetime] NOT NULL,
Now here is the Get Action itself that is taking 20-25 seconds, you can see in there that it calls repository function that I have defined further down the this thread.
public ContentResult Get(string target,int id)
{
int revisionID = Convert.ToInt32(Request.QueryString["revisionID"]);
int tempUserID = (Request.QueryString["userID"] != null)
? Convert.ToInt32(Request.QueryString["userID"])
: UserID;
var nodesList = new List<JsTreeNode>();
if(target.Contains("tree"))
{
tree tree = _treeRepository.GettreeByID(id);
var cnode = new JsTreeNode
{
attributes = new Attributes {id = "0",title = tree.treeName},
data = new Data {title = tree.treeName},
children = _treeNodesRepository.GetNodesBytreeID(id, null,revisionID),
state = "open"
};
cnode.attributes.rel = "root";
cnode.attributes.mdata = "{draggable : true}";
nodesList.Add(cnode);
}
else
{
var trees = _CategoryRepository.getAlltreesByCategoryID(id);
IQueryable<tree> custom;
if(revisionID != 0)
{
custom = from c in trees
where
c.AccountID == AccountID
select c;
} else
{
custom = from c in trees
where
c.AccountID == AccountID && c.PublishedRevisionID != null && c.PublishedRevisionID != 0
select c;
}
var acme = from c in trees where c.AccountID == acmeContent.acmeID select c;
foreach (var tree in (custom.Count() > 0) ? custom : acme)
{
if(revisionID == 0 && tree.PublishedRevisionID == null) continue;
int tempRev = (revisionID != 0) ? revisionID : (int)tree.PublishedRevisionID;
if(custom.Count() == 1)
{
var tempNodes = _treeNodesRepository.GetNodesBytreeID(tree.treeID, null, tempUserID, tempRev);
nodesList.AddRange(tempNodes);
}
else
{
var cnode = new JsTreeNode();
cnode.attributes = new Attributes { id = tree.treeID.ToString(), title = tree.treeName };
cnode.data = new Data { title = tree.treeName };
cnode.children = _treeNodesRepository.GetNodesBytreeID(tree.treeID, null, tempUserID, tempRev);
cnode.attributes.rel = "Folder";
nodesList.Add(cnode);
}
}
}
var ser = new JavaScriptSerializer();
string res = ser.Serialize(nodesList);
return Content(res,"application/json");
}
...and Finally, the 'culprit', see how it calls itself:
public List<JsTreeNode> GetNodesByTreeID(int TreeID, int? parentID,int userID, int revisionID)
{
IQueryable<UserTreeNode> TreeNodes = GetAllTreeNodesByTreeIDAndParentNodeID(TreeID, parentID,userID,revisionID);
List<JsTreeNode> nodesList = new List<JsTreeNode>();
foreach (UserTreeNode node in TreeNodes)
{
string nodeName = node.Node.NodeName.Replace("'", "'");
JsTreeNode cnode = new JsTreeNode
{
attributes = new Attributes
{id = node.Node.TreeNodeID.ToString(),
title = nodeName},
data = new Data {title = nodeName}
};
if(node.Node.ResourceID != null)
{
cnode.attributes.complete = (node.IsComplete) ? "true" : "false";
cnode.attributes.rel = ResourceTypes.ReturnResourceTypeName(_resourceRepository.getResourceByID(Convert.ToInt32(node.Node.ResourceID)).ResourceTypeID);
cnode.attributes.href = "/resource/" + node.Node.ResourceID + "?minimal=true&nodeID=" + node.Node.TreeNodeID.ToString();
}
else
{
var nodeChildren = GetNodesByTreeID(TreeID, node.Node.TreeNodeID,userID,revisionID);
if (nodeChildren.Count > 0)
cnode.children = nodeChildren;
cnode.attributes.complete = "false";
cnode.attributes.rel = "Folder";
}
nodesList.Add(cnode);
}
return nodesList;
}