C #에서 SQL Server 로의 대량 삽입 전략

bulkinsert c# sqlbulkcopy sql-server

문제

현재 프로젝트에서 고객은 복잡한 / 중첩 된 메시지 모음을 시스템에 보냅니다. 이 메시지의 빈도는 약입니다. 1000-2000 초 / 초.

이러한 복잡한 오브젝트에는 추가 할 트랜잭션 데이터와 마스터 데이터 (발견되지 않는 경우 추가됩니다)가 들어 있습니다. 그러나 마스터 데이터의 ID를 전달하는 대신 고객은 '이름'열을 전달합니다.

시스템은 이러한 이름에 대한 마스터 데이터가 존재하는지 확인합니다. 발견되면 데이터베이스의 ID를 사용하고 그렇지 않으면이 마스터 데이터를 먼저 만든 다음이 ID를 사용합니다.

마스터 데이터 ID가 확인되면 시스템은 마스터 데이터 ID를 사용하여 트랜잭션 데이터를 SQL Server 데이터베이스에 삽입합니다. 메시지 당 마스터 엔티티의 수는 약 15-20입니다.

다음은 우리가 채택 할 수있는 몇 가지 전략입니다.

  1. 먼저 C # 코드에서 마스터 ID를 확인하고 (찾을 수없는 경우 마스터 데이터 삽입) 이러한 ID를 C # 캐시에 저장할 수 있습니다. 모든 ID가 해결되면 SqlBulkCopy 클래스를 사용하여 트랜잭션 데이터를 대량 삽입 할 수 있습니다. 데이터베이스를 15 번 방문하여 다른 엔티티에 대한 ID를 가져온 다음 데이터베이스를 한 번 더 눌러 최종 데이터를 삽입 할 수 있습니다. 우리는이 모든 처리를 한 후에 같은 연결을 사용하여 그것을 닫을 수 있습니다.

  2. 마스터 데이터와 트랜잭션 데이터를 포함하는 메시지를 모두 단일 히트로 데이터베이스에 보내고 (다중 TVP 형태로) 저장된 프로 시저 내부에서 먼저 누락 된 마스터 데이터를 만든 다음 트랜잭션 데이터를 삽입 할 수 있습니다.

누구든지이 사용 사례에서 최상의 접근 방식을 제안 할 수 있습니까?

개인 정보 보호 문제로 인해 실제 개체 구조를 공유 할 수 없습니다. 그러나 여기에 우리의 비즈니스 객체에 매우 가까운 가상의 객체 구조가 있습니다.

이러한 메시지 중 하나에는 여러 공급 업체의 한 제품 (마스터 데이터) 및 가격 세부 정보 (거래 데이터)에 대한 정보가 포함됩니다.

마스터 데이터 (찾을 수없는 경우 추가해야 함)

제품 이름 : ABC, ProductCateory : XYZ, 제조업체 : XXX 및 기타 세부 정보 (속성 수는 15-20 개)

거래 데이터 (항상 추가됨)

공급 업체 이름 : A, ListPrice : XXX, 할인 : XXX

공급 업체 이름 : B, ListPrice : XXX, 할인 : XXX

공급 업체 이름 : C, ListPrice : XXX, 할인 : XXX

공급 업체 이름 : D, ListPrice : XXX, 할인 : XXX

마스터 데이터에 대한 대부분의 정보는 한 제품에 속한 메시지에 대해 동일하게 유지되며 (자주 변경되지는 않음) 트랜잭션 데이터는 항상 변동합니다. 따라서 시스템에서는 'XXX'제품이 시스템에 존재하는지 여부를 확인합니다. 그렇지 않으면이 제품과 함께 언급 된 '카테고리'가 존재하지 않는지 확인하십시오. 그렇지 않으면 범주에 대한 새 레코드를 삽입 한 다음 제품에 대한 새 레코드를 삽입합니다. 이는 제조업체 및 기타 마스터 데이터에 대해 수행됩니다.

여러 공급 업체가 여러 제품 (2000-5000)에 대한 데이터를 동시에 전송합니다.

따라서 우리는 1000 개의 공급 업체가 있다고 가정하고 각 공급 업체는 10-15 개의 서로 다른 제품에 대한 데이터를 전송합니다. 2-3 초마다 모든 공급 업체가 10 개 제품의 가격 업데이트를 보내줍니다. 그는 신제품에 대한 데이터를 보내기 시작할 수는 있지만 그다지 빈번하지는 않습니다.

인기 답변

# 2 아이디어 (예 : 여러 개의 TVP를 사용하여 한 번에 15-20 개의 모든 엔티티를 DB로 보내고 최대 2000 개의 메시지 집합으로 처리)을 사용하는 것이 가장 좋습니다.

앱 계층에서 마스터 데이터 조회를 캐싱하고 DB에 보내기 전에 번역하는 것은 좋지만 뭔가를 그리워합니다.

  1. 어쨌든 초기 목록을 얻으려면 DB를 치셔야합니다.
  2. 어쨌든 새 항목을 삽입하려면 DB를 눌러야합니다.
  3. 사전으로 값을 조회하여 ID로 바꾸려면 데이터베이스에서 수행하는 작업과 정확히 동일 합니다 (이러한 이름 - ID 조회마다 비 클러스터형 인덱스가 있다고 가정)
  4. 자주 쿼리 값은 자신의 datapages은 (메모리 캐시 인) 버퍼 풀에 캐시 할 것이다

왜 앱 레이어에서 이미 제공 되고 현재 DB 레이어에서 일어나는 일을 복제할까요?

  • 15 - 20 개의 엔티티는 최대 20k 개의 레코드를 가질 수 있습니다 (비 클러스터 인덱스는 두 개의 필드 만 필요하다는 점을 감안할 때 상대적으로 적은 수입니다. NameID 를 사용하면 여러 개의 행을 단일 데이터 페이지로 묶을 수 있음). 100 % 채우기 비율).
  • 모든 20k 항목이 "활성"또는 "현재"인 것은 아니므로 모든 항목을 캐싱 할 필요가 없습니다. 그래서 쉽게 사람이 조회되는 것으로 확인됩니다 현재, 그리고 데이터 페이지 (일부 비활성 항목을 포함 할 수 있지만이 더 큰 문제는) 버퍼 풀에 캐시 얻을 수있는 사람 수 없습니다 어떤 값.

따라서 자연스럽게 처리되는 것과 같이 값이 변경 될 수 있으므로 (예 : 특정 ID 대해 업데이트 된 Name 오래된 항목을 고갈 시키거나 키 만료 또는 재로드를 강요 할 필요가 없습니다.

그렇습니다. 메모리 내 캐싱은 멋진 기술이며 웹 사이트 속도를 크게 향상시킵니다. 그러나 비 데이터베이스 프로세스가 순수한 읽기 전용 용도로 동일한 데이터를 반복해서 요청할 때 유용합니다. 그러나이 특정 시나리오는 데이터가 병합되고 참조 값 목록이 자주 변경 될 수있는 시나리오입니다 (업데이트 된 항목으로 인한 것보다 새로운 항목으로 인해 발생 함).


그 모두가 말하기를, 옵션 # 2는 갈 길입니다. 나는이 기술을 여러 번 성공 시켰지만, 15 개의 TVP가 아니 었습니다. 이 특정 상황을 조정하기 위해 메소드에 대한 최적화 / 조정이 필요할 수도 있지만 잘 작동하는 것으로 밝혀졌습니다.

  • TVP를 통해 데이터를 수락하십시오. 나는 이것을 SqlBulkCopy 보다 선호하기 때문에 :
    • 그것은 쉽게 포함 된 저장 프로 시저를 만듭니다.
    • 그것은 매우 멋지게 애플 리케이션 코드에 DB에 컬렉션을 완전히 DataTable 컬렉션을 복제하는 데 복사하지 않고 DB에 컬렉션을 복제하는 스트림에 CPU와 메모리를 낭비하고있다. 이를 위해서는 IEnumerable<SqlDataRecord> 을 반환하고 컬렉션을 입력으로 받아들이고 yield return; 사용하는 각 컬렉션마다 메서드를 만들어야합니다 yield return; for 또는 foreach 루프에서 각 레코드를 보냅니다.
  • TVP는 통계에 적합하지 않으므로 조인하는 데는별로 좋지 않습니다 (그러나 검색어에 TOP (@RecordCount) 를 사용하여 완화 할 수 있지만).하지만 인구 통계를 작성하는 데만 사용되므로 걱정할 필요가 없습니다. 누락 된 값이있는 실제 테이블
  • 1 단계 : 각 엔티티에 누락 된 이름 삽입 각 엔티티의 [Name] 필드에 NonClustered Index가 있어야하고 ID가 Clustered Index라고 가정 할 때 그 값은 당연히 인덱스의 일부가되므로 [Name] 만이 포함 된 인덱스를 제공합니다. 다음 작업을 돕는 것 외에도. 또한이 클라이언트에 대한 사전 실행 (즉, 대략 엔티티 값이 동일)은 이러한 인덱스에 대한 데이터 페이지가 버퍼 풀 (예 : 메모리)에 캐시 된 상태로 남아 있음을 기억하십시오.

    ;WITH cte AS
    (
      SELECT DISTINCT tmp.[Name]
      FROM   @EntityNumeroUno tmp
    )
    INSERT INTO EntityNumeroUno ([Name])
      SELECT cte.[Name]
      FROM   cte
      WHERE  NOT EXISTS(
                     SELECT *
                     FROM   EntityNumeroUno tab
                     WHERE  tab.[Name] = cte.[Name]
                       )
    
  • 2 단계 : 간단한 INSERT...SELECT 모든 "메시지" INSERT...SELECT 조회 테이블 (즉, "엔터티")의 데이터 페이지가 1 단계로 인해 버퍼 풀에 이미 캐시되어있는 경우


마지막으로, 추측 / 가정 / 교육적 추측은 테스트를 대신 할 수 없음을 명심하십시오. 특정 상황에 가장 적합한 것이 무엇인지 확인하려면 몇 가지 방법을 시도해야합니다. 여기에 "이상적인"것으로 간주되는 것에 영향을 줄 수있는 추가 세부 정보가있을 수 있기 때문입니다.

메시지가 삽입형이라면 블라드의 생각이 더 빠를 것이라고 나는 말할 것이다. 여기에서 설명하는 방법은 좀 더 복잡하고 완전한 동기화 (업데이트 및 삭제)가 필요하고 추가 유효성 검사 및 관련 운영 데이터 (조회 값 아님) 생성이 필요한 상황에서 사용했습니다. SqlBulkCopy 사용하면 직선 삽입에서 더 빠를 수도 있지만 (2000 레코드 만 있으면 차이가있을 것입니다), 대상 테이블 (메시지 및 조회)에 직접로드되고 중간 / 준비 테이블에는로드되지 않는다고 가정합니다. 블라드의 생각은 SqlBulkCopy 가 대상 테이블에 직접 연결한다는 것입니다.) 그러나 위에서 언급했듯이 외부 캐시 (즉, 버퍼 풀이 아닌)를 사용하면 조회 값 업데이트 문제로 인해 오류가 발생하기 쉽습니다. 특히 외부 캐시를 사용하는 것이 약간 빠르다면 외부 캐시를 무효화하는 데 가치가있는 것보다 많은 코드가 필요할 수 있습니다. 추가 위험 / 유지 관리는 어떤 방법으로 전체적인 요구 사항을 고려해야하는지 고려해야합니다.


최신 정보

의견에 제공된 정보를 바탕으로 다음과 같은 사실을 알게되었습니다.

  • 여러 공급 업체가 있습니다.
  • 각 공급 업체가 제공하는 여러 제품이 있습니다.
  • 제품은 공급 업체에 고유하지 않습니다. 1 개 이상의 공급 업체가 판매하는 제품
  • 제품 속성은 단수입니다.
  • 가격 정보에는 여러 레코드가있을 수있는 속성이 있습니다.
  • 가격 정보는 INSERT 전용 (즉, 특정 시점 내역)
  • 고유 상품은 SKU (또는 유사한 입력란)에 의해 결정됩니다.
  • 생성 된 후에는 기존 SKU를 통해 전달되지만 다른 속성 (예 : 범주, 제조업체 등)이있는 제품은 동일한 제품 으로 간주됩니다. 차이점은 무시됩니다.

이 모든 것을 염두에두고 필자는 TVP를 여전히 추천 할 것이지만 접근 방식을 다시 생각하고 제품 중심이 아닌 공급 업체 중심적으로 만들 것입니다. 여기서는 공급 업체가 파일을 보낼 때마다 가정합니다. 따라서 파일을 가져 오면 파일을 가져 오십시오. 사전에 수행 할 유일한 조회는 공급 업체입니다. 다음은 기본 레이아웃입니다.

  1. 이 시점에서 VendorID가 있다고 가정하는 것이 합리적입니다. 시스템이 알 수없는 소스에서 파일을 가져 오는 이유는 무엇입니까?
  2. 배치로 가져올 수 있습니다.
  3. 다음과 같은 SendRows 메서드를 만듭니다.
    • 파일을 통해 진행할 수있는 FileStream 또는 다른 것을 허용합니다.
    • int BatchSize 와 같은 것을 받아 들인다 int BatchSize
    • IEnumerable<SqlDataRecord> 반환합니다.
    • TVP 구조와 일치하는 SqlDataRecord 를 만듭니다.
    • for BatchSize가 충족되거나 File에 더 이상 레코드가 없을 때까지 FileStream을 통해 반복됩니다.
    • 데이터에 대해 필요한 모든 검증을 수행한다.
    • 데이터를 SqlDataRecord 매핑
    • 통화 yield return;
  4. 파일 열기
  5. 파일에 데이터가있는 동안
    • 저장된 proc를 호출한다.
    • VendorID를 패스
    • TVP에 대한 SendRows(FileStream, BatchSize) 전달
  6. 파일 닫기
  7. 실험 대상 :
    • FileStream 주위의 루프 전에 SqlConnection을 열고 루프가 완료된 후 닫습니다.
    • SqlConnection 열기, 저장 프로 시저 실행 및 FileStream 루프 내부의 SqlConnection 닫기
  8. 다양한 BatchSize 값으로 실험 해보십시오. 100, 200, 500 등에서 시작하십시오.
  9. 저장된 proc은 새로운 제품 삽입을 처리합니다.

이 유형의 구조를 사용하면 사용되지 않는 제품 속성 (예 : SKU 만 기존 제품 검색에 사용됨)을 보내 게됩니다. 하지만 파일 크기와 관련하여 상한선이 없으므로 확장 성이 뛰어납니다. 공급 업체가 50 개의 제품을 보내면 잘됩니다. 그들이 50k 제품을 보내면 괜찮아. 그들이 4 백만 가지 제품 (내가 일한 시스템이고 그 제품의 특성 중 다른 제품 정보를 업데이트하는 일을 처리 했음)을 보내면 괜찮습니다. 앱 계층 또는 DB 계층에서 메모리가 증가하지 않아도 1 천만 개 제품을 처리 할 수 ​​있습니다. 전송 된 제품의 양에 따라 가져 오기 시간이 증가해야합니다.


업데이트 2
소스 데이터와 관련된 새로운 세부 정보 :

  • Azure EventHub에서 온다.
  • C # 개체 (파일 없음) 형태로 제공됩니다.
  • 제품 정보는 OP 시스템의 API를 통해 제공됩니다.
  • 단일 대기열에서 수집됩니다 (데이터베이스에서 데이터를 가져 오기만하면됩니다)

데이터 소스가 C # 개체 인 경우 가장 먼저 TVP를 사용합니다. 첫 번째 업데이트에서 설명한 메서드 (예 : IEnumerable<SqlDataRecord> 를 반환하는 메서드)를 통해 전달할 수 있습니다. 공급 업체 별 가격 / 제공 항목에 대해 하나 이상의 TVP를 보내지 만 단일 속성 속성에 대해서는 일반 입력 매개 변수를 보내십시오. 예 :

CREATE PROCEDURE dbo.ImportProduct
(
  @SKU             VARCHAR(50),
  @ProductName     NVARCHAR(100),
  @Manufacturer    NVARCHAR(100),
  @Category        NVARCHAR(300),
  @VendorPrices    dbo.VendorPrices READONLY,
  @DiscountCoupons dbo.DiscountCoupons READONLY
)
SET NOCOUNT ON;

-- Insert Product if it doesn't already exist
IF (NOT EXISTS(
         SELECT  *
         FROM    dbo.Products pr
         WHERE   pr.SKU = @SKU
              )
   )
BEGIN
  INSERT INTO dbo.Products (SKU, ProductName, Manufacturer, Category, ...)
  VALUES (@SKU, @ProductName, @Manufacturer, @Category, ...);
END;

...INSERT data from TVPs
-- might need OPTION (RECOMPILE) per each TVP query to ensure proper estimated rows


아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.