다중 INSERT 문 대 다중 값을 가진 단일 INSERT

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

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

놀랍게도 결과는 제가 생각했던 것과 정반대입니다.

• 1000 INSERT 문: 290 밀리초
• 1 INSERT 스테이트먼트(1000 VALUES: 2800 밀리초).

테스트는 측정에 사용되는 SQL Server Profiler를 사용하여 MSQL Management Studio에서 직접 실행됩니다(SqlClient를 사용하여 C# 코드에서 실행한 결과도 비슷합니다.이는 모든 DAL 레이어 라운드 트립을 고려할 때 더욱 놀라운 결과입니다).

이것이 타당하거나 어떤 식으로든 설명될 수 있을까요?왜 더 빠른 방법일 경우 성능이 10배(!)나 떨어집니까?

감사해요.

두 가지 하기:

추가:SQL Server 2012는 이 분야에서 성능이 다소 향상되었지만 아래에 언급된 특정 문제에 대해서는 대처하지 못하는 것으로 보입니다.이것은 SQL Server 2012 이후의 메이저버전에서는 수정이 필요할 것 같습니다.

계획에 따르면 단일 삽입물이 매개 변수화된 절차(아마도 자동 매개 변수화됨)를 사용하고 있으므로 이러한 삽입물에 대한 구문 분석/컴파일 시간은 최소화되어야 합니다.

좀 더 알아보려고 했는데 루프(스크립트)를 설정해 놓고 수를 조정해 보았습니다.VALUES절과 컴파일 시간을 기록합니다.

그런 다음 컴파일 시간을 행 수로 나누어 절당 평균 컴파일 시간을 구했습니다.결과는 다음과 같습니다.

250 † 250VALUES 절은 가 약간 상승 인 것은 .

하지만 그때 갑자기 변화가 생겼다.

데이터의 그 부분은 다음과 같습니다.

+------+----------------+-------------+---------------+---------------+
| 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배 증가하고 Compile Memory가 실행됩니다.이것은 자동 매개변수화된 계획(파라미터 1,000개 포함)과 매개변수화되지 않은 계획 사이의 컷오프 지점입니다.그 후에는 (특정 시간 내에 처리된 가치 절의 수 측면에서) 선형적으로 효율성이 떨어지는 것으로 보입니다.

왜 그래야 하는지 모르겠다.아마도 특정 리터럴 값에 대한 계획을 컴파일할 때 선형적으로 확장되지 않는 활동(예: 정렬)을 수행해야 합니다.

완전히 중복된 행으로 구성된 쿼리를 시도했을 때 캐시된 쿼리 계획의 크기에는 영향을 주지 않으며 상수 테이블의 출력 순서에도 영향을 주지 않습니다(또한 힙에 삽입할 경우 정렬에 소요되는 시간은 아무리 해도 무의미합니다).

또한 클러스터된 인덱스가 테이블에 추가되어도 계획에는 명시적인 정렬 단계가 표시되므로 실행 시 정렬을 피하기 위해 컴파일 시 정렬되지 않는 것으로 보입니다.

내 공개 할 수 없는 것 대신 Server 2008을 .UNION ALLSQL Server 2005 sql sql sql sql 。

일반적인 스택 트레이스는 다음과 같습니다.

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쿼리 처리의 정규화 단계라고 불리던 것과 관련되어 있습니다.

이 단계는 현재 바인딩 또는 대수로 불리며, 이전 구문 분석 단계에서 식 구문 분석 트리 출력을 가져와서 최적화(이 경우 사소한 계획 최적화)로 진행하기 위해 대수 분석 식 트리(쿼리 프로세서 트리)를 출력합니다. [ref]

원래 테스트를 다시 실행하지만 세 가지 사례를 살펴보는 실험(스크립트)을 하나 더 시도했습니다.

1. 이름 및 성 문자열 길이는 중복되지 않고 10자입니다.
2. 이름 및 성 문자열 길이는 50자로 중복되지 않습니다.
3. [이름(First Name)]및 [성(Last Name)]문자열은 모두 중복됩니다.

끈이 길수록 더 나빠지고 반대로 중복될수록 더 좋아진다는 것을 분명히 알 수 있다.앞에서 말한 것처럼 중복은 캐시된 계획의 크기에 영향을 주지 않기 때문에 대수식 트리 자체를 구축할 때 중복 식별 프로세스가 있을 것으로 추측됩니다.

편집

이 정보가 활용되고 있는 장소를 @Lieven에 나타냅니다.

SELECT * 
FROM (VALUES ('Lieven1', 1),
             ('Lieven2', 2),
             ('Lieven3', 3))Test (name, ID)
ORDER BY name, 1/ (ID - ID) 

시 " " " " 가 " " 가 " 가 " 가 " " 가 " 가 " 가 " 가 " 가 " 가 " " 라고될 수 .Name 순서로 .1/ (ID - ID)실행 시 표현식(계획의 종류에는 하나만 있음)ORDER BY0으로 나누기 오류는 발생하지 않습니다.테이블에 중복이 추가되면 정렬 연산자가 열별로 2개의 순서를 표시하며 예상되는 오류가 발생합니다.

이 작은 삽입물에 대한 실행 계획이 한 번 계산되고 다시 1000번 재사용되는 것은 그리 놀라운 일이 아닙니다.계획에는 4가지 값만 포함되어 있기 때문에 분석 및 준비가 빠릅니다.한편 1000행 플랜에서는 4000개의 값(C# 테스트를 파라미터화한 경우에는 4000개의 파라미터)을 처리해야 합니다.이로 인해 특히 네트워크 속도가 너무 느리지 않은 경우 SQL Server에 대한 999회 왕복이 없어져 시간을 절약할 수 없게 됩니다.

이 문제는 쿼리를 컴파일하는 데 걸리는 시간과 관련이 있을 수 있습니다.

삽입 속도를 높이려면 트랜잭션으로 삽입을 정리해야 합니다.

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#에서 table valued 파라미터의 사용을 검토할 수도 있습니다.여러 명령어를 세미콜론으로 구분하여 1개의 배치로 발행하는 것도 도움이 되는 방법입니다.

C++ 프로그램(MFC/ODBC)을 사용하여 10만 행이 여러 개 있는 테이블을 변환하려다가 비슷한 상황이 발생했습니다.

이 작업은 시간이 오래 걸리기 때문에 여러 개의 삽입물을 한 개에 번들할 수 있다고 생각했습니다(MS SQL 제한으로 인해 최대 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에 대한 1000개의 단일 콜:1개의 INSERT 스테이트먼트(메서드 1)를 사용하는 ExecuteSql은 각각 CDatabase에 대한1개의 콜보다 약 2배 고속입니다.1000개의 값 튜플을 가진 여러 줄의 INSERT 문을 사용하여 ExecuteSql을 지정합니다(메서드 2).

업데이트: 다음으로 1000개의 INSERT 문을 1개의 문자열에 번들하여 서버에서 실행하도록 했습니다(방식 3).이것은 방법 1보다 조금 더 빠른 것으로 나타났습니다.

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

언급URL : https://stackoverflow.com/questions/8635818/multiple-insert-statements-vs-single-insert-with-multiple-values