외래 키 관계와 사전 처리를 통해 대량의 삽입 작업을 수행하는 가장 빠른 방법은 무엇입니까?

bulkinsert foreign-key-relationship sqlbulkcopy sql-server sql-server-2008-r2

문제

커다란 (수십만 줄) tsv 파일을 여러 관련 SQL Server 2008 R2 테이블로 정기적으로 가져와야합니다.

입력 파일은 다음과 같이 보입니다 (실제로는 더 복잡하고 데이터는 다른 성격을 갖지만 여기에있는 것은 유사합니다).

January_1_Lunch.tsv
+-------+----------+-------------+---------+
| Diner | Beverage | Food        | Dessert |
+-------+----------+-------------+---------+
| Nancy | coffee   | salad_steak | pie     |
| Joe   | milk     | soup_steak  | cake    |
| Pat   | coffee   | soup_tofu   | pie     |
+-------+----------+-------------+---------+

한 열에는 분할하기 위해 사전 처리가 필요한 문자 구분 목록 이 들어 있습니다.

스키마는 고도로 정규화되어 있습니다. 각 레코드는 다 대다 외래 키 관계를 가지고 있습니다. 여기 특이한 것도 ...

Meals
+----+-----------------+
| id |       name      |
+----+-----------------+
|  1 | January_1_Lunch |
+----+-----------------+

Beverages
+----+--------+
| id |  name  |
+----+--------+
|  1 | coffee |
|  2 | milk   |
+----+--------+

Food
+----+-------+
| id | name  |
+----+-------+
|  1 | salad |
|  2 | soup  |
|  3 | steak |
|  4 | tofu  |
+----+-------+

Desserts
+----+------+
| id | name |
+----+------+
|  1 | pie  |
|  2 | cake |
+----+------+

각 입력 열은 궁극적으로 별도의 테이블을 대상으로합니다.

이것은 불필요하게 복잡한 스키마처럼 보일 수 있습니다 - 입력과 일치하는 단일 테이블을 가지지 않는 이유는 무엇입니까? 그러나 식당에 들어 와서 음료수 나 디저트 만 주문할 수도 있습니다.이 경우 많은 행이있을 것입니다. 이 DB가 궁극적으로 수억 개의 레코드를 저장한다는 것을 고려하면 스토리지의 빈약 한 사용처럼 보입니다. 나는 또한 단지 음료, 단지 디저트 등에 대한 보고서를 생성 할 수 있기를 원하며, 나는 그것들이 별개의 테이블로 훨씬 더 잘 수행 될 것이라고 생각한다.

주문은 다음과 같은 관계 테이블에서 추적됩니다.

BeverageOrders
+--------+---------+------------+
| mealId | dinerId | beverageId |
+--------+---------+------------+
|      1 |       1 |          1 |
|      1 |       2 |          2 |
|      1 |       3 |          1 |
+--------+---------+------------+

FoodOrders
+--------+---------+--------+
| mealId | dinerId | foodId |
+--------+---------+--------+
|      1 |       1 |      1 |
|      1 |       1 |      3 |
|      1 |       2 |      2 |
|      1 |       2 |      3 |
|      1 |       3 |      2 |
|      1 |       3 |      4 |
+--------+---------+--------+

DessertOrders
+--------+---------+-----------+
| mealId | dinerId | dessertId |
+--------+---------+-----------+
|      1 |       1 |         1 |
|      1 |       2 |         2 |
|      1 |       3 |         1 |
+--------+---------+-----------+

입력에는 여러 레코드로 분리 된 불쾌한 작은 목록이 있었으므로 Food에 대한 레코드가 더 많음에 유의하십시오. 이것이 별도의 테이블을 갖는 데 도움이되는 또 다른 이유입니다.


그래서 문제는 파일에서 위의 스키마로 데이터를 가져 오는 가장 효율적인 방법은 무엇입니까?

고려한 접근 방식 :

  1. tsv 파일을 한 줄씩 파싱하고 삽입을 수행합니다. ORM을 사용하든 아니든 상관없이, 이것은 데이터베이스로의 많은 이동과 같으며 매우 느릴 것입니다.
  2. tsv 파일을 스키마에 해당하는 메모리의 데이터 구조 또는 디스크의 여러 파일로 구문 분석하십시오. 그런 다음 SqlBulkCopy를 사용하여 각각을 가져옵니다. 트랜잭션이 적지 만 많은 양의 데이터를 캐시하거나 디스크에 많은 쓰기 작업을 수행해야하기 때문에 인서트를 많이 수행하는 것보다 비용이 많이 드는 것처럼 보입니다.
  3. Per ID 관계가있는 데이터 테이블 두 개를 대량으로 삽입 하고 SQL Server 2008에서 많은 양의 데이터를 삽입 / 업데이트하는 최상의 방법 , tsv 파일을 준비 테이블로 가져온 다음 DB 함수를 사용하여 스키마에 병합 전처리. 이것은 최고의 옵션처럼 보이지만, 유효성 검사와 전처리가 C # 또는 다른 어떤 것보다 효율적으로 수행 될 수 있다고 생각합니다.

밖에 다른 가능성이 있습니까?

스키마가 아직 개발 중입니다. 따라서 스키마가 끝나면 수정할 수 있습니다.

수락 된 답변

Diner , Beverage , Food , Dessert , ID (ID, ID, 기본 키가 클러스터되지 않음 - 성능 문제) 테이블에 파일을 가져올 수 있습니다.

이 후 Dinner_ID , Beverage_ID , Dessert_ID 열을 추가하고 별도의 표에 따라 항목을 채 웁니다. 각 열을 그룹화하고 누락 된 데이터를 조회 테이블에 Beverages , Desserts , Meals 로 추가하는 것이 간단합니다. 기존 및 새로 추가 된 레코드의 ID로 가져온 테이블을 수정합니다.

Food 테이블을 사용하는 상황은 Foodcombine 하는 능력 때문에 더 복잡하지만 같은 트릭을 사용할 수 있습니다. 또한 조회 테이블에 데이터를 추가 할 수 있으며이 중 식품 조합을 추가 임시 테이블에 저장할 수 있습니다 ( 고유 한 ID로) 및 단일 접시에서 분리.

구문 분석이 완료되면 3 개의 임시 테이블이 생성됩니다.

  1. 가져온 모든 데이터 및 모든 텍스트 열에 대한 ID가 포함 된 표
  2. 독특한 음식 목록이있는 테이블 (ID 포함)
  3. 식품 조합 당 식품 ID가있는 테이블

위 테이블에서 파싱 된 값을 원하는대로 구조체에 삽입 할 수 있습니다.

이 경우에는 코드 측에서 DB에 단 하나의 삽입 (대량) 만 수행됩니다. 다른 모든 데이터 조작은 DB에서 수행됩니다.



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