Dapper를 사용하여 IN 절에서 2100 개가 넘는 값을 사용하려면 어떻게해야합니까?

c# dapper sql-server

문제

'IN'절의 매개 변수에 대한 SQL 제한을 피하기 위해 Dapper를 사용하여 임시 테이블에 삽입 할 ID가 포함 된 목록이 있습니다.

그래서 현재 내 코드는 다음과 같습니다.

public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
    using (var db = new SqlConnection(this.connectionString))
    {
        return db.Query<int>(
            @"SELECT a.animalID        
            FROM
            dbo.animalTypes [at]
            INNER JOIN animals [a] on a.animalTypeId = at.animalTypeId
            INNER JOIN edibleAnimals e on e.animalID = a.animalID
            WHERE
            at.animalId in @animalIds", new { animalIds }).ToList();
    }
}

내가 해결해야 할 문제는 animalIds 목록에 2100 개 이상의 ID가있는 경우 "들어오는 요청의 매개 변수가 너무 많습니다. 서버에서 최대 2100 개의 매개 변수를 지원합니다"라는 SQL 오류가 발생합니다.

이제 메서드에 전달 된 animalIds로 채워진 임시 테이블을 만들고 싶습니다. 그러면 임시 테이블의 동물 테이블에 가입하고 거대한 "IN"절을 사용하지 않아도됩니다.

구문의 다양한 조합을 시도했지만 어디에도 없습니다. 이것은 내가 지금있는 곳입니다.

public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
    using (var db = new SqlConnection(this.connectionString))
    {
        db.Execute(@"SELECT INTO #tempAnmialIds @animalIds");

        return db.Query<int>(
            @"SELECT a.animalID        
            FROM
            dbo.animalTypes [at]
            INNER JOIN animals [a] on a.animalTypeId = at.animalTypeId
            INNER JOIN edibleAnimals e on e.animalID = a.animalID
            INNER JOIN #tempAnmialIds tmp on tmp.animalID = a.animalID).ToList();
    }
}

SELECT INTO를 ID 목록으로 작업 할 수 없습니다. 내가 잘못된 길로 가고 있을지도 모르지만 아마 "IN"절의 한계를 피하는 더 좋은 방법이있을 것입니다.

내가 1000의 블록으로 animalIDs의 들어오는 목록을 나눌 수있는 백업 솔루션을 가지고 있지만 큰 "IN"절 고통 히트 및 임시 테이블에 합류하는 것이 더 효율적일 것입니다 읽었습니다 그리고 그것은 또한 내가 의미하는 1,000 개의 블록으로 ID를 배치하려면 별도의 '분할'코드가 필요하지 않습니다.

수락 된 답변

좋아, 여기에 원하는 버전이있다. SP / TVP를 사용하여 만든 첫 번째 대답은 다른 개념을 사용하므로 별도의 대답으로 추가하고 있습니다.

public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
  using (var db = new SqlConnection(this.connectionString))
  {
    // This Open() call is vital! If you don't open the connection, Dapper will
    // open/close it automagically, which means that you'll loose the created
    // temp table directly after the statement completes.
    db.Open();

    // This temp table is created having a primary key. So make sure you don't pass
    // any duplicate IDs
    db.Execute("CREATE TABLE #tempAnimalIds(animalId int not null primary key);");
    while (animalIds.Any())
    {
      // Build the statements to insert the Ids. For this, we need to split animalIDs
      // into chunks of 1000, as this flavour of INSERT INTO is limited to 1000 values
      // at a time.
      var ids2Insert = animalIds.Take(1000);
      animalIds = animalIds.Skip(1000).ToList();

      StringBuilder stmt = new StringBuilder("INSERT INTO #tempAnimalIds VALUES (");
      stmt.Append(string.Join("),(", ids2Insert));
      stmt.Append(");");

      db.Execute(stmt.ToString());
    }

    return db.Query<int>(@"SELECT animalID FROM #tempAnimalIds").ToList();
  }
}

테스트하려면 :

var ids = LoadAnimalTypeIdsFromAnimalIds(Enumerable.Range(1, 2500).ToList());

선택 진술을 원래대로 수정해야합니다. 내 환경에서 모든 테이블을 보유하고 있지 않기 때문에 필자는 임시 테이블에서 선택한 방식대로 작동하는지 확인했습니다.

함정, 코멘트를 참조하십시오 :

  • 처음에 연결을 엽니 다. 그렇지 않으면 테이블 작성 직후에 dapper가 자동으로 연결을 닫은 후에 임시 테이블이 사라집니다.
  • INSERT INTO 은 한 번에 1000 개의 값으로 제한되므로 전달 된 ID를 그에 따라 청크로 분할해야합니다.
  • 임시 테이블의 기본 키가 허용하지 않으므로 중복 키를 전달하지 마십시오.

편집하다

Dapper는이 작업을 할 수있는 세트 기반 작업을 지원합니다.

public IList<int> LoadAnimalTypeIdsFromAnimalIdsV2(IList<int> animalIds)
{
  // This creates an IEnumerable of an anonymous type containing an Id property. This seems
  // to be necessary to be able to grab the Id by it's name via Dapper.
  var namedIDs = animalIds.Select(i => new {Id = i});
  using (var db = new SqlConnection(this.connectionString))
  {
    // This is vital! If you don't open the connection, Dapper will open/close it
    // automagically, which means that you'll loose the created temp table directly
    // after the statement completes.
    db.Open();

    // This temp table is created having a primary key. So make sure you don't pass
    // any duplicate IDs
    db.Execute("CREATE TABLE #tempAnimalIds(animalId int not null primary key);");

    // Using one of Dapper's convenient features, the INSERT becomes:
    db.Execute("INSERT INTO #tempAnimalIds VALUES(@Id);", namedIDs);

    return db.Query<int>(@"SELECT animalID FROM #tempAnimalIds").ToList();
  }
}

나는 이것이 이전 버전 (즉, 각각 1000, 1000, 500 값을 갖는 3 개의 인서트 대신 2500 개의 단일 인서트)에 비해 얼마나 잘 수행되는지 모른다. 그러나 의사는 비동기, MARS 및 파이프 라이닝과 함께 사용하면 성능이 향상 될 것이라고 제안합니다.


인기 답변

귀하의 예제에서 볼 수없는 것은 animalIds 목록이 실제로 #tempAnimalIDs 테이블에 삽입 될 쿼리에 전달되는 방법입니다.

테이블 값 매개 변수가있는 저장 프로 시저를 사용하여 임시 테이블을 사용하지 않고이를 수행하는 방법이 있습니다.

SQL :

CREATE TYPE [dbo].[udtKeys] AS TABLE([i] [int] NOT NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[myProc](@data as dbo.udtKeys readonly)AS
BEGIN
    select i from @data;
END
GO

이렇게하면 udtKeys 라는 사용자 정의 테이블 형식이 만들어 udtKeys 여기에는 i 라는 하나의 int 열과 해당 형식의 매개 변수가 필요한 저장 프로 시저가 들어 있습니다. proc은 전달한 ID를 선택하는 대신 다른 작업을 수행하지만 다른 테이블을 조인 할 수는 있습니다. 구문과 관련된 힌트는 여기를 참조하십시오 .

기음#:

var dataTable = new DataTable();
dataTable.Columns.Add("i", typeof(int));
foreach (var animalId in animalIds)
    dataTable.Rows.Add(animalId);
using(SqlConnection conn = new SqlConnection("connectionString goes here"))
{
    var r=conn.Query("myProc", new {data=dataTable},commandType: CommandType.StoredProcedure);
    // r contains your results
}

프로 시저 내의 매개 변수는 DataTable을 전달하여 채워지며 DataTable의 구조는 사용자가 만든 테이블 형식 중 하나와 일치해야합니다.

2100 개 이상의 값을 실제로 전달해야하는 경우 성능을 높이기 위해 테이블 ​​유형의 색인을 생성하는 것이 좋습니다. 다음과 같이 중복 키를 전달하지 않으면 실제로 기본 키를 제공 할 수 있습니다.

CREATE TYPE [dbo].[udtKeys] AS TABLE(
    [i] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
        [i] ASC
    )WITH (IGNORE_DUP_KEY = OFF)
)
GO

다음과 같이 실행하는 데이터베이스 사용자에게 유형에 대한 실행 권한을 지정해야 할 수도 있습니다.

GRANT EXEC ON TYPE::[dbo].[udtKeys] TO [User]
GO

여기여기도 참조 하십시오 .



아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow