bcp / BULK INSERT 대 테이블 반환 매개 변수의 성능

bcp / BULK INSERT 대 테이블 반환 매개 변수의 성능

---

BULK INSERT스키마가 변경 되었기 때문에 SQL Server의 명령을 사용하여 다소 오래된 코드를 다시 작성 해야하는데, 대신 TVP를 사용하여 저장 프로 시저로 전환하는 것에 대해 생각해야한다는 생각이 들었지만 어떤 효과가 있는지 궁금합니다. 성능에 영향을 미칠 수 있습니다.

이 질문을하는 이유를 설명하는 데 도움이되는 몇 가지 배경 정보 :

• 데이터는 실제로 웹 서비스를 통해 들어옵니다. 웹 서비스는 데이터베이스 서버의 공유 폴더에 텍스트 파일을 작성하여 BULK INSERT. 이 프로세스는 원래 SQL Server 2000에서 구현되었으며 당시에 INSERT는 서버에서 수백 개의 명령문을 처리하는 것 외에 다른 대안이 없었습니다. 실제로는 원래 프로세스 였고 성능에 문제가있었습니다.

• 데이터는 영구 준비 테이블에 대량 삽입 된 다음 훨씬 더 큰 테이블에 병합됩니다 (그 후에는 준비 테이블에서 삭제됨).

• 삽입 할 데이터의 양은 "큰"이지만 "거대한"것은 아닙니다. 일반적으로 몇 백 행, 드물게 5-10k 행이 맨 위에있을 수 있습니다. 따라서 내 직감은 BULK INSERT로그되지 않은 작업이 큰 차이를 만들지 않는다는 것 입니다 (물론 확실하지 않으므로 질문).

• 삽입은 실제로 훨씬 더 큰 파이프 라인 배치 프로세스의 일부이며 연속적으로 여러 번 발생해야합니다. 따라서 성능 이 중요합니다.

를 BULK INSERTTVP 로 바꾸고 싶은 이유는 다음과 같습니다.

• NetBIOS를 통해 텍스트 파일을 작성하는 데는 이미 시간이 많이 걸리고 아키텍처 관점에서 보면 매우 끔찍합니다.

• 나는 스테이징 테이블이 제거 될 수 있다고 믿습니다. 주된 이유는 삽입 된 데이터를 삽입과 동시에 몇 가지 다른 업데이트에 사용해야하기 때문이며, 거의 비어있는 스테이징을 사용하는 것보다 대규모 프로덕션 테이블에서 업데이트를 시도하는 것이 훨씬 더 비쌉니다. 표. TVP으로, 매개 변수는 기본적 이다 내가 주 삽입 후 내가 전에 그것으로 원하는 모든 것을 / 할 수있는 스테이징 테이블.

• 중복 검사, 정리 코드 및 대량 삽입과 관련된 모든 오버 헤드를 거의 없앨 수 있습니다.

• 서버가 이러한 트랜잭션을 한 번에 가져 오는 경우 스테이징 테이블 또는 tempdb의 잠금 경합에 대해 걱정할 필요가 없습니다 (우리는이를 방지하려고하지만 발생 함).

나는 분명히 이것을 생산에 투입하기 전에 프로파일 할 것이지만, 그 시간을 보내기 전에 먼저 물어 보는 것이 좋은 생각이라고 생각했습니다. 누군가이 목적을 위해 TVP를 사용하는 것에 대해 엄격한 경고가 있는지 확인하십시오.

그래서-SQL Server 2008에 대해 충분히 아는 사람이 이것을 시도하거나 적어도 조사한 적이 있다면 평결은 무엇입니까? 꽤 자주 발생하는 수백에서 수천 개의 행을 삽입하는 경우 TVP가 겨자를 자르나요? 대량 삽입과 비교하여 성능에 큰 차이가 있습니까?

---

업데이트 : 이제 물음표가 92 % 감소했습니다!

(일명 : 테스트 결과)

최종 결과는 이제 36 단계 배포 프로세스처럼 느껴지는 프로덕션에 있습니다. 두 솔루션 모두 광범위하게 테스트되었습니다.

• 공유 폴더 코드를 추출하고 SqlBulkCopy클래스를 직접 사용합니다 .
• TVP를 사용하여 저장 프로 시저로 전환.

독자들이 정확히 무엇 을 테스트 했는지에 대한 아이디어 를 얻고이 데이터의 신뢰성에 대한 의구심을 풀 수 있도록이 가져 오기 프로세스가 실제로 수행하는 작업에 대한 자세한 설명 은 다음과 같습니다.

1. 일반적으로 약 20-50 개의 데이터 포인트 (때로는 수백 개까지 될 수 있지만) 인 시간 데이터 시퀀스로 시작합니다.

2. 대부분 데이터베이스와 무관 한 미친 처리를 모두 수행하십시오. 이 프로세스는 병렬화되어 있으므로 (1)의 시퀀스 중 약 8-10 개가 동시에 처리됩니다. 각 병렬 프로세스는 3 개의 추가 시퀀스를 생성합니다.

3. 3 개의 시퀀스와 원본 시퀀스를 모두 가져 와서 배치로 결합합니다.

4. 8 ~ 10 개의 모든 처리 작업의 배치를 하나의 큰 수퍼 배치로 결합하십시오.

5. BULK INSERT전략 (다음 단계 참조) 또는 TVP 전략 (8 단계로 건너 뛰기)을 사용하여 가져옵니다 .

6. SqlBulkCopy클래스를 사용하여 전체 수퍼 배치를 4 개의 영구 스테이징 테이블에 덤프합니다.

7. (a) 여러 JOIN조건을 포함하여 2 개의 테이블에서 여러 집계 단계를 수행 한 다음 (b) MERGE집계 된 데이터와 집계되지 않은 데이터를 모두 사용하여 6 개의 프로덕션 테이블에서 수행 하는 저장 프로 시저를 실행합니다 . (끝마친)

   또는

8. DataTable병합 할 데이터를 포함하는 4 개의 개체를 생성 합니다. 그중 3 개에는 ADO.NET TVP에서 제대로 지원하지 않는 CLR 유형이 포함되어 있으므로 문자열 표현으로 삽입해야하므로 성능이 약간 저하됩니다.

9. 기본적으로 (7)과 동일한 처리를 수행하지만 수신 된 테이블을 직접 사용하는 저장 프로 시저에 TVP를 공급합니다. (끝마친)

결과는 상당히 비슷했지만, TVP 접근 방식은 데이터가 1000 행을 조금 넘었을 때에도 평균적으로 더 나은 성과를 거두었습니다.

이 가져 오기 프로세스는 연속으로 수천 번 실행되므로 모든 병합을 완료하는 데 걸린 시간 (예, 시간)을 계산하여 평균 시간을 얻는 것이 매우 쉬웠습니다.

원래 평균 병합은 완료하는 데 거의 정확히 8 초가 걸렸습니다 (정상로드시). NetBIOS 클러지를 제거하고 전환 SqlBulkCopy하여 거의 정확히 7 초로 시간을 단축했습니다. TVP로 전환하면 배치 당 5.2 초로 시간이 더욱 단축되었습니다 . 이는 실행 시간이 몇 시간 단위로 측정되는 프로세스의 처리량 이 35 % 개선 된 것이므로 전혀 나쁘지 않습니다. 또한 SqlBulkCopy.

나는 실제로 진정한 개선이 이것보다 훨씬 더 많았다 고 상당히 확신합니다. 테스트 중에 최종 병합이 더 이상 중요한 경로가 아니라는 것이 분명해졌습니다. 대신 모든 데이터 처리를 수행하는 웹 서비스는 들어오는 요청 수에 따라 버클 링되기 시작했습니다. CPU와 데이터베이스 I / O 모두 실제로 최대 값이 초과되지 않았고 중요한 잠금 활동도 없었습니다. 어떤 경우에는 연속적인 병합 사이에 몇 초의 유휴 시간 간격이 나타났습니다. 약간의 간격이 있었지만을 사용할 때는 훨씬 더 작습니다 (0.5 초 정도) SqlBulkCopy. 그러나 나는 그것이 다른 날에 대한 이야기가 될 것이라고 생각합니다.

결론 : 테이블 값 매개 변수 BULK INSERT는 중간 크기의 데이터 세트에서 작동하는 복잡한 가져 오기 + 변환 프로세스의 작업 보다 실제로 더 잘 수행 됩니다.

---

프로 스테이징 테이블에있는 사람들의 우려를 덜어주기 위해 다른 요점을 추가하고 싶습니다. 어떤면에서이 전체 서비스는 하나의 거대한 스테이징 프로세스입니다. 프로세스의 모든 단계는 심하게 감사되므로 특정 병합이 실패한 이유를 확인하기 위해 스테이징 테이블이 필요 하지 않습니다 (실제로는 거의 발생하지 않음). 우리가해야 할 일은 서비스에 디버그 플래그를 설정하는 것뿐입니다. 그러면 디버거가 중단되거나 데이터베이스 대신 파일에 데이터가 덤프됩니다.

즉, 우리는 이미 프로세스에 대한 충분한 통찰력을 가지고 있으며 스테이징 테이블의 안전성이 필요하지 않습니다. 처음에 스테이징 테이블이있는 유일한 이유는 다른 방법으로 사용해야했던 모든 INSERT및 UPDATE문 에 대한 스 래싱을 방지하기위한 것입니다. 원래 프로세스에서 스테이징 데이터는 어쨌든 몇 분의 1 초 동안 만 스테이징 테이블에 있었으므로 유지 보수 / 유지 보수 측면에서 가치를 추가하지 않았습니다.

또한 우리가주의 하지 매일 교체 BULK INSERTTVPs와 작업을. 더 많은 양의 데이터를 처리하거나 데이터를 DB에 던지는 것 외에 특별한 작업을 수행 할 필요가없는 여러 작업은 여전히 SqlBulkCopy. 나는 TVP가 성능 만병 통치약이라고 제안하는 것이 아니라 SqlBulkCopy초기 스테이징과 최종 병합 사이의 여러 변환을 포함하는 특정 인스턴스에서 성공했다는 것 입니다.

그래서 거기에 있습니다. 포인트는 가장 관련성이 높은 링크를 찾기 위해 TToni로 이동하지만 다른 응답도 감사합니다. 다시 한 번 감사드립니다!

---

아직 TVP에 대한 경험이 없지만 MSDN의 BULK INSERT에 대한 멋진 성능 비교 차트가 있습니다 .

그들은 BULK INSERT가 시작 비용이 더 높지만 그 이후에는 더 빠르다고 말합니다. 원격 클라이언트 시나리오에서는 약 1000 개의 행에 선을 그립니다 ( "단순한"서버 로직의 경우). 그들의 설명으로 판단하면 TVP를 사용하는 것이 좋습니다. 성능 저하 (있는 경우)는 무시할 수있을 것이며 아키텍처상의 이점은 매우 좋아 보입니다.

편집 : 참고로 서버 로컬 파일을 피하고 SqlBulkCopy 개체를 사용하여 대량 복사를 계속 사용할 수 있습니다. DataTable을 채우고 SqlBulkCopy 인스턴스의 "WriteToServer"-Method에 공급하면됩니다. 사용하기 쉽고 매우 빠릅니다.

---

The chart mentioned with regards to the link provided in @TToni's answer needs to be taken in context. I am not sure how much actual research went into those recommendations (also note that the chart seems to only be available in the 2008 and 2008 R2 versions of that documentation).

On the other hand there is this whitepaper from the SQL Server Customer Advisory Team: Maximizing Throughput with TVP

I have been using TVPs since 2009 and have found, at least in my experience, that for anything other than simple insert into a destination table with no additional logic needs (which is rarely ever the case), then TVPs are typically the better option.

I tend to avoid staging tables as data validation should be done at the app layer. By using TVPs, that is easily accommodated and the TVP Table Variable in the stored procedure is, by its very nature, a localized staging table (hence no conflict with other processes running at the same time like you get when using a real table for staging).

Regarding the testing done in the Question, I think it could be shown to be even faster than what was originally found:

1. You should not be using a DataTable, unless your application has use for it outside of sending the values to the TVP. Using the IEnumerable<SqlDataRecord> interface is faster and uses less memory as you are not duplicating the collection in memory only to send it to the DB. I have this documented in the following places:
   • How can I insert 10 million records in the shortest time possible? (lots of extra info and links here as well)
   • Pass Dictionary<string,int> to Stored Procedure T-SQL
   • Streaming Data Into SQL Server 2008 From an Application (on SQLServerCentral.com ; free registration required)
2. TVPs are Table Variables and as such do not maintain statistics. Meaning, they report only having 1 row to the Query Optimizer. So, in your proc, either:
   • Use statement-level recompile on any queries using the TVP for anything other than a simple SELECT: OPTION (RECOMPILE)
   • Create a local temporary table (i.e. single #) and copy the contents of the TVP into the temp table

---

I think I'd still stick with a bulk insert approach. You may find that tempdb still gets hit using a TVP with a reasonable number of rows. This is my gut feeling, I can't say I've tested the performance of using TVP (I am interested in hearing others input too though)

You don't mention if you use .NET, but the approach that I've taken to optimise previous solutions was to do a bulk load of data using the SqlBulkCopy class - you don't need to write the data to a file first before loading, just give the SqlBulkCopy class (e.g.) a DataTable - that's the fastest way to insert data into the DB. 5-10K rows isn't much, I've used this for up to 750K rows. I suspect that in general, with a few hundred rows it wouldn't make a vast difference using a TVP. But scaling up would be limited IMHO.

Perhaps the new MERGE functionality in SQL 2008 would benefit you?

Also, if your existing staging table is a single table that is used for each instance of this process and you're worried about contention etc, have you considered creating a new "temporary" but physical staging table each time, then dropping it when it's finished with?

Note you can optimize the loading into this staging table, by populating it without any indexes. Then once populated, add any required indexes on at that point (FILLFACTOR=100 for optimal read performance, as at this point it will not be updated).

---

Staging tables are good! Really I wouldn't want to do it any other way. Why? Because data imports can change unexpectedly (And often in ways you can't foresee, like the time the columns were still called first name and last name but had the first name data in the last name column, for instance, to pick an example not at random.) Easy to research the problem with a staging table so you can see exactly what data was in the columns the import handled. Harder to find I think when you use an in memory table. I know a lot of people who do imports for a living as I do and all of them recommend using staging tables. I suspect there is a reason for this.

Further fixing a small schema change to a working process is easier and less time consuming than redesigning the process. If it is working and no one is willing to pay for hours to change it, then only fix what needs to be fixed due to the schema change. By changing the whole process, you introduce far more potential new bugs than by making a small change to an existing, tested working process.

And just how are you going to do away with all the data cleanup tasks? You may be doing them differently, but they still need to be done. Again, changing the process the way you describe is very risky.

Personally it sounds to me like you are just offended by using older techniques rather than getting the chance to play with new toys. You seem to have no real basis for wanting to change other than bulk insert is so 2000.

참고URL : https://stackoverflow.com/questions/2149897/performance-of-bcp-bulk-insert-vs-table-valued-parameters