优化LINQ查询 - 如何提高执行时间？

问题描述

我想知道是否有一个很好的方法来优化我的LINQ查询。我使用类似于以下内容的LINQ查询从数据库中检索数据：

  // PK是一个整数列表
 var import = context.table.Where（x => PKs.Contains（x.PrimaryKey））。AsEnumerable（）; 
  

我假设（错误地）这将导致一个如下的SQL调用：

（PK [1]，PK [2]，...）中的PrimaryKey的表中的$ *

$ b $ $ $ $ c>

ie 1数据库命中。使用检查SQL调用，我看到LINQ查询被转换为许多单独的SQL调用，例如：

  SELECT * from Table where PrimaryKey = PK [1] 
 SELECT * from table where PrimaryKey = PK [2] 
 ... 
  

个别查询执行得很快，但是由于我的整数列表很大（以千计），由于开销导致的偏移量导致查询花费大约25秒才能运行。

我决定删除耗时的LINQ查询数据库调用并通过存储过程调用导入数据：

  var import = this.p_import（parameter）.ToList（）; 
  

这显着改善了响应时间，因为只有一个数据库调用，从大约25秒到下1秒。

这解决了我的问题，但是我使用LINQ的全部原因是为了避免使用存储过程。有没有更好的方法可以优化我的LINQ查询？

解决方案

如果你改变加入，事情应该会更多

  context.table.Join（PKs，t => t.PrimaryKey，pk => pk，（t ，pk）=> t）.AsEnumerable（）
  

Linq2Sql不支持将实体加入POCO列表，但只是用EF测试，一切都很好。

使用Linq2Sql，您被困在包含在这种情况下，我可能只需要使用表值参数进行存储过程。

I'm wondering if there is a good way to optimize my LINQ queries. I am retrieving data from a database using a LINQ query similar to the following:

// PKs is a list of integers
var import = context.table.Where(x => PKs.Contains(x.PrimaryKey)).AsEnumerable();

I assumed (incorrectly) that this would result in a SQL call like the following:

SELECT * from table where PrimaryKey in (PK[1], PK[2], ...)

i.e 1 database hit. Using Glimpse to inspect the SQL calls I saw that the LINQ query was translated into many separate SQL calls, e.g:

SELECT * from table where PrimaryKey=PK[1]
SELECT * from table where PrimaryKey=PK[2]
...

The individual queries were being executed very quickly, but as my list of integers was large (in the thousands), the offset due to overheads was resulting in the query taking around 25 seconds to run.

I decided to strip out the time-consuming LINQ queries with database calls and import the data via a stored procedure call:

var import = this.p_import(parameter).ToList();

This dramatically improved the response time, as there was only one database call, from about 25 seconds to under 1 second.

This solved my problem, but the whole reason I was using LINQ was to avoid using stored procedures. Is there a better way I could optimize my LINQ queries?

解决方案

If you change to a join, things should go a lot more smoothly.

context.table.Join(PKs,t => t.PrimaryKey, pk => pk, (t, pk) => t).AsEnumerable()

Linq2Sql does not support joining entities to POCO lists, but just tested with EF and all is good.

With Linq2Sql, you're stuck with Contains, in which case, I'd probably just go for a stored procedure with table-value parameters.

这篇关于优化LINQ查询 - 如何提高执行时间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！