SQL Server(C# 클라이언트)에서 많은 데이터를 대량으로 삽입하는 가장 빠른 방법은 무엇입니까?

SQL Server(C# 클라이언트)에서 많은 데이터를 대량으로 삽입하는 가장 빠른 방법은 무엇입니까?

C# 클라이언트가 SQL Server 2005 데이터베이스에 대량 데이터를 삽입하는 과정에서 성능 병목 현상이 발생했습니다. 이 과정을 가속화할 수 있는 방법을 찾고 있습니다.

이미 SqlClient를 사용하고 있습니다.SqlBulkCopy(TDS 기반)를 사용하여 유선 데이터 전송 속도를 높이는 것이 큰 도움이 되었지만, 여전히 더 많은 것을 찾고 있습니다.

다음과 같은 간단한 테이블이 있습니다.

 CREATE TABLE [BulkData](
 [ContainerId] [int] NOT NULL,
 [BinId] [smallint] NOT NULL,
 [Sequence] [smallint] NOT NULL,
 [ItemId] [int] NOT NULL,
 [Left] [smallint] NOT NULL,
 [Top] [smallint] NOT NULL,
 [Right] [smallint] NOT NULL,
 [Bottom] [smallint] NOT NULL,
 CONSTRAINT [PKBulkData] PRIMARY KEY CLUSTERED 
 (
  [ContainerIdId] ASC,
  [BinId] ASC,
  [Sequence] ASC
))

각 청크에서 ContainerId와 BinId가 일정하고 Sequence 값이 0-n이며 기본 키를 기준으로 값이 사전 정렬된 평균 300개의 행에 데이터를 삽입하고 있습니다.

%Disk 시간 성능 카운터는 100%에서 많은 시간을 소비하므로 디스크 IO가 주요 문제인 것은 분명하지만 원시 파일 복사본보다 몇 배 더 빠릅니다.

도움이 될까요?

1. 삽입하는 동안 기본 키를 놓고 나중에 다시 만들기
2. 삽입이 발생하는 테이블의 크기를 작게 유지하기 위해 스키마가 동일한 임시 테이블에 삽입하고 주기적으로 기본 테이블로 전송합니다.
3. 또 다른 건 없으세요?

제가 받은 답변을 바탕으로, 다음과 같이 약간 명확히 설명하겠습니다.

포트만:데이터를 모두 가져오면 순차적으로 데이터에 액세스해야 하기 때문에 클러스터된 인덱스를 사용합니다.나는 데이터를 가져올 때 인덱스가 특별히 필요하지 않습니다.제약 조건을 완전히 가져오기 위해 삭제하는 것과 반대로 삽입을 수행하는 동안 비클러스터된 PK 인덱스를 갖는 것에 대한 이점이 있습니까?

쇼펜:데이터는 다른 많은 컴퓨터에서 원격으로 생성되고 있습니다(현재 SQL 서버는 10개 정도만 처리할 수 있지만 더 추가할 수 있으면 좋겠습니다).로컬 컴퓨터에서 전체 프로세스를 실행하는 것은 실용적이지 않습니다. 출력을 생성하려면 50배의 입력 데이터를 처리해야 하기 때문입니다.

제이슨: 저는 가져오기 과정 중에 테이블에 대해 동시 쿼리를 수행하지 않습니다. 기본 키를 삭제하고 도움이 되는지 확인해 보겠습니다.

SQL Server에서 인덱스를 비활성화/활성화할 수 있는 방법은 다음과 같습니다.

--Disable Index ALTER INDEX [IX_Users_UserID] SalesDB.Users DISABLE
GO
--Enable Index ALTER INDEX [IX_Users_UserID] SalesDB.Users REBUILD

다음은 솔루션을 찾는 데 도움이 되는 몇 가지 리소스입니다.

일부 대량 적재 속도 비교

SqlBulkCopy를 사용하여 클라이언트의 데이터를 SQL Server로 신속하게 로드

대량 복사 성능 최적화

NOCHECK 및 TABLOCK 옵션을 확인하십시오.

테이블 힌트(트랜잭션-SQL)

INSERT(트랜잭션-SQL)

이미 SqlBulkCopy를 사용하고 있는데, 이는 좋은 시작입니다.

그러나 SQLBulkCopy 클래스를 사용한다고 해서 SQL이 대량 복사를 수행하는 것은 아닙니다.특히 SQL 서버가 효율적인 대량 삽입을 수행하려면 몇 가지 요구 사항을 충족해야 합니다.

자세한 내용:

• 대량 가져오기에서 최소 로깅을 위한 필수 구성 요소
• 대량 가져오기 성능 최적화

궁금해서 그러는데, 당신의 인덱스는 왜 그렇게 설정되어 있나요?ContainerId/BinId/Sequence가 비클러스터형 인덱스에 훨씬 적합한 것 같습니다.이 인덱스를 클러스터화하려는 특별한 이유가 있습니까?

이 인덱스를 비클러스터형으로 변경하면 크게 개선될 것으로 예상됩니다.그러면 두 가지 옵션이 제공됩니다.

1. 인덱스를 비클러스터된 인덱스로 변경하고 클러스터된 인덱스 없이 힙 테이블로 유지
2. 인덱스를 비클러스터형으로 변경한 다음 대리 키(예: "id")를 추가하여 ID, 기본 키 및 클러스터형 인덱스로 만듭니다.

어느 쪽이든 읽기 속도를 눈에 띄게 늦추지 않고 삽입 속도를 높일 수 있습니다.

이런 식으로 생각해 보세요. 지금은 SQL에 대량 삽입을 지시하고 있지만, 추가한 모든 테이블의 순서를 변경하도록 SQL에 요청하고 있습니다.비클러스터형 인덱스를 사용하면 레코드가 들어오는 순서에 따라 레코드를 추가한 다음 원하는 순서를 나타내는 별도의 인덱스를 작성할 수 있습니다.

트랜잭션을 사용해 보셨습니까?

설명한 바에 따르면 서버가 디스크에 100% 시간을 할애하는 경우 각 데이터 행을 원자 SQL 문장으로 전송하여 서버가 각 행을 강제로 커밋(디스크에 쓰기)하도록 하는 것으로 보입니다.

대신 트랜잭션을 사용한 경우, 서버는 트랜잭션이 끝날 때 한 번만 커밋합니다.

추가 도움말:서버에 데이터를 삽입하기 위해 어떤 방법을 사용하고 있습니까?데이터 어댑터를 사용하여 데이터 테이블을 업데이트하거나 문자열을 사용하여 각 문장을 실행합니까?

BCP - 설정하는 것은 번거롭지만 DB가 시작된 이래로 계속되어 왔으며 매우 빠릅니다.

데이터를 그 순서대로 삽입하지 않는 한 3부 인덱스는 속도를 늦출 것입니다.나중에 적용하면 속도도 느려지지만 두 번째 단계가 될 것입니다.

SQL의 복합 키는 항상 매우 느리며, 키가 클수록 느립니다.

저는 똑똑하지도 않고 Sql Client에 대한 경험도 많지 않습니다.SqlBulkCopy 방법이지만 여기 제 2센트의 가치가 있습니다.저는 그것이 당신과 다른 사람들에게 도움이 되기를 바랍니다(혹은 적어도 사람들이 저의 무지를 외치게 만들 것입니다).

데이터베이스 데이터 파일(mdf)이 트랜잭션 로그 파일(ldf)과 별도의 실제 Disk에 있지 않으면 원시 파일 복사 속도와 일치하지 않습니다.또한 보다 공정한 비교를 위해 클러스터된 인덱스도 별도의 실제 Disk에 있어야 합니다.

원시 복사본이 인덱싱을 위해 선택한 필드(열)의 정렬 순서를 기록하거나 유지하지 않습니다.

비클러스터된 ID 시드를 생성하고 기존 비클러스터된 인덱스를 클러스터된 인덱스로 변경하는 것에 대해 Portman의 의견에 동의합니다.

클라이언트에서 사용 중인 구성(데이터 어댑터, 데이터 세트, 데이터 테이블 등)에 대해 설명합니다.서버의 디스크 IO가 100%인 경우, 현재 서버가 처리할 수 있는 속도보다 빠른 것으로 보이기 때문에 클라이언트 구성을 분석하는 데 가장 적합한 시간이라고 생각하지 않습니다.

최소한의 로깅에 대한 포트만의 링크를 따르면, 저는 당신의 대량 복사본을 거래에서 둘러싸는 것이 큰 도움이 될 것이라고 생각하지 않지만, 제가 살면서 여러 번 틀렸습니다;)

지금 당장은 도움이 되지 않지만 현재 문제를 파악할 경우 다음 병목 현상(네트워크 처리량)에 도움이 될 수 있습니다. 특히 인터넷을 통해 문제가 해결되는 경우...

Chopen도 재미있는 질문을 했습니다.레코드 개수 청크 300개를 삽입하는 데 사용하기로 결정한 방법은 무엇입니까? SQL Server에는 기본 패킷 크기(4096바이트)가 있으며 레코드 크기를 도출하고 클라이언트와 서버 간에 전송되는 패킷을 효율적으로 사용하는 것이 좋습니다.모든 서버 통신에 대해 패킷 크기를 분명히 변경하는 서버 옵션과 반대로 클라이언트 코드에서 패킷 크기를 변경할 수 있습니다. 아마 좋은 생각은 아닐 것입니다.)예를 들어 레코드 크기로 인해 300개의 레코드 배치에 4500바이트가 필요한 경우 두 번째 패킷이 대부분 낭비되고 있는 상태에서 두 번째 패킷을 보냅니다.배치 레코드 수가 임의로 할당된 경우 간단한 계산을 수행하는 것이 합리적일 수 있습니다.

제가 알기로는 (데이터 유형 크기에 대해서도 기억하고 있음) 각 레코드에 대해 정확히 20바이트가 있습니다(int=4바이트 및 smallint=2바이트인 경우).300개의 레코드 카운트 배치를 사용하는 경우 300 x 20 = 6,000바이트를 전송하려고 합니다(그리고 연결에 대한 약간의 오버헤드 등).200개의 레코드 카운트 배치(200 x 20 = 4,000 + 오버헤드 공간) = 1 패킷으로 전송하는 것이 더 효율적일 수 있습니다.한편, 병목 현상은 여전히 서버의 디스크 IO로 나타납니다.

물리적 데이터 전송을 동일한 하드웨어/구성의 SqlBulkCopy와 비교하고 있다는 것을 알고 있습니다. 하지만 문제가 해결되지 않았다면 다음과 같이 설명하겠습니다.

이 게시물은 다소 오래되었기 때문에 더 이상 도움이 되지 않을 수도 있지만, 다음으로 디스크의 RAID 구성과 사용 중인 디스크의 속도를 묻겠습니다.데이터 파일에 RAID 5(이상적으로 1)가 있는 RAID 10을 사용하는 드라이브에 로그 파일을 저장해 보십시오.이를 통해 Disk의 여러 섹터로 스핀들 이동이 많이 감소하고 비생산적인 "이동" 상태 대신 읽기/쓰기 시간이 길어질 수 있습니다.데이터와 로그 파일을 이미 분리한 경우 데이터 파일과 다른 실제 Disk 드라이브에 인덱스가 있는지 여부(클러스터된 인덱스에서만 이 작업을 수행할 수 있음).이렇게 하면 데이터 삽입을 통해 로깅 정보를 동시에 업데이트할 수 있을 뿐만 아니라 인덱스 삽입(및 값비싼 인덱스 페이지 작업)도 동시에 수행할 수 있습니다.

SSIS 패키지를 사용하여 수행할 수 있을 것으로 생각됩니다.SQL 2000의 DTS 패키지와 유사합니다.일반 텍스트 CSV 파일, 기존 SQL 테이블, 그리고 여러 워크시트에 걸쳐 6자리 행이 있는 XLS 파일의 모든 것을 성공적으로 변환하는 데 사용했습니다.C#을 사용하여 데이터를 가져올 수 있는 형식(CSV, XLS 등)으로 변환한 다음 SQL 서버에서 예약된 SSIS 작업을 실행하여 데이터를 가져오도록 할 수 있습니다.

SSIS 패키지를 만드는 것은 매우 쉽습니다. SQL Server의 Enterprise Manager 도구("데이터 가져오기" 레이블)가 내장되어 있으며, 마법사 끝에 SSIS 패키지로 저장할 수 있는 옵션이 제공됩니다.Technet에도 많은 정보가 있습니다.

아직도 문제에 직면해 있습니까?이것도 한 번 해보세요.

• 데이터베이스 구성(메모리 및 프로세서)을 확인합니다.
• 대용량 데이터의 경우 최소 16GB의 메모리와 16GB의 프로세서를 사용하는 것이 좋습니다.

네, 당신의 아이디어가 도움이 될 것입니다.
로드 중에 읽기가 발생하지 않으면 옵션 1에 기대십시오.
처리 중에 대상 테이블을 쿼리하는 경우 옵션 2에 기대십시오.

@앤드류
질문.300개의 덩어리로 삽입합니다.당신이 삽입한 총 용량은 얼마입니까? SQL 서버는 300개의 일반적인 오래된 삽입을 매우 빠르게 처리할 수 있어야 합니다.

언급URL : https://stackoverflow.com/questions/24200/whats-the-fastest-way-to-bulk-insert-a-lot-of-data-in-sql-server-c-client