如何提高这个linq查询的性能？

本文介绍了如何提高这个linq查询的性能？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

我有一个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个函数顺序可以提高查询性能吗？

Quote：

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

解决方案

var results = from myRow in dtTaskandBugs.AsParallel（）选择 myRow; results.ForAll（r = > {r [ Storyid] = GetStoryid（r [ Id ]）; r [ FeatureID] = Fidname（r [ 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 ）的工作示例以及帮助您使其工作的注释： 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 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 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） [ ^ ]， 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 ?

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查询的性能？