Click here to Skip to main content
15,886,840 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
i have a Linq Query which is currently taking about 15 min to Run as it's calling 3 function Sequentially can this Query Performance be improve ?


var results = from myRow in dtTaskandBugs.AsEnumerable()
                          select myRow;
            results.ToList()
             .ForEach(
             r =>
             {
                 r["Storyid"] = GetStoryid(r["Id"]);
                 r["FeatureID"] = Fidname(r["Storyid"]);
                 r["FeatureName"] = r["FeatureID"].ToString() == "0" ? "Anonymous" : fname(r["FeatureID"]);

             });


What I have tried:

i have a Linq Query which is currently taking about 15 min to Run as it's calling 3 function Sequentially can this Query Performance be improve ?

var results = from myRow in dtTaskandBugs.AsParallel()
                              select myRow;
                results.ForAll(async r =>
                {
                    Task<int> storyProcessing = GetStoryid(r["Id"]);
                    Task<int> fidProcessing = Fidname(r["Storyid"]);
                    Task<string> featureProcessing = r["FeatureID"].ToString() == "0" ? Task.FromResult("Anonymous") : fname(r["FeatureID"]);
                    r["Storyid"] = await storyProcessing;
                    r["FeatureID"] = await fidProcessing;
                    r["FeatureName"] = await featureProcessing;
                });


        public async Task<int> GetStoryid(object _TbId)
            {
                Task<int> processing = Task.Run(() => {
                    string _wiql =
    String.Format("SELECT [System.Id],[System.Title] " +
    "FROM WorkItemLinks WHERE  ([Source].[System.WorkItemType] = 'Product Backlog Item') " +
    "And ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward') And ([Target].[System.Id] = {0}  " +
    "AND  [Target].[System.WorkItemType] = 'Task')" +
    " ORDER BY [System.Id] mode(Recursive,ReturnMatchingChildren)", _TbId);


                    Microsoft.TeamFoundation.WorkItemTracking.Client.Query _query = new Microsoft.TeamFoundation.WorkItemTracking.Client.Query(_workitemstore, _wiql);
                    WorkItemLinkInfo[] _links = _query.RunLinkQuery();
                    if (_links.Count() == 2) //only 1 child and its parent
                    {
                        return _links[1].SourceId;
                    }
                    else
                    {
                        return 0;
                    }/*delay-heavy processing*/
                });
                //Any independent processing
                int result = await processing;
                //Processing dependent on the result
                return result;
            }

            public async Task <int> Fidname(object _id)
            {
                Task<int> processing = Task.Run(() => {
                    string _wiql =

    String.Format("SELECT [System.Id],[System.Title],[System.Links.LinkType] FROM WorkItemLinks WHERE ([Source].[System.Id] = {0})" +
    " And ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Reverse')" +
    " And ([Target].[System.WorkItemType] = 'Feature') ORDER BY [System.Id]", _id);


    Microsoft.TeamFoundation.WorkItemTracking.Client.Query _query = new Microsoft.TeamFoundation.WorkItemTracking.Client.Query(_workitemstore, _wiql);

                    WorkItemLinkInfo[] _links = _query.RunLinkQuery();
                    if (_links.Count() == 2) //only 1 child and its parent
                    {
                        return _links[1].TargetId;
                    }
                    else
                    {
                        return 0;
                    }/*delay-heavy processing*/
                });
                //Any independent processing
                int result = await processing;
                //Processing dependent on the result
                return result;

    }

            public async Task<string> fname(object fid)
            {
                Task<string> processing = Task.Run(() => {
                    string Ftitle = "";
                    string _wiql = string.Format("SELECT[System.Title], [System.Id]" +
                        " FROM WorkItems WHERE[System.Id]  = {0}" +
                        "  AND[System.WorkItemType] = 'Feature' ORDER BY[Microsoft.VSTS.Common.Priority]", fid);
                    Microsoft.TeamFoundation.WorkItemTracking.Client.Query _query = new Microsoft.TeamFoundation.WorkItemTracking.Client.Query(_workitemstore, _wiql);
                    WorkItemCollection workItemCollection = _query.RunQuery();

                    foreach (WorkItem workItem in workItemCollection)
                    {
                        Ftitle = workItem.Title;
                    }

                    return Ftitle; /*delay-heavy processing*/
                });
                //Any independent processing
                string result = await processing;
                //Processing dependent on the result
                return result;
            }

Quote:
fidname function is throwing error and _id is blank pls tell me what need to be done to fix this error.
Posted
Updated 3-Mar-18 1:23am
v3
Comments
Patrice T 3-Mar-18 0:04am    
What are those 3 functions are doing?
Akhil Jain 3-Mar-18 0:25am    
wiql is there in all of them returning int/string
Patrice T 3-Mar-18 3:34am    
What of work are they doing?
integer factorization? cipher? hashing? ...
Akhil Jain 3-Mar-18 4:41am    
wiql query tfs workitem checking parent or not

1 solution

C#
var results = from myRow in dtTaskandBugs.AsParallel()
              select myRow;
results.ForAll(r =>
  {
    r["Storyid"] = GetStoryid(r["Id"]);
    r["FeatureID"] = Fidname(r["Storyid"]);
    r["FeatureName"] = r["FeatureID"].ToString() == "0" ? 
      "Anonymous" : 
      fname(r["FeatureID"]);
  });

PLINQ could help if performance is an issue. It actually determines whether it expects a speed-up by running the query in parallel and will run parallel or sequentially accordingly.

If you have control over the GetStoryid(), Fidname(), or fname() functions you could use async/await. If they have heavy delay (e.g. accessing another DB or something) this might help:
C#
public async Task<int> GetStoryid(int id)
{
  Task<int> processing = Task.Run(() => { /*delay-heavy processing*/});
  //Any independent processing
  int result = await processing;
  //Processing dependent on the result
  return result;
}
//Same basic idea for Fidname() and fname()

var results = from myRow in dtTaskandBugs.AsParallel()
              select myRow;
results.ForAll(async r =>
  {
    Task<int> storyProcessing = GetStoryid(r["Id"]);
    Task<int> fidProcessing = Fidname(r["Storyid"]);
    Task<string> featureProcessing = r["FeatureID"].ToString() == "0" ? 
      Task.FromResult("Anonymous") : 
      fname(r["FeatureID"]);
    r["Storyid"] = await storyProcessing;
    r["FeatureID"] = await fidProcessing;
    r["FeatureName"] = await featureProcessing;
  });


EDIT: original edit deleted for space.
EDIT2: So after reading more into Query and seeing below that PLINQ isn't helping, I'm guessing the DB calls are your biggest problem. The code below demonstrates a working example of what my suggestion would be (async) along with comments to help you to get it to work:
C#
class Program
{
    private static Random randomNumber = new Random();

    static void Main(string[] args)
    {
        int tableRows = 10;
        List<Dictionary<string, int>> table = new List<Dictionary<string, int>>();
        for (int i = 0; i < tableRows; i++)
        {
            Dictionary<string, int> row = new Dictionary<string, int>();
            row.Add("Id", i);
            row.Add("StoryId", tableRows + i);
            row.Add("FeatureId", tableRows * 2 + i);
            table.Add(row);
        }

        var results = from myRow in table
                      select myRow;
        List<Task> tasks = new List<Task>();
        foreach (var result in results)
            tasks.Add(Process(result));
        Task.WaitAll(tasks.ToArray()); //this is a blocking operation to wait on
                                       //all tasks to complete
        Console.ReadKey();
    }

    public static async Task Process(Dictionary<string, int> row)
    {
        Task<int> storyProcessing = GetStoryid(row["StoryId"]);
        Task<int> fidProcessing = Fidname(row["FeatureId"]);
        Task<string> nameProcessing = fname(row["Id"]);
        await Task.WhenAll(storyProcessing, fidProcessing, nameProcessing);
        //---------
        Console.WriteLine($"#{row["Id"]} complete. SID: {storyProcessing.Result}, FID: {fidProcessing.Result}, FN: {nameProcessing.Result}");
        /*---------
         * Replace above with something like:
         * row["StoryId"] = storyProcessing.Result;
         * row["FeatureId"] = fidProcessing.Result;
         * row["Id"] = nameProcessing.Result;
         */
    }

    public static async Task<int> GetStoryid(int id)
    {
        //---------
        await Task.Delay(randomNumber.Next(10000));
        return id * 10;
        /*---------
         * Replace above with something like:
         * string _wiql = String.Format("SELECT [System.Id],[System.Title] " +
                           "FROM WorkItemLinks WHERE ([Source].[System.WorkItemType] = 'Product Backlog Item') " +
                           "And ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward') And ([Target].[System.Id] = {0}  " +
                           "AND  [Target].[System.WorkItemType] = 'Task')" +
                           " ORDER BY [System.Id] mode(Recursive,ReturnMatchingChildren)", _TbId);
         * Query _query = new Query(_workitemstore, _wiql);
         * WorkItemLinkInfo[] links = await Task.Factory.FromAsync(_query.BeginLinkQuery, _query.EndLinkQuery);
         * if (links.Count() == 2)
         *     return links[1].SourceId;
         * return 0;
         */
    }
    public static async Task<int> Fidname(int id)
    {
        //---------
        await Task.Delay(randomNumber.Next(10000));
        return id * 10;
        /*---------
         * Replace above with something like:
         * string _wiql = String.Format("SELECT [System.Id],[System.Title],[System.Links.LinkType] FROM WorkItemLinks WHERE ([Source].[System.Id] = {0})" +
                          " And ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Reverse')" +
                          " And ([Target].[System.WorkItemType] = 'Feature') ORDER BY [System.Id]", _id);
         * Query _query = new Query(_workitemstore, _wiql);
         * WorkItemLinkInfo[] links = await Task.Factory.FromAsync(_query.BeginLinkQuery, _query.EndLinkQuery);
         * if (links.Count() == 2)
         *     return links[1].TargetId;
         * return 0;
         */
    }
    public static async Task<string> fname(int id)
    {
        await Task.Delay(randomNumber.Next(10000));
        return $"{id * 10}";
        /*---------
         * Replace above with something like:
         * string Ftitle = "";
         * string _wiql = string.Format("SELECT[System.Title], [System.Id]" +
                              " FROM WorkItems WHERE[System.Id]  = {0}" +
                              "  AND[System.WorkItemType] = 'Feature' ORDERBY[Microsoft.VSTS.Common.Priority]", fid);
         * Query _query = new Query(_workitemstore, _wiql);
         * WorkItemCollection workItems = await Task.Factory.FromAsync(_query.BeginQuery, _query.EndQuery);
         * foreach (WorkItem workItem in workItems)
         *     Ftitle = workItem.Title;
         * return Ftitle;
         */
    }
}

More info: Task-based Asynchronous Programming (TAP)[^], APM to TAP[^], and Query[^].
 
Share this answer
 
v10
Comments
Akhil Jain 3-Mar-18 7:23am    
getting fidname function is throwing error and _id is blank pls tell me what need to be done to fix this error. have updated my code in tried portion.
Jon McKee 3-Mar-18 12:41pm    
What error are you seeing? If _id is blank at the start of the function I'd check to ensure r["Storyid"] exists. I'd also try to see if you could batch execute the queries for each row. Each row select triggering 3 separate DB queries isn't exactly a great idea if performance is an issue.
Akhil Jain 3-Mar-18 21:35pm    
_id in fidname not getting value and due to which Query in string.format in that function (fidname) has not receive the value and throwing the error.very fine but slow (17 min) if i remove the Code u suggested i this answer.
Jon McKee 3-Mar-18 23:03pm    
Is the error coming from the Query and if so which part - the constructor call or the query execution? If not, is it String.Format? If so, could you put a breakpoint before Task.Run and on string _wiql. Check the value of _id. If it's fine on the first breakpoint but not on the second, try moving the string _wiql definition to the start of the function instead of inside the Task.Run. Debugging is incredibly difficult without stack traces, the full relevant code, and knowledge of the specific libraries used like Microsoft.TeamFoundation.WorkItemTracking.Client. I do have a new suggestion up top though after reading about Query for after debugging.
Akhil Jain 3-Mar-18 23:17pm    
Query execution as it does not receive the _id.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900