[SQL] SQL 서버 (C #을 클라이언트)의 데이터를 많이 삽입 대량 가장 빠른 방법은 무엇입니까

SQL 서버 (C #을 클라이언트)의 데이터를 많이 삽입 대량 가장 빠른 방법은 무엇입니까

나는 내 C # 클라이언트가 SQL Server 2005 데이터베이스에 대량의 데이터를 삽입 일부 성능 병목 현상을 때리는 그리고 난 과정을 빠르게 할 수있는 방법을 찾고 있어요.

이미 많은 도움 와이어를 통해 데이터 전송 속도를 (TDS에 근거)에 SqlClient.SqlBulkCopy를 사용하고,하지만, 난 여전히 더 찾고 있어요.

나는 간단한 테이블이 그 다음과 같다 :

 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
))

I는 ContainerId 및 BinId 각 청크 상수와 시퀀스 값이다 평균 약 300 행 0 및 N 값이 미리 정렬 된 기본 키에 기반인지 청크 데이터를 삽입하고있다.

이 디스크 IO가 주요 문제가 분명하다 있도록 % 디스크 시간 성능 카운터는 100 %에 많은 시간을 소비하지만 내가 원시 파일 복사 아래의 몇 배를하는군요 속도가 빨라집니다.

그것은 어떤 경우에는 내가 도움이 있습니까 :

- 내가 입수했습니다 응답을 바탕으로, 내가 조금 명확히하자 :

포트먼 : 데이터를 모두 가져올 때 나는 순서대로 순차적으로 데이터에 액세스해야하므로 내가 클러스터 된 인덱스를 사용하고 있습니다. 나는 특히 데이터를 가져 오는 동안 인덱스가 될 필요가 없습니다. 수입 완전히 제약 조건을 삭제 반대로 삽입을하는 동안 클러스터되지 않은 PK 지수를 갖는 어떤 이점이 있습니까?

Chopeen : 데이터는 많은 다른 시스템에서 원격으로 생성되는 (내 SQL 서버는 현재 약 10 처리 할 수 ​​있습니다,하지만 난 더 추가 할 수 싶어요). 그것은 다음 많은 입력 데이터가 출력을 생성하기로 50 회를 처리해야하기 때문에 로컬 컴퓨터에서 전체 프로세스를 실행하는 것은 실용적이지입니다.

제이슨 : 나는 가져 오기 프로세스 중에 테이블에 대해 어떤 동시 쿼리를하고 있지 않다, 나는 차 키를 삭제하려고하고 도움이되는지 볼 수 있습니다.

해결법

1. ==============================

   1.다음은 / 해제 SQL Server의 인덱스를 사용하는 방법은 다음과 같습니다

   다음은 / 해제 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 서버로 클라이언트에서 데이터를로드

   최적화 대량 복사 성능

   확실히 NOCHECK 및 TABLOCK 옵션에 보면 :

   테이블 힌트 (Transact-SQL)를 참조하십시오

   INSERT (Transact-SQL)를 참조하십시오

2. ==============================

   2.당신은 이미 좋은 시작이다 SqlBulkCopy의를 사용하고 있습니다.

   당신은 이미 좋은 시작이다 SqlBulkCopy의를 사용하고 있습니다.

   그러나 단지 SqlBulkCopy의 클래스를 사용하여 반드시 SQL이 대량 복사를 수행하는 것을 의미하지 않습니다. 특히, 효율적인 대량 삽입을 수행하기 위해 SQL Server에 대한 충족해야합니다 몇 가지 요구 사항이 있습니다.

   추가 읽기 :

   호기심, 왜 지수는 그렇게 설정되어 있습니까? ContainerId / BinId / 시퀀스 훨씬 더 비 클러스터형 인덱스로 적합 것 같다. 클러스터되는이 인덱스를 원 특별한 이유가 있나요?

3. ==============================

   3.내 생각 엔 당신이 클러스터되지 않은 것으로 해당 인덱스를 변경 한 경우에는 극적인 향상을 볼 수 있다는 것입니다. 이 두 가지 옵션을 잎 :

   내 생각 엔 당신이 클러스터되지 않은 것으로 해당 인덱스를 변경 한 경우에는 극적인 향상을 볼 수 있다는 것입니다. 이 두 가지 옵션을 잎 :

   어느 하나가 눈에 띄게 귀하의 읽기 속도를 늦추고없이 삽입 속도를 높일 것입니다.

   지금, 당신이있는 거 텔링 SQL은 대량 삽입을 할 수 있지만, 당신은 SQL을 요구하고하면 전체 테이블에게 당신은 아무것도를 추가 할 때마다 테이블 순서를 - 그것에 대해 이런 식으로 생각하십시오. 클러스터되지 않은 인덱스로, 당신은 그들이 와서 어떤 순서로 레코드를 추가 한 다음 자신의 원하는 순서를 나타내는 별도의 인덱스를 만들 수 있습니다.

4. ==============================

   4.당신은 트랜잭션을 사용하여 시도 적이 있습니까?

   당신은 트랜잭션을 사용하여 시도 적이 있습니까?

   당신이 무엇을 설명에서, 디스크에 시간의 100 %를 투입 서버를 가지고, 당신이 때문에 모든 단일 행 (디스크에 대한 쓰기)을 저지하기 위해 서버를 강제 원자 SQL 문장에서 각 데이터 행을 보내는 것 같다.

   대신 트랜잭션을 사용하는 경우, 서버는 트랜잭션의 끝에서 한 번 커밋 것입니다.

   추가 도움이 필요한 경우 : 당신은 서버에 데이터를 삽입하기 위해 어떤 방법을 사용하고 있습니까? 데이터 어댑터를 사용하여 DataTable을 업데이트하거나 문자열을 사용하여 각 문장을 실행?

5. ==============================

   5.BCP는 - 그것을 설정하는 고통하지만 데시벨의 새벽부터 주변에있어 그것은 아주 아주 빠르다.

   BCP는 - 그것을 설정하는 고통하지만 데시벨의 새벽부터 주변에있어 그것은 아주 아주 빠르다.

   당신은 3 부분으로 지수는 순서대로 정말 천천히 일을 데이터를합니다 삽입하지 않는 한. 정말 천천히 일을 너무 나중에 것입니다 적용 만하는 것은 두 번째 단계에있을 것입니다.

   SQL에서 복합 키는 느린 더 큰, 매우 느린 항상 열쇠입니다.

6. ==============================

   6.정말 밝은 사람이 아니에요 그리고 나는 SqlClient.SqlBulkCopy 방법에 많은 경험을 가지고 있지만 여기에 무엇의 가치에 대한 제 2 센트입니다하지 않습니다. 나는 당신과 다른 사람 도움이되기를 바랍니다 (또는 적어도 원인의 사람들이 나의 무지를 호출).

   정말 밝은 사람이 아니에요 그리고 나는 SqlClient.SqlBulkCopy 방법에 많은 경험을 가지고 있지만 여기에 무엇의 가치에 대한 제 2 센트입니다하지 않습니다. 나는 당신과 다른 사람 도움이되기를 바랍니다 (또는 적어도 원인의 사람들이 나의 무지를 호출).

   (MDF) 데이터베이스의 데이터 파일이 트랜잭션 로그 파일 (LDF)에서 별도의 물리적 디스크에있는 경우가 아니면 당신은 원시 파일 복사 속도를 일치하지 않습니다. 또한, 클러스터 된 인덱스도 공정한 비교를위한 별도의 물리적 디스크에해야합니다.

   귀하의 원시 사본 로그인하거나 인덱싱 목적을 위해 선택 필드 (열)의 정렬 순서를 유지하지 않습니다.

   나는 비 클러스터형 정체성 씨앗을 만들고 클러스터 된 인덱스에 기존의 클러스터되지 않은 인덱스 변경에 대한 포트먼에 동의합니다.

   지금까지 ... 당신은 클라이언트에서 사용하고 (데이터 어댑터, 데이터 집합, 데이터 테이블 등) 구성 내용과. 서버의 디스크 IO가 100 % 인 경우, 나는 그들이 더 빠른 서버가 현재 처리 할 수있는 것보다으로 나타나는 시간이 가장 지출 분석 클라이언트 구조라고 생각하지 않습니다.

   당신이 최소한의 로깅에 대한 포트먼의 링크를 따라 경우에, 나는 어떤하지만 난 내 인생에서 잘못된 여러 번 봤는데 경우에 많은 도움이 될 거래에서 대량 복사를 둘러싼 생각하지 것이다)

   이것은 반드시 지금 당신을 도울 수 있지만하지 않습니다 당신이 당신의 현재 문제, 다음 병목 현상 (네트워크 처리량)이 다음 주석 힘의 도움을 알아낼 경우 - 그것은 인터넷을 통해입니다 특히 ...

   Chopeen 너무 재미있는 질문을했다. 어떻게 삽입하는 300 개 기록 카운트 덩어리를 사용하도록 결정 했습니까? SQL Server는 기본 패킷 크기를 (나는 그것이 4096 바이트 생각)이 있고 그것은 당신의 기록의 크기를 유도하고 클라이언트와 서버 사이에 전송 패킷을 효율적으로 사용하고 있는지 확인하기 위해 나에게 의미가있다. (분명히 모든 서버 통신을 변경 할 서버 옵션에 반대 참고, 당신은 당신의 클라이언트 코드에 패킷 크기를 변경할 수 있습니다 -. 아마 좋은 생각) 예를 들어, 300 기록 일괄하여 레코드 크기 결과가 4500을 필요로하는 경우 바이트, 당신은 두 번째 패킷이 대부분 낭비로이 개 패킷을 전송합니다. 배치 레코드 수 임의로 할당 된 경우에는 몇 가지 빠른 쉬운 수학을 위해 적합 할 수 있습니다.

   에서 당신이 각 레코드에 대해 정확히 20 바이트를 가지고 이야기 (및 데이터 유형의 크기에 대한 기억) 할 수 있는지 (만약 INT = 4 바이트 SMALLINT = 2 바이트). 당신이 300 개 레코드 수의 배치를 사용하는 경우, 당신은 300 × 20 = 6,000 바이트를 보내려고 (플러스 나는 연결 등 약간의 오버 헤드를 추측하고있어). 당신은 200 개 기록 카운트 일괄이를 보내는 것이 더 효율적일 수 있습니다 (200 × 20 = 오버 헤드 4,000 + 방) = 1 패킷. 그럼 다시, 당신의 병목 현상은 여전히 ​​서버의 디스크 IO 것으로 보인다.

   난 당신이 동일한 하드웨어 / 구성으로 SqlBulkCopy의에 원시 데이터 전송을 비교하지만, 여기에 도전 내 있었다면 나 또한 갈 것 어디있어 실현 :

   오히려 기존의하지만 난 다음 디스크의 RAID 구성이 무엇인지 물어 보곤 당신은 디스크의 어떤 속도를 사용하는이 게시물은 아마 더 이상 도움이되지 않습니다? 데이터 파일에 RAID 5 (이상적으로는 1)과 RAID 10을 사용하는 드라이브에 로그 파일을 넣어보십시오. 이 캔 도움이 대신 비생산적인 "이동"상태의 쓰기 / 디스크에 다른 분야에 스핀들 운동을 많이 줄이고 더 많은 시간을 독서에 발생합니다. 이미 데이터를 분리하고 로그 파일을 경우, 당신은 당신의 데이터 파일에서 다른 물리적 디스크 드라이브에 대한 색인을해야합니까 (당신은 단지 클러스터 된 인덱스가이 작업을 수행 할 수 있습니다). 그뿐만 아니라, 동시에 삽입 데이터와 로그 정보를 업데이트 할 수 있도록 할뿐만 동시 발생 인덱스 삽입 (및 고가의 인덱스 페이지 작업)을 할 수있다.

7. ==============================

   7.나는이 SSIS 패키지를 사용하여 수행 할 수처럼 소리라고 생각합니다. 그들은 SQL 2000의 DTS 패키지와 유사한입니다. 나는 성공적으로 기존의 SQL 테이블에서 일반 텍스트 CSV 파일에서 모든 것을 변환을 사용, 심지어 여러 워크 시트에 걸쳐 스팬 6 자리 행 XLS 파일에서했습니다. 당신이 가져올 수있는 형식 (CSV, XLS 등)로 데이터를 변환하는 C 번호를 사용할 수 있습니다, 다음 SQL 서버가 데이터를 가져올 예약 된 SSIS 작업을 실행해야합니다.

   나는이 SSIS 패키지를 사용하여 수행 할 수처럼 소리라고 생각합니다. 그들은 SQL 2000의 DTS 패키지와 유사한입니다. 나는 성공적으로 기존의 SQL 테이블에서 일반 텍스트 CSV 파일에서 모든 것을 변환을 사용, 심지어 여러 워크 시트에 걸쳐 스팬 6 자리 행 XLS 파일에서했습니다. 당신이 가져올 수있는 형식 (CSV, XLS 등)로 데이터를 변환하는 C 번호를 사용할 수 있습니다, 다음 SQL 서버가 데이터를 가져올 예약 된 SSIS 작업을 실행해야합니다.

   그것은 거기 내장으로 ( "데이터 가져 오기"내가 생각하는 표시) SQL 서버의 엔터프라이즈 관리자 도구 마법사가, 그리고 마법사의 끝에서 당신에게 SSIS 패키지로 저장하는 옵션을 제공, SSIS 패키지를 만드는 데 매우 간단합니다. 무리 더 윈스 테크넷에 대한 정보뿐만 아니라이있다.

8. ==============================

   8.예 아이디어가 도움이 될 것입니다. 더 당신의로드하는 동안이 일어나고 읽기 경우 옵션 1을 의지하지 않는다. 당신 대상 테이블은 당신의 처리 중에 조회되는 경우 옵션 2 린.

   예 아이디어가 도움이 될 것입니다. 더 당신의로드하는 동안이 일어나고 읽기 경우 옵션 1을 의지하지 않는다. 당신 대상 테이블은 당신의 처리 중에 조회되는 경우 옵션 2 린.

   앤드류 질문. (300)의 덩어리에 대한 여러분의 삽입은 무엇 총량하여 삽입입니까? SQL 서버는 매우 빠른 300 개 평범한 구식 삽입을 처리 할 수 ​​있어야합니다.

from https://stackoverflow.com/questions/24200/whats-the-fastest-way-to-bulk-insert-a-lot-of-data-in-sql-server-c-client by cc-by-sa and MIT license