포함 () 연산자가 Entity Framework의 성능을 향상시키는 이유는 무엇입니까?

포함 () 연산자가 Entity Framework의 성능을 향상시키는 이유는 무엇입니까?

---

업데이트 3 : 이 발표 에 따르면 EF6 알파 2의 EF 팀 이이 문제를 해결했습니다.

업데이트 2 :이 문제를 해결하기위한 제안을 만들었습니다. 투표 비용 여기로 이동하세요 .

하나의 매우 간단한 테이블이있는 SQL 데이터베이스를 고려하십시오.

CREATE TABLE Main (Id INT PRIMARY KEY)

테이블에 10,000 개의 레코드를 채 있습니다.

WITH Numbers AS
(
  SELECT 1 AS Id
  UNION ALL
  SELECT Id + 1 AS Id FROM Numbers WHERE Id <= 10000
)
INSERT Main (Id)
SELECT Id FROM Numbers
OPTION (MAXRECURSION 0)

테이블에 대한 EF 모델을 빌드하고 LINQPad에서 다음 쿼리를 실행합니다 ( "C # Statements"모드를 사용하여 LINQPad가 자동으로 생성되지 않음).

var rows = 
  Main
  .ToArray();

실행 시간은 ~ 0.07 초입니다. 이제 연산자를 추가하고 쿼리를 다시 실행합니다.

var ids = Main.Select(a => a.Id).ToArray();
var rows = 
  Main
  .Where (a => ids.Contains(a.Id))
  .ToArray();

이 경우 실행 시간은 20.14 초 (288 배 느림)입니다!

처음에는 쿼리를 위해 내 보낸 T-SQL이 실행하는 데 시간이 더 오래 걸리다고 생각했기 때문에 LINQPad의 SQL 창에서 잘라내어 SQL Server Management Studio로 널 넣었습니다.

SET NOCOUNT ON
SET STATISTICS TIME ON
SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Primary] AS [Extent1]
WHERE [Extent1].[Id] IN (1,2,3,4,5,6,7,8,...

결과는

SQL Server Execution Times:
  CPU time = 0 ms,  elapsed time = 88 ms.

다음으로 LINQPad가 문제의 원인이라고 생각했지만 LINQPad에서 실행하든 콘솔 응용 프로그램에서 실행하든 성능은 동일합니다.

따라서 문제는 Entity Framework 내 어딘가에있는 시청합니다.

내가 여기서 뭔가 잘못하고 있니? 이것이 내 코드에서 시간이 중요한 부분 성능을 높이기 위해 할 수있는 일이 있습니까?

Entity Framework 4.1 및 Sql Server 2008 R2를 사용하고 있습니다.

업데이트 1 :

아래 토론에는 EF가 초기 쿼리를 작성하는 동안 지연이 발생했거나 다시받은 데이터를 구문 분석하는 동안 발생했는지에 대한 몇 가지 질문이 있습니다. 관리자 테스트하기 위해 다음 코드를 실행했습니다.

var ids = Main.Select(a => a.Id).ToArray();
var rows = 
  (ObjectQuery<MainRow>)
  Main
  .Where (a => ids.Contains(a.Id));
var sql = rows.ToTraceString();

이는 EF가 데이터베이스에 대해 실행하지 않고 쿼리를 생성합니다. 그 결과가 코드를 실행하는 데 ~ 20 개의 secord가 필요 초기 쿼리를 작성하는 거의 모든 시간이 드는 데 거의 모든 시간이 소요되는 데 있습니다.

CompiledQuery를 구출 비용? 그리 빠르지 언어. CompiledQuery는 쿼리에 전달 된 매개 변수가 기본 유형 (int, string, float 등)이어야합니다. 배열 또는 IEnumerable을 허용하지 않습니다.

---

업데이트 : EF6에 InExpression이 추가됨에 따라 Enumerable. 처리 성능이 크게 향상되었습니다. 이 답변에 설명 된 접근 방식은 더 이상 필요하지 않습니다.

대부분의 시간이 쿼리 번역을 처리하는 데 소비가 좋습니다. EF의 공급자 모델에는 현재 IN 절을 갖추고 있지 않습니다. ADO.NET 대신 Enumerable.Contains 구현은이를 OR 그대로 트리로 변환합니다. 즉, C #에서 다음과 같은 것입니다.

new []{1, 2, 3, 4}.Contains(i)

... 다음과 같이 표현할 수있는 DbExpression 트리를 생성합니다.

((1 = @i) OR (2 = @i)) OR ((3 = @i) OR (4 = @i))

(표현식 트리는 균형을 이루어야합니다. 왜냐하면 우리가 하나의 긴 척추에 대한 모든 OR이 가능하기 때문에 더 많기 때문입니다 (예, 실제로는 작업을 수행했습니다)).

나중에 이와 같은 트리를 ADO.NET 공급자에게 보냅니다. ADO.NET 공급자는 SQL 생성 중에 사용 가능합니다.

EF4 Enumerable.Contains에 대한 지원을 추가 할 때 공급자 모델에서 입력에 대한 지원을 도입하지 않고 수행하는 것이 바로 예상되는 요소 수보다 많은 고객이 있습니다. Enumerable.Contains. 즉, 이것이 바로 성가신 일입니다.

개발자 중 한 명과 문제를 논의하고 앞으로 IN에 대한 최고 수준의 지원을 추가하여 구현을 믿습니다. 나는 이것이 우리의 백 로그에 추가해야 할 것이지만, 우리가 만들고 싶은 다른 많은 개선이 있기 때문에 언제 만들어 질지 약속 할 수 없습니다.

제안에서 이미 제안 된 해결 방법에 다음을 추가합니다.

포함에 전달하는 요소 수와 데이터베이스 왕복 수의 균형을 맞추는 방법을 만드는 것이 좋습니다. 예를 들어, 필자의 테스트에서 SQL Server의 로컬 인스턴스에 대해 계산하고 실행하는 데 100 개의 요소가있는 쿼리가 1/60 초가되는 것을 관찰했습니다. 100 개의 서로 다른 ID 세트로 100 개의 쿼리를 실행하면 10,000 개의 요소가있는 쿼리와 결과를 얻을 수있는 방식으로 쿼리를 사용할 수 있습니다. 18 초가 아닌 쿼리를 사용할 수 있습니다.

다른 청크 크기는 쿼리 및 데이터베이스 연결 대기 시간에 따라 더 잘 작동합니다. 특정 쿼리의 경우, 즉 전달 된 시퀀스에 결과가 있거나 Enumerable. 중첩 조건에서 사용되는 경우에 해당하는 요소를받을 수 있습니다.

다음은 코드 스 니펫입니다 (입력을 청크로 분할하는 데 사용 된 코드가 너무 복잡해 보이면 죄송합니다. 동일한 작업을 수행하는 더 간단한 방법이 시퀀스에 대한 스트리밍을 유지하는 패턴을 처리하고 있습니다. 아마도 그 부분을 과장했습니다 :)) :

용법 :

var list = context.GetMainItems(ids).ToList();

온라인 또는 저장소에 대한 방법 :

public partial class ContainsTestEntities
{
    public IEnumerable<Main> GetMainItems(IEnumerable<int> ids, int chunkSize = 100)
    {
        foreach (var chunk in ids.Chunk(chunkSize))
        {
            var q = this.MainItems.Where(a => chunk.Contains(a.Id));
            foreach (var item in q)
            {
                yield return item;
            }
        }
    }
}

열거 가능한 시퀀스를 분할하기위한 확장 메소드 :

public static class EnumerableSlicing
{

    private class Status
    {
        public bool EndOfSequence;
    }

    private static IEnumerable<T> TakeOnEnumerator<T>(IEnumerator<T> enumerator, int count, 
        Status status)
    {
        while (--count > 0 && (enumerator.MoveNext() || !(status.EndOfSequence = true)))
        {
            yield return enumerator.Current;
        }
    }

    public static IEnumerable<IEnumerable<T>> Chunk<T>(this IEnumerable<T> items, int chunkSize)
    {
        if (chunkSize < 1)
        {
            throw new ArgumentException("Chunks should not be smaller than 1 element");
        }
        var status = new Status { EndOfSequence = false };
        using (var enumerator = items.GetEnumerator())
        {
            while (!status.EndOfSequence)
            {
                yield return TakeOnEnumerator(enumerator, chunkSize, status);
            }
        }
    }
}

도움이 되셨기를 바랍니다!

---

방해가되는 성능 문제를 발견 한 경우 한 경우 문제를 해결하는 데 오랜 시간을 소비하지 않습니다. 성공하지 않을 가능성이 높고 MS와 직접 통신해야하기 때문입니다 (프리미엄 지원이있는 경우). 나이.

성능 문제가있는 경우 해결 방법 및 해결 방법을 사용하고 EF는 직접 SQL을 의미합니다. 나쁜 것은 없습니다. EF를 사용하는 것이 더 이상 SQL을 사용하지 않는 것은 거짓말이라는 글로벌 아이디어입니다. SQL Server 2008 R2가 있으므로 :

• ID를 전달하기 위해 테이블 ​​값 매개 변수를 허용하는 저장 프로 시저 만들기
• 저장 프로 시저가 여러 결과 집합을 반환하도록 최적 Include의 방식으로 논리 를 에뮬레이션합니다.
• 복잡한 쿼리 작성이 필요한 경우 저장 프로 시저 내에서 동적 SQL을 사용하십시오.
• 사용 SqlDataReader결과를 생성하고
• 연결에 연결하고 EF에서로드 된 것처럼 작업

성능이 중요하다면 더 나은 솔루션을 찾지 못할 것입니다. 현재 버전은 테이블 값 일련 변수 또는 여러 결과 집합을 지원하지 않습니다. 프로시 저는 EF에서 매핑 및 수 없습니다.

---

중간 테이블을 추가하고 포함하고있는 절을 포함하는 LINQ 쿼리에서 해당 테이블에 조인하여 EF 포함 문제를 해결합니다. 이 접근 방식으로 놀라운 결과를 얻을 수 있습니다. 큰 EF 모델이 EF 쿼리를 미리 수행 할 때 "Contains"가 허용되지 않습니다. "Contains"절을 사용하는 쿼리에 대해 성능이 매우 높습니다.

개요 :

• SQL 서버에서 테이블을 생성 -를 들어 예 HelperForContainsOfIntType와 HelperID의 Guid데이터-type ReferenceID의 int데이터 유형 컬럼. 필요에 따라 데이터 유형이 다른 ReferenceID를 사용하여 다른 테이블을 만듭니다.

• HelperForContainsOfIntTypeEF 모델에서 Entity / EntitySet 및 기타 기존 테이블을 만듭니다 . 필요에 따라 다른 데이터 유형에 대해 다른 Entity / EntitySet을 만듭니다.

• 의 입력을 받아 .NET 코드를 IEnumerable<int>반환하는 .NET 코드에서 도우미 메서드를 만듭니다 Guid. 이 방법은 새롭게 생성 Guid및 삽입의 값 IEnumerable<int>에을 HelperForContainsOfIntType생성 Guid합니다. 다음으로, 메서드는 새로 생성 한 Guid호출자에게 반환 합니다. HelperForContainsOfIntType테이블에 삽입 된 값 목록을 입력하고 삽입을 수행하는 저장 프로 시저를 만듭니다. SQL Server 2008 (ADO.NET)의 테이블 반환 변수를 참조하십시오 . 서로 다른 데이터 유형에 대해 서로 다른 도우미를 만들거나 다른 데이터 유형을 처리하는 일반 도우미 메서드를 만듭니다.

• EF 준비된 쿼리를 만듭니다.

  static Func<MyEntities, Guid, IEnumerable<Customer>> _selectCustomers =
      CompiledQuery.Compile(
          (MyEntities db, Guid containsHelperID) =>
              from cust in db.Customers
              join x in db.HelperForContainsOfIntType on cust.CustomerID equals x.ReferenceID where x.HelperID == containsHelperID
              select cust 
      );
  

• Contains절 에서 사용할 값으로 도우미 메서드를 호출하고 Guid쿼리에서 사용할 수 있습니다. 예를 들면 :

  var containsHelperID = dbHelper.InsertIntoHelperForContainsOfIntType(new int[] { 1, 2, 3 });
  var result = _selectCustomers(_dbContext, containsHelperID).ToList();
  

---

내 원래 답변 편집-엔터티의대로에 따라 가능한 해결 방법이 있습니다. EF가 엔터티를 채우기 위해 생성하는 SQL을 알고있는 경우 DbContext.Database.SqlQuery를 사용하여 직접 사용할 수 있습니다 . EF 4에서는 ObjectContext.ExecuteStoreQuery를 사용할 수 있다고 생각 하지만 시도하지 않습니다.

예를 들어 아래의 원래 답변의 코드를 사용하여 사용하여 SQL 문을 생성 StringBuilder하면 다음을 수행 할 수 있습니다.

var rows = db.Database.SqlQuery<Main>(sql).ToArray();

총 시간은 약 26 초에서 0.5 초로 제작되었습니다.

나는 그것이 추악 말하는 첫 번째 사람이 될, 더 나은 해결책이 제시되기를 바랍니다.

최신 정보

조금 더 생각한 끝에 조인을 사용하여 결과를 필터링하면 EF가 긴 ID 목록을 필요가 있습니다. 이 동시 쿼리 수에 따라 복잡 할 수있는 사용자 ID 또는 세션 ID를 사용하여 격리 할 수 ​​있습니다.

이를 테스트하기 위해, 내가 만든 Target같은 스키마와 테이블을 Main. 그런 다음 a StringBuilder를 사용 INSERT하여 Target테이블을 1,000 개 단위 로 채우는 명령 을 만들었습니다 INSERT. SQL 문을 직접 실행하는 것이 EF (약 0.3 초 ​​대 2.5 초)를 거치는 것보다 훨씬 빠르며 테이블 스키마가 변경되지 않는 것이 괜찮을 생각합니다.

마지막으로를 사용하여 선택하면 join0.5 초 내에서 실행됩니다.

ExecuteStoreCommand("DELETE Target");

var ids = Main.Select(a => a.Id).ToArray();
var sb = new StringBuilder();

for (int i = 0; i < 10; i++)
{
    sb.Append("INSERT INTO Target(Id) VALUES (");
    for (int j = 1; j <= 1000; j++)
    {
        if (j > 1)
        {
            sb.Append(",(");
        }
        sb.Append(i * 1000 + j);
        sb.Append(")");
    }
    ExecuteStoreCommand(sb.ToString());
    sb.Clear();
}

var rows = (from m in Main
            join t in Target on m.Id equals t.Id
            select m).ToArray();

rows.Length.Dump();

그리고 조인을 위해 EF에서 생성 한 SQL :

SELECT 
[Extent1].[Id] AS [Id]
FROM  [dbo].[Main] AS [Extent1]
INNER JOIN [dbo].[Target] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]

---

(원래 답변)

이것은 대답이 아니지만 몇 가지 추가 정보를 공유하고 싶었고 의견에 맞추기에는 너무 깁니다. 결과를 재현 할 수 있었고 추가 할 몇 가지 다른 사항이 있습니다.

SQL 프로파일 러는 첫 번째 쿼리 ( Main.Select)와 두 번째 쿼리 실행 사이에 지연이 있음을 보여 Main.Where주므로 문제가 해당 크기 (48,980 바이트)의 쿼리를 생성하고 전송하는 데 있다고 의심했습니다.

그러나 T-SQL에서 같은 SQL 문을 구축 동적으로 일초보다 적게 소요되며 복용 ids하여에서 Main.Select동일한 SQL 문을 작성하고 사용을 실행, 문 SqlCommand콘솔에 내용을 작성하는 시간을 포함하여 사용자들은 0.112 초 갔고, .

이 시점에서 EF는 ids쿼리를 작성할 때 10,000 개 각각에 대해 분석 / 처리를 수행하고 있다고 생각 합니다. 확실한 대답과 해결책을 제공 할 수 있기를 바랍니다.

SSMS 및 LINQPad에서 시도한 코드는 다음과 같습니다 (너무 가혹하게 비판하지 마십시오. 퇴근하려고 서두르고 있습니다).

declare @sql nvarchar(max)

set @sql = 'SELECT 
[Extent1].[Id] AS [Id]
FROM [dbo].[Main] AS [Extent1]
WHERE [Extent1].[Id] IN ('

declare @count int = 0
while @count < 10000
begin
    if @count > 0 set @sql = @sql + ','
    set @count = @count + 1
    set @sql = @sql + cast(@count as nvarchar)
end
set @sql = @sql + ')'

exec(@sql)

---

var ids = Mains.Select(a => a.Id).ToArray();

var sb = new StringBuilder();
sb.Append("SELECT [Extent1].[Id] AS [Id] FROM [dbo].[Main] AS [Extent1] WHERE [Extent1].[Id] IN (");
for(int i = 0; i < ids.Length; i++)
{
    if (i > 0) 
        sb.Append(",");     
    sb.Append(ids[i].ToString());
}
sb.Append(")");

using (SqlConnection connection = new SqlConnection("server = localhost;database = Test;integrated security = true"))
using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = sb.ToString();
    connection.Open();
    using(SqlDataReader reader = command.ExecuteReader())
    {
        while(reader.Read())
        {
            Console.WriteLine(reader.GetInt32(0));
        }
    }
}

---

Entity Framework에 익숙하지 않지만 다음을 수행하면 성능이 더 좋습니까?

대신 :

var ids = Main.Select(a => a.Id).ToArray();
var rows = Main.Where (a => ids.Contains(a.Id)).ToArray();

이것에 대해 (ID가 int라고 가정) :

var ids = new HashSet<int>(Main.Select(a => a.Id));
var rows = Main.Where (a => ids.Contains(a.Id)).ToArray();

---

Entity Framework 6 Alpha 2에서 수정되었습니다. http://entityframework.codeplex.com/SourceControl/changeset/a7b70f69e551

http://blogs.msdn.com/b/adonet/archive/2012/12/10/ef6-alpha-2-available-on-nuget.aspx

---

포함에 대한 캐시 가능한 대안?

이것은 나를 물었으므로 Entity Framework 기능 제안 링크에 두 펜스를 추가했습니다.

문제는 분명히 SQL을 생성 할 때입니다. 쿼리 생성은 4 초 였지만 실행은 0.1 초였습니다.

동적 LINQ 및 OR를 사용할 때 SQL 생성에 시간이 오래 걸리지 만 캐시 될 수있는 무언가가 생성 된다는 것을 알았습니다 . 그래서 다시 실행할 때 0.2 초로 줄었습니다.

SQL in은 여전히 ​​생성되었습니다.

초기 히트를 감당할 수 있다면 고려해야 할 다른 사항이 있습니다. 배열 수는 많이 변경되지 않으며 쿼리를 많이 실행합니다. (LINQ Pad에서 테스트 됨)

---

문제는 Entity Framework의 SQL 생성에 있습니다. 매개 변수 중 하나가 목록이면 쿼리를 캐시 할 수 없습니다.

EF가 쿼리를 캐시하도록하려면 목록을 문자열로 변환하고 문자열에 .Contains를 수행 할 수 있습니다.

예를 들어이 코드는 EF가 쿼리를 캐시 할 수 있으므로 훨씬 빠르게 실행됩니다.

var ids = Main.Select(a => a.Id).ToArray();
var idsString = "|" + String.Join("|", ids) + "|";
var rows = Main
.Where (a => idsString.Contains("|" + a.Id + "|"))
.ToArray();

이 쿼리가 생성되면 In 대신 Like를 사용하여 생성 될 가능성이 높으므로 C # 속도가 빨라지지만 잠재적으로 SQL 속도가 느려질 수 있습니다. 제 경우에는 SQL 실행에서 성능 저하가 발견되지 않았고 C #이 훨씬 빠르게 실행되었습니다.

참고 URL : https://stackoverflow.com/questions/7897630/why-does-the-contains-operator-degrade-entity-frameworks-performance-so-drama