如何提高这个linq查询的性能？ [英] How to improve performance of this linq query ?

问题描述

我有一个Linq查询，目前大约需要15分钟才能运行，因为它正在调用3个函数顺序可以提高查询性能吗？

 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？匿名：fname（ r [FeatureID]）; 
 
}）; 

我尝试过：



i有一个Linq查询，目前大约需要15分钟才能运行，因为它调用了3个函数顺序可以提高查询性能吗？

 var results = from myRow in dtTaskandBugs.AsParallel（）
 select myRow; 
 results.ForAll（async r => 
 {
任务< int> storyProcessing = GetStoryid（r [Id]）; 
任务< int> fidProcessing = Fidname（ r [Storyid]）; 
任务< string> featureProcessing = r [FeatureID]。ToString（）==0？Task.FromResult（Anonymous）：fname（r [FeatureID ]）; 
r [Storyid] =等待storyProcessing; 
r [FeatureID] =等待fidProcessing; 
r [FeatureName] = await featureProcessing; 
}）; 
 
 
公共异步任务< 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'）+ 
和（[System.Links.LinkType] ='System.LinkTypes.Hierarchy-Forward'）和（[Target]。 [System.Id] = {0}+ 
AND [Target]。[System.WorkItemType] ='Task'）+ 
ORDER BY [System.Id]模式（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）//只有1个孩子及其父亲
 {
返回_links [1] .SourceId; 
} 
其他
 {
返回0; 
} / *延迟重处理* / 
}）; 
 //任何独立处理
 int result =等待处理; 
 //处理取决于结果
返回结果; 
} 
 
公共异步任务< 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）//只有1个孩子及其父母
 {
返回_links [1] .TargetId; 
} 
其他
 {
返回0; 
} / *延迟重处理* / 
 }）; 
 //任何独立处理
 int result =等待处理; 
 //处理取决于结果
返回结果; 
 
} 
 
公共异步任务< string> fname（object fid）
 {
任务< 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; 
} 
 
返回Ftitle; / *延迟重处理* / 
 }）; 
 //任何独立处理
字符串结果=等待处理; 
 //处理取决于结果
返回结果; 
} 

Quote：

fidname函数抛出错误，_id是空白请告诉我需要做些什么来解决这个错误。

解决方案

  var  results =  from  myRow  in  dtTaskandBugs.AsParallel（）
 选择 myRow; 
 results.ForAll（r = >  
 {
r [  Storyid] = GetStoryid（r [  Id ]）; 
r [  FeatureID] = Fidname（r [< span class =code-string>  Storyid]）; 
r [  FeatureName] = r [  FeatureID ]。ToString（）==   0？
  匿名：
 fname（r [  FeatureID]）; 
}）; 

如果性能是一个PLINQ可能会有所帮助问题。它实际上通过并行运行查询来确定它是否期望加速，并且将相应地并行或顺序运行。



如果您可以控制 GetStoryid（）， Fidname（），或 fname（）你可以使用的功能使用 async / 等待。如果他们有很大的延迟（例如访问另一个数据库或其他东西），这可能会有所帮助：

  public   async 任务< int> GetStoryid（ int  id）
 {
 Task< int> processing = Task.Run（（）= >  { / *  延迟重处理* / }）; 
  //  任何独立处理 
  int  result = 等待处理; 
  //  处理取决于结果 
 返回结果; 
} 
  //   Fidname（）和fname（）的基本思路相同
 
  var  results = 来自 myRow  in  dtTaskandBugs.AsParallel（）
 选择 myRow; 
 results.ForAll（ async  r = >  
 {
任务< int> storyProcessing = GetStoryid（r [  Id]）; 
任务< int> fidProcessing = Fidname（r [  Storyid]）; 
任务< string> featureProcessing = r [  FeatureID]。ToString（）==   0？
 Task.FromResult（ 匿名）：
 fname（r [  FeatureID ]）; 
r [  Storyid] =  await  storyProcessing; 
r [  FeatureID] = 等待 fidProcessing; 
r [  FeatureName] =  await  featureProcessing; 
}）; 

编辑：删除原始编辑空间。

EDIT2：所以之后更多地阅读查询并看到下面的PLINQ没有帮助，我猜测数据库调用是你最大的问题。下面的代码演示了我的建议（ async ）的工作示例以及帮助您使其工作的注释：

< pre lang =c＃> class 计划
{
private static 随机randomNumber = new Random（）;

静态 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 = 来自 myRow 中的code-keyword>
选择 myRow;
列表<任务> tasks = new List< Task>（）;
foreach （ var 结果 in results）
tasks.Add（Process（result））;
Task.WaitAll（tasks.ToArray（））; // 这是一个等待的阻止操作
// 要完成的所有任务
Console.ReadKey（）;
}

public static async 任务流程（字典< string，int>行）
{
任务< int> storyProcessing = GetStoryid（row [ StoryId]）;
任务< int> fidProcessing = Fidname（行[ FeatureId]）;
任务< string> nameProcessing = fname（row [ Id]）;
await Task.WhenAll（storyProcessing，fidProcessing，nameProcessing）;
// ---------
Console.WriteLine （

＃{row [ Id ]}完成.SID：{storyProcessing.Result}，FID：{fidProcessing.Result}，FN：{nameProcessing.Result} ）;
/ * ---------
*将以上内容替换为：
* row [StoryId] = storyProcessing.Result;
* row [FeatureId] = fidProcessing.Result;
* row [Id] = nameProcessing.Result;
* /
}

public static async 任务< int> GetStoryid（ int id）
{
// ---------
await Task.Delay（randomNumber.Next（ 10000 ））;
return id * 10 ;
/ * ---------
*将以上内容替换为：
* string _wiql = String.Format（SELECT [System.Id]，[System.Title]+
FROM WorkItemLinks WHERE（[Source]。[System.WorkItemType] ='Product Backlog Item '）+
和（[System.Links.LinkType] ='System.LinkTypes.Hierarchy-Forward'）和（[Target]。[System.Id] = {0}+
AND [Target]。[System.WorkItemType] ='Task'）+
ORDER BY [System.Id] mode（Recursive，ReturnMatchingChildren），_ TBId）;
*查询_query = new Query（_workitemstore，_wiql）;
* WorkItemLinkInfo [] links = await Task.Factory.FromAsync（_query.BeginLinkQuery，_query.EndLinkQuery）;
* if（links.Count（）== 2）
* return links [1] .SourceId;
*返回0;
* /
}
public static < span class =code-keyword> async 任务< int> Fidname（ int id）
{
// ---------
await Task.Delay（randomNumber.Next（ 10000 ））;
return id * 10 ;
/ * ---------
*将以上内容替换为：
* string _wiql = String.Format（SELECT [System.Id]，[System.Title]，[System.Links.LinkType] FROM WorkItemLinks WHERE（[Source]。[System.Id] = {0} ）+
和（[System.Links.LinkType] ='System.LinkTypes.Hierarchy-Reverse'）+
和（[Target]。[System.WorkItemType] ='Feature' ）ORDER BY [System.Id]，_ id）;
*查询_query = new Query（_workitemstore，_wiql）;
* WorkItemLinkInfo [] links = await Task.Factory.FromAsync（_query.BeginLinkQuery，_query.EndLinkQuery）;
* if（links.Count（）== 2）
* return links [1] .TargetId;
*返回0;
* /
}
public static < span class =code-keyword> async 任务< string> fname（ int id）
{
await Task.Delay（randomNumber.Next （ 10000 ））;
return

{id * 10};
/ * ---------
*将以上内容替换为：
* string Ftitle =;
* string _wiql = string.Format（SELECT [System.Title]，[System.Id]+
FROM WorkItems WHERE [System.Id] = {0}+
AND [System.WorkItemType] ='功能'ORDERBY [Microsoft.VSTS.Common.Priority]，fid）;
*查询_query = new Query（_workitemstore，_wiql）;
* WorkItemCollection workItems = await Task.Factory.FromAsync（_query.BeginQuery，_query.EndQuery）;
* foreach（workItem中的WorkItem workItem）
* Ftitle = workItem.Title;
*返回Ftitle;
* /
}
}

更多信息：基于任务的异步编程（TAP） [ ^ ]，< a href =https://docs.microsoft.com/en-us/dotnet/standard/asynchronous-programming-patterns/interop-with-other-asynchronous-patterns-and-types> APM to TAP [ ^ ]和查询 [ ^ ]。

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.

解决方案

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:

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:

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[^].

这篇关于如何提高这个linq查询的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！