여러 값을 하나의 INSERT 대 여러 INSERT 문

나는 1000 INSERT 문을 사용 사이의 성능 비교를 실행하고 있습니다 :

INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('6f3f7257-a3d8-4a78-b2e1-c9b767cfe1c1', 'First 0', 'Last 0', 0)
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('32023304-2e55-4768-8e52-1ba589b82c8b', 'First 1', 'Last 1', 1)
...
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('f34d95a7-90b1-4558-be10-6ceacd53e4c4', 'First 999', 'Last 999', 999)

..versus 1000 개 값으로 단일 INSERT 문을 사용하여 :

INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
VALUES 
('db72b358-e9b5-4101-8d11-7d7ea3a0ae7d', 'First 0', 'Last 0', 0),
('6a4874ab-b6a3-4aa4-8ed4-a167ab21dd3d', 'First 1', 'Last 1', 1),
...
('9d7f2a58-7e57-4ed4-ba54-5e9e335fb56c', 'First 999', 'Last 999', 999)

내 큰 놀랍게도, 결과는 내가 생각했던 것과 반대입니다 :

시험은 측정에 사용되는 SQL Server 프로파일 러와 MSSQL Management Studio에서 직접 실행 (나는 놀라운 일 모든 DAL 레이어 왕복을 고려하더라도 더하는 SqlClient를 사용하여 C # 코드에서 실행 비슷한 결과를 가지고)

이 합리적인하거나 어떻게 든 설명 할 수 있습니까? 어떻게 10 배 (!) 더 성능이 아마도 빠른 방법의 결과는 올?

감사합니다.

편집 : 모두 연결 실행 계획 :

해결법

==============================
1.이 최소화되어야합니다에 대한 당신의 계획 쇼는 하나의 인서트 (아마도 자동 매개 변수화) 구문 분석 / 컴파일 시간이 너무 매개 변수화 절차를 사용하고 있습니다.

이 최소화되어야합니다에 대한 당신의 계획 쇼는 하나의 인서트 (아마도 자동 매개 변수화) 구문 분석 / 컴파일 시간이 너무 매개 변수화 절차를 사용하고 있습니다.

나는 그렇게 루프 (스크립트)을 설정하고 VALUES 절의 수를 조정하고 컴파일 시간을 기록하려고하지만 좀 더이 조사 거라고 생각했다.

그때 나는 절 당 평균 컴파일 시간을 얻기 위해 행의 수에 의해 컴파일 시간을 나누었다. 결과는 다음과 같습니다

250 VALUES 절은 절 컴파일 시간 / 숫자가 약간 상승 추세하지만 너무 극적인 아무것도가 제시까지.

그러나 갑작스런 변화가있다.

데이터의 부분은 다음과 같다.
```
+------+----------------+-------------+---------------+---------------+
| Rows | CachedPlanSize | CompileTime | CompileMemory | Duration/Rows |
+------+----------------+-------------+---------------+---------------+
|  245 |            528 |          41 |          2400 | 0.167346939   |
|  246 |            528 |          40 |          2416 | 0.162601626   |
|  247 |            528 |          38 |          2416 | 0.153846154   |
|  248 |            528 |          39 |          2432 | 0.157258065   |
|  249 |            528 |          39 |          2432 | 0.156626506   |
|  250 |            528 |          40 |          2448 | 0.16          |
|  251 |            400 |         273 |          3488 | 1.087649402   |
|  252 |            400 |         274 |          3496 | 1.087301587   |
|  253 |            400 |         282 |          3520 | 1.114624506   |
|  254 |            408 |         279 |          3544 | 1.098425197   |
|  255 |            408 |         290 |          3552 | 1.137254902   |
+------+----------------+-------------+---------------+---------------+
```
선형 갑자기 성장했다 캐시 된 계획의 크기 방울하지만, 컴파일 타임이 7 배와 CompileMemory 촬영을 증가시킨다. 비에 (1000 개 매개 변수와 함께)를 매개 변수화이 자동되는 계획 사이의 지점 오프 컷 하나를 매개 변수화입니다. 그 후는 (주어진 시간에 처리 절 값의 수의 관점에서) 선형 비효율적 얻는 것으로 보인다.

이되어야하는 이유 확실하지. 그것은 (예 : 정렬로) 선형 적으로 확장되지 않는 몇 가지 작업을 수행해야하는 특정 문자 값에 대한 계획을 컴파일 아마도 때.

내가 중복 행의 전체 구성되는 쿼리를 시도 할 때 캐시 된 쿼리 계획의 크기에 영향을 미칠 것으로 보인다 및도 (상수 테이블의 출력의 순서에 영향을하고 힙 시간에 삽입 될 때 정렬을 소비하지 않습니다 )는 한해도 무의미 어쨌든이 될 것입니다.

클러스터 된 인덱스가 테이블에 추가됩니다 또한 경우 계획은 여전히 실행시에 정렬을 피하기 위해 컴파일 타임에 정렬하지 않는 것, 그래서 명시 적 정렬 단계를 보여줍니다.

나는 디버거에서이 보는 시도했지만 SQL 서버 2008의 내 버전의 공용 기호를 사용할 수하지 않는 것 그래서 대신 내가 SQL Server 2005의 해당 UNION ALL 건설에서 지켜 볼 수밖에 없었습니다.

전형적인 스택 추적은 다음과 같습니다
```
sqlservr.exe!FastDBCSToUnicode()  + 0xac bytes  
sqlservr.exe!nls_sqlhilo()  + 0x35 bytes    
sqlservr.exe!CXVariant::CmpCompareStr()  + 0x2b bytes   
sqlservr.exe!CXVariantPerformCompare<167,167>::Compare()  + 0x18 bytes  
sqlservr.exe!CXVariant::CmpCompare()  + 0x11f67d bytes  
sqlservr.exe!CConstraintItvl::PcnstrItvlUnion()  + 0xe2 bytes   
sqlservr.exe!CConstraintProp::PcnstrUnion()  + 0x35e bytes  
sqlservr.exe!CLogOp_BaseSetOp::PcnstrDerive()  + 0x11a bytes    
sqlservr.exe!CLogOpArg::PcnstrDeriveHandler()  + 0x18f bytes    
sqlservr.exe!CLogOpArg::DeriveGroupProperties()  + 0xa9 bytes   
sqlservr.exe!COpArg::DeriveNormalizedGroupProperties()  + 0x40 bytes    
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x18a bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!COptExpr::DeriveGroupProperties()  + 0x146 bytes   
sqlservr.exe!CQuery::PqoBuild()  + 0x3cb bytes  
sqlservr.exe!CStmtQuery::InitQuery()  + 0x167 bytes 
sqlservr.exe!CStmtDML::InitNormal()  + 0xf0 bytes   
sqlservr.exe!CStmtDML::Init()  + 0x1b bytes 
sqlservr.exe!CCompPlan::FCompileStep()  + 0x176 bytes   
sqlservr.exe!CSQLSource::FCompile()  + 0x741 bytes  
sqlservr.exe!CSQLSource::FCompWrapper()  + 0x922be bytes    
sqlservr.exe!CSQLSource::Transform()  + 0x120431 bytes  
sqlservr.exe!CSQLSource::Compile()  + 0x2ff bytes   
```
스택 추적에 이름을 가고 그래서 시간 비교 문자열을 많이 보내고 나타납니다.

이 KB 기사는 DeriveNormalizedGroupProperties이 질의 처리의 정상화 단계를 호출하는 데 사용되는 것과 연관되어 있음을 나타냅니다

이 단계는 현재 결합 또는 algebrizing 호출되고 이전 파싱 단계에서 발현 파스 트리 출력을 얻어와 algebrized 식 트리 (쿼리 프로세서 트리) [참조] (이 경우에는 사소한 계획 최적화) 최적화를 전진에 출력된다.

나는 다시 실행 원래 테스트하지만, 세 가지 경우에보고했다 한 번 더 실험 (스크립트)을 시도했다.

분명히 더 이상 문자열이 나쁜 일을 얻을 수 있음을 알 수있다 그 반대로 더 많은 중복이 더 나은 것을 얻을. 나는 algebrized 식 트리 자체를 구성 할 때 중복 식별의 과정이 있어야한다는 가정 때문에 전술 한 바와 같이 중복은 캐시 된 계획의 크기에 영향을 미치지 않습니다.

편집하다

이 정보는 여기 @Lieven으로 표시됩니다 활용되어 한 곳에서
```
SELECT * 
FROM (VALUES ('Lieven1', 1),
             ('Lieven2', 2),
             ('Lieven3', 3))Test (name, ID)
ORDER BY name, 1/ (ID - ID) 
```
컴파일시에이 이름 열이 보조 1 / (ID - ID)에 의해 순서를 건너 뛰고에는 중복이없는 것으로 판단 할 수 있기 때문에 실행시에 식 (계획의 종류를 하나의 열 BY ORDER가) 제로 오류에 의해 더 나누기 발생합니다. 중복이 열로 정렬 운전자에게 다음 프로그램 표 개의 순서를 첨가하는 경우 예상되는 오류가 발생한다.
==============================
2.너무 놀라운 일이 아니다 : 작은 삽입에 대한 실행 계획을 한 번 계산하고 1000 번 재사용된다. 구문 분석하고와 델에 네 개의 값을 가지고 있기 때문에 계획을 준비하는 것은 빠르다. 1000 행 계획은, 다른 한편으로는, (당신이 당신의 C # 테스트를 매개 변수화하는 경우 또는 4000 개 매개 변수) 4000 개 값을 처리 할 필요가있다. 이것은 쉽게 네트워크가 지나치게 느린하지 특히, 당신은 SQL Server에 999 왕복을 제거하여 얻는 시간 절약을 먹을 수 있습니다.

너무 놀라운 일이 아니다 : 작은 삽입에 대한 실행 계획을 한 번 계산하고 1000 번 재사용된다. 구문 분석하고와 델에 네 개의 값을 가지고 있기 때문에 계획을 준비하는 것은 빠르다. 1000 행 계획은, 다른 한편으로는, (당신이 당신의 C # 테스트를 매개 변수화하는 경우 또는 4000 개 매개 변수) 4000 개 값을 처리 할 필요가있다. 이것은 쉽게 네트워크가 지나치게 느린하지 특히, 당신은 SQL Server에 999 왕복을 제거하여 얻는 시간 절약을 먹을 수 있습니다.
==============================
3.문제는 아마 쿼리를 컴파일하는 데 걸리는 시간과 관련이있다.

문제는 아마 쿼리를 컴파일하는 데 걸리는 시간과 관련이있다.

당신은 당신이 정말로 무엇을해야하는지 삽입물을 가속화하려는 경우 트랜잭션을 포장입니다 :
```
BEGIN TRAN;
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('6f3f7257-a3d8-4a78-b2e1-c9b767cfe1c1', 'First 0', 'Last 0', 0);
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('32023304-2e55-4768-8e52-1ba589b82c8b', 'First 1', 'Last 1', 1);
...
INSERT INTO T_TESTS (TestId, FirstName, LastName, Age) 
   VALUES ('f34d95a7-90b1-4558-be10-6ceacd53e4c4', 'First 999', 'Last 999', 999);
COMMIT TRAN;
```
C #을에서, 당신은 또한 테이블 반환 매개 변수를 사용하는 것이 좋습니다. 세미콜론으로 구분하여, 하나의 일괄 처리에서 여러 명령을 발행, 또한 도움이 될 것입니다 또 다른 접근 방법이다.
==============================
4.나는 C ++ 프로그램 (MFC / ODBC) 여러 100,000 행이있는 테이블을 변환하려고 비슷한 상황에 달렸다.

나는 C ++ 프로그램 (MFC / ODBC) 여러 100,000 행이있는 테이블을 변환하려고 비슷한 상황에 달렸다.

이 작업은 매우 긴 시간이 걸렸습니다 때문에, 나는 (때문에 MSSQL 제한으로 최대 1000) 하나에 여러 개의 삽입을 묶어 냈다. 단일 삽입 문이 많이 여기에 설명 된 것과 오버 헤드 비슷한을 만드는 것이 내 생각 엔.

그러나, 변환이 꽤 이상 실제로 걸렸다 밝혀 :
```
        Method 1       Method 2     Method 3 
        Single Insert  Multi Insert Joined Inserts
Rows    1000           1000         1000
Insert  390 ms         765 ms       270 ms
per Row 0.390 ms       0.765 ms     0.27 ms
```
그래서를 CDatabase :: ExecuteSql 1000 개 단일 통화를 하나의 INSERT 문 (방법 1) 각 1000 값 튜플 (방법 2) 여러 줄 INSERT 문을 CDatabase :: ExecuteSql에 단일 통화로 빠른 속도로 거의 두 배입니다.

업데이트 : 그래서, 내가 시도한 다음 일은 하나의 문자열로 1000 별도의 INSERT 문을 번들하고 서버가 그 (방법 3)을 실행하도록했다. 그것은이도 조금 더 빠른 방법 일보다 밝혀졌습니다.

편집 : Microsoft SQL Server Express Edition을 사용하고 (64 비트) v10.0.2531.0

from https://stackoverflow.com/questions/8635818/multiple-insert-statements-vs-single-insert-with-multiple-values by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 한 쿼리에서 두 테이블에서 삭제 (0)	2020.03.18
[SQL] 어떻게 SQL Server의 날짜에 BIGINT (UNIX 타임 스탬프)를 변환 할 수 있습니까? (0)	2020.03.18
[SQL] SQLite는 INSERT - ON DUPLICATE KEY UPDATE (UPSERT) (0)	2020.03.18
[SQL] C # SQL 서버 - 저장 프로 시저 목록을 전달 (0)	2020.03.18
[SQL] SQL - 하나 개의 쿼리의 업데이트는 여러 레코드 (0)	2020.03.18

복붙노트

[SQL] 여러 값을 하나의 INSERT 대 여러 INSERT 문

여러 값을 하나의 INSERT 대 여러 INSERT 문

해결법

1.이 최소화되어야합니다에 대한 당신의 계획 쇼는 하나의 인서트 (아마도 자동 매개 변수화) 구문 분석 / 컴파일 시간이 너무 매개 변수화 절차를 사용하고 있습니다.

3.문제는 아마 쿼리를 컴파일하는 데 걸리는 시간과 관련이있다.

4.나는 C ++ 프로그램 (MFC / ODBC) 여러 100,000 행이있는 테이블을 변환하려고 비슷한 상황에 달렸다.

'SQL' 카테고리의 다른 글

티스토리툴바