SQL Server에서 수백만 행의 큰 테이블을 업데이트하는 방법은 무엇입니까?
는 있어요.UPDATE백만개 이상의 기록을 갱신할 수 있는 성명서.1000개 또는 10000개 묶음으로 업데이트하고 싶습니다. .@@ROWCOUNT하지만 원하는 결과를 얻을 수 없습니다.
단지 테스트 목적으로 14개의 레코드가 있는 테이블을 선택해서 행 수를 5개로 설정했습니다.이 쿼리는 5, 5, 4의 레코드를 업데이트하도록 되어 있지만 처음 5개의 레코드만 업데이트합니다.
쿼리 - 1:
SET ROWCOUNT 5
UPDATE TableName
SET Value = 'abc1'
WHERE Parameter1 = 'abc' AND Parameter2 = 123
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 5
UPDATE TableName
SET Value = 'abc1'
WHERE Parameter1 = 'abc' AND Parameter2 = 123
PRINT (@@ROWCOUNT)
END
SET rowcount 0
쿼리 - 2:
SET ROWCOUNT 5
WHILE (@@ROWCOUNT > 0)
BEGIN
BEGIN TRANSACTION
UPDATE TableName
SET Value = 'abc1'
WHERE Parameter1 = 'abc' AND Parameter2 = 123
PRINT (@@ROWCOUNT)
IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END
COMMIT TRANSACTION
END
SET ROWCOUNT 0
내가 뭘 놓치고 있는 거지?
잠금을 하지 않은 경우(이 10k의 페이지 잠금의 가 아닌 한 는 안 .
UPDATE작업).문제는 5000개의 잠금에서 잠금 에스컬레이션(행 또는 페이지에서 테이블로 잠금)이 발생한다는 것입니다.따라서 작업이 행 잠금 장치를 사용할 경우에는 5000 이하로 유지하는 것이 가장 안전합니다.SET ROWCOUNT를 사용하여 수정할 행 수를 제한해서는 안 됩니다.여기에는 두 가지 문제가 있습니다.
SQL Server 2005가 출시된 이후(11년 전) 사용하지 않게 되었습니다.
SET ROWCOUNT를 사용하면 향후 SQL Server 릴리스에서 DELETE, INSERT 및 UPDATE 문에 영향을 주지 않습니다.SET ROWCOUNT를 새로운 개발 작업에서 DELETE, INSERT, UPDATE 문과 함께 사용하지 않도록 하고 현재 사용 중인 응용 프로그램을 수정할 계획입니다.유사한 동작의 경우 TOP 구문을 사용합니다.
이는 단순히 다루는 문장 이상의 영향을 미칠 수 있습니다.
SET ROWCOUNT 옵션을 설정하면 지정된 행 수에 영향을 받았을 때 대부분의 Transact-SQL 문이 처리를 중지합니다.여기에는 트리거가 포함됩니다.ROWCOUNT 옵션은 동적 커서에는 영향을 주지 않지만 키셋 및 둔감 커서의 행 집합을 제한합니다.이 옵션은 주의해서 사용해야 합니다.
그 대신에 합니다를 합니다.
TOP ()여기서 명시적인 거래를 하는 것은 목적이 없습니다.코드가 복잡해지고 당신은 처리할 수 없습니다.
ROLLBACK은 자체 auto-commit 필요하지도 즉, 은 에 이기 도 도 에 이기 .를 유지해야 할를 찾았다고 하면, Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ ΔΔ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ Δ
TRY/CATCH구조.DBA에서 제 답변을 봐주시기 바랍니다.스택 Exchange for aTRY/CATCH트랜잭션을 처리하는 템플릿:
내가 보기엔 진짜가WHERE절은 질문의 예제 코드에 표시되지 않으므로 단순히 표시된 내용에 의존하면 더 나은 모델이 될 수 있습니다.
DECLARE @Rows INT,
@BatchSize INT; -- keep below 5000 to be safe
SET @BatchSize = 2000;
SET @Rows = @BatchSize; -- initialize just to enter the loop
BEGIN TRY
WHILE (@Rows = @BatchSize)
BEGIN
UPDATE TOP (@BatchSize) tab
SET tab.Value = 'abc1'
FROM TableName tab
WHERE tab.Parameter1 = 'abc'
AND tab.Parameter2 = 123
AND tab.Value <> 'abc1' COLLATE Latin1_General_100_BIN2;
-- Use a binary Collation (ending in _BIN2, not _BIN) to make sure
-- that you don't skip differences that compare the same due to
-- insensitivity of case, accent, etc, or linguistic equivalence.
SET @Rows = @@ROWCOUNT;
END;
END TRY
BEGIN CATCH
RAISERROR(stuff);
RETURN;
END CATCH;
테스트로@Rows그에 반대하여@BatchSize, 당신은 결승전을 피할 수 있습니다.UPDATE(대부분의 경우) 최종 집합은 일반적으로 다음보다 적은 수의 행이기 때문에 쿼리합니다.@BatchSize, 이 경우 더 이상 처리할 것이 없음을 알 수 있습니다(답변에 표시된 출력에서 볼 수 있는 것입니다).최종 행 집합이 다음과 같은 경우에만@BatchSize이 코드가 결승전을 치르게 될까요?UPDATE0행에 영향을 미칩니다.
나는 또한 조건을 달았습니다.WHERE이미 업데이트된 행을 다시 업데이트하지 못하도록 하는 절입니다.
성능에 관한 참고사항
위에서 "더 나은"("이것이 더 나은 모델")을 강조한 것은 이것이 O.P.의 원래 코드보다 몇 가지 개선된 점이 있고, 많은 경우에 잘 작동하지만 모든 경우에 완벽한 것은 아니기 때문입니다.특정 크기 이상의 테이블의 경우(여러 요인으로 인해 변경되므로 더 구체적으로 설명할 수 없음), 다음 중 하나에 해당하는 경우 수정해야 할 행이 적으므로 성능이 저하됩니다.
- 쿼리를 지원할 인덱스가 없습니다.
- 인덱스가 있지만 적어도 하나의 열은
WHEREclause는 이진 대조를 사용하지 않는 문자열 데이터 유형입니다. 따라서COLLATE절은 이진 대조를 강제로 수행하기 위해 여기 쿼리에 추가되며, 이렇게 하면 인덱스(이 특정 쿼리의 경우)가 무효화됩니다.
이는 @mikesigs가 직면한 상황이므로 다른 접근법이 필요합니다.업데이트된 메서드는 업데이트할 모든 행의 ID를 임시 테이블로 복사한 다음 해당 임시 테이블을 사용하여INNER JOIN클러스터된 인덱스 키 열에서 업데이트 중인 테이블로 이동합니다.(주요 핵심 열인지 여부와 상관없이 클러스터화된 인덱스 열을 캡처하여 결합하는 것이 중요합니다.
자세한 사항은 아래 @mikesigs 답변을 참조하시기 바랍니다.그 답변에 나타난 접근 방식은 제가 여러 번 사용해 본 매우 효과적인 패턴입니다.변경 사항은 다음과 같습니다.
- 으로 합니다를 .
#targetIds는블블gnre기 보다 테이블SELECT INTO... - 의 경우 의 경우
#targetIds합니다.table,에본를다된다를본e된a에en,, - 의 경우 의 경우
#batchIds합니다.table,에본를다된다를본e된a에en,, - 에
#targetIds,사용하다INSERT INTO #targetIds (column_name(s)) SELECT제거합니다.ORDER BY불필요한 일이니깐
따라서 이 작업에 사용할 수 있는 인덱스가 없고 실제로 작동할 인덱스를 일시적으로 만들 수 없는 경우(필터링된 인덱스는 사용자에 따라 작동할 수 있음)WHERE한항 절UPDATEquery), 그런 다음 @ymmesigs 답변에 나와 있는 접근 방식을 시도해 보십시오(그리고 해당 솔루션을 사용하는 경우 업스케일링하십시오).
WHILE EXISTS (SELECT * FROM TableName WHERE Value <> 'abc1' AND Parameter1 = 'abc' AND Parameter2 = 123)
BEGIN
UPDATE TOP (1000) TableName
SET Value = 'abc1'
WHERE Parameter1 = 'abc' AND Parameter2 = 123 AND Value <> 'abc1'
END
저는 어제 이 스레드를 접했고 합격된 답변을 바탕으로 대본을 작성했습니다.33M 행 25M를 처리하는 데 12시간이 걸릴 정도로 매우 느린 성능을 보였습니다.오늘 아침에 취소하고 DBA와 함께 개선 작업을 하게 되었습니다.
DBA는 다음과 같이 지적했습니다.is null체크 인 내 UPDATE 쿼리는 PK에서 Clustered Index Scan을 사용하고 있었고 쿼리 속도가 느려진 것은 스캔이었습니다.기본적으로 쿼리가 오래 실행될수록 오른쪽 행에 대한 인덱스를 더 자세히 살펴볼 필요가 있습니다.
그가 생각해낸 접근법은 뒤에서 보면 분명했습니다.기본적으로 업데이트할 행의 ID를 임시 테이블에 로드한 다음 업데이트 문의 대상 테이블에 결합합니다.검색 대신 인덱스 검색을 사용합니다.그리고 소년은 일의 속도를 높입니다!마지막 8M 기록을 업데이트하는 데 2분이 걸렸습니다.
Temp Table을 사용한 배치
SET NOCOUNT ON
DECLARE @Rows INT,
@BatchSize INT,
@Completed INT,
@Total INT,
@Message nvarchar(max)
SET @BatchSize = 4000
SET @Rows = @BatchSize
SET @Completed = 0
-- #targetIds table holds the IDs of ALL the rows you want to update
SELECT Id into #targetIds
FROM TheTable
WHERE Foo IS NULL
ORDER BY Id
-- Used for printing out the progress
SELECT @Total = @@ROWCOUNT
-- #batchIds table holds just the records updated in the current batch
CREATE TABLE #batchIds (Id UNIQUEIDENTIFIER);
-- Loop until #targetIds is empty
WHILE EXISTS (SELECT 1 FROM #targetIds)
BEGIN
-- Remove a batch of rows from the top of #targetIds and put them into #batchIds
DELETE TOP (@BatchSize)
FROM #targetIds
OUTPUT deleted.Id INTO #batchIds
-- Update TheTable data
UPDATE t
SET Foo = 'bar'
FROM TheTable t
JOIN #batchIds tmp ON t.Id = tmp.Id
WHERE t.Foo IS NULL
-- Get the # of rows updated
SET @Rows = @@ROWCOUNT
-- Increment our @Completed counter, for progress display purposes
SET @Completed = @Completed + @Rows
-- Print progress using RAISERROR to avoid SQL buffering issue
SELECT @Message = 'Completed ' + cast(@Completed as varchar(10)) + '/' + cast(@Total as varchar(10))
RAISERROR(@Message, 0, 1) WITH NOWAIT
-- Quick operation to delete all the rows from our batch table
TRUNCATE TABLE #batchIds;
END
-- Clean up
DROP TABLE IF EXISTS #batchIds;
DROP TABLE IF EXISTS #targetIds;
느린 방법으로 배치합니다. 사용하지 마십시오!
참고로, 원래의 느린 수행 쿼리는 다음과 같습니다.
SET NOCOUNT ON
DECLARE @Rows INT,
@BatchSize INT,
@Completed INT,
@Total INT
SET @BatchSize = 4000
SET @Rows = @BatchSize
SET @Completed = 0
SELECT @Total = COUNT(*) FROM TheTable WHERE Foo IS NULL
WHILE (@Rows = @BatchSize)
BEGIN
UPDATE t
SET Foo = 'bar'
FROM TheTable t
JOIN #batchIds tmp ON t.Id = tmp.Id
WHERE t.Foo IS NULL
SET @Rows = @@ROWCOUNT
SET @Completed = @Completed + @Rows
PRINT 'Completed ' + cast(@Completed as varchar(10)) + '/' + cast(@Total as varchar(10))
END
저는 제 경험을 공유하고 싶습니다.며칠 전에 저는 테이블에 있는 2,100만 개의 레코드와 7,600만 개의 레코드를 업데이트해야 했습니다.제 동료가 다음 변종을 제안했습니다.예를 들어, 다음 표 'Person'이 있습니다.
Id | FirstName | LastName | Email | JobTitle
1 | John | Doe | abc1@abc.com | Software Developer
2 | John1 | Doe1 | abc2@abc.com | Software Developer
3 | John2 | Doe2 | abc3@abc.com | Web Designer
작업: 사용자를 새 작업으로 업데이트합니다. 제목: '소프트웨어 개발자' -> '웹 개발자'.
1.임시 테이블 'Person_SoftwareDeveloper_To_WebDeveloper(ID INT Primary Key)' 생성
2.새 작업 제목으로 업데이트할 임시 테이블 사용자를 선택합니다.
INSERT INTO Persons_SoftwareDeveloper_To_WebDeveloper SELECT Id FROM
Persons WITH(NOLOCK) --avoid lock
WHERE JobTitle = 'Software Developer'
OPTION(MAXDOP 1) -- use only one core
행 수에 따라 이 문은 임시 테이블을 채우는 데 시간이 다소 걸리지만 잠기지는 않습니다.제 상황에서는 약 5분(2,100만 줄)이 걸렸습니다.
3.주요 아이디어는 데이터베이스를 업데이트하기 위해 마이크로 sql 문을 생성하는 것입니다.그럼 인쇄해 보겠습니다.
DECLARE @i INT, @pagesize INT, @totalPersons INT
SET @i=0
SET @pagesize=2000
SELECT @totalPersons = MAX(Id) FROM Persons
while @i<= @totalPersons
begin
Print '
UPDATE persons
SET persons.JobTitle = ''ASP.NET Developer''
FROM Persons_SoftwareDeveloper_To_WebDeveloper tmp
JOIN Persons persons ON tmp.Id = persons.Id
where persons.Id between '+cast(@i as varchar(20)) +' and '+cast(@i+@pagesize as varchar(20)) +'
PRINT ''Page ' + cast((@i / @pageSize) as varchar(20)) + ' of ' + cast(@totalPersons/@pageSize as varchar(20))+'
GO
'
set @i=@i+@pagesize
end
이 스크립트를 실행하면 MS SQL Management Studio의 한 탭에서 실행할 수 있는 수백 개의 배치가 제공됩니다.
4.인쇄된 sql 문을 실행하고 테이블의 잠금을 확인합니다.항상 @pageSize로 처리를 중지하고 재생하여 업데이트 속도를 높이거나 속도를 낮출 수 있습니다(스크립트를 일시 중지한 후 @i를 변경하는 것을 잊지 마십시오).
5.사용자 삭제_소프트웨어 개발자_To_AspNetDeveloper.임시 테이블을 제거합니다.
마이너 노트:이 마이그레이션에는 시간이 걸리고 마이그레이션 중에 잘못된 데이터가 있는 새 행이 삽입될 수 있습니다.따라서 먼저 행이 추가되는 위치를 수정합니다.내 상황에서 UI를 고친 것은 '소프트웨어 개발자' -> '웹 개발자' 입니다.내 블로그 https://yarkul.com/how-smoothly-insert-millions-of-rows-in-sql-server/ 에서 이 방법에 대해 자세히 알아봅니다.
@Kramb의 솔루션을 보다 효율적으로 사용할 수 있습니다.절에서 이미 이를 처리하고 있으므로 존재 확인이 중복됩니다.대신 행 수를 잡고 배치 크기와 비교합니다.
또한 @Kramb 솔루션은 다음 반복에서 이미 업데이트된 행을 걸러내지 않았으므로 무한 루프가 됩니다.
또한 행 수를 사용하는 대신 현대적인 배치 크기 구문을 사용합니다.
DECLARE @batchSize INT, @rowsUpdated INT
SET @batchSize = 1000;
SET @rowsUpdated = @batchSize; -- Initialise for the while loop entry
WHILE (@batchSize = @rowsUpdated)
BEGIN
UPDATE TOP (@batchSize) TableName
SET Value = 'abc1'
WHERE Parameter1 = 'abc' AND Parameter2 = 123 and Value <> 'abc1';
SET @rowsUpdated = @@ROWCOUNT;
END
당신의.print 있습니다. 로고요죠기을stspegs .@@ROWCOUNT든지. @@ROWCOUNT, 제 조언은 항상 변수에 바로 맞추라는 것입니다.그래서:
DECLARE @RC int;
WHILE @RC > 0 or @RC IS NULL
BEGIN
SET rowcount 5;
UPDATE TableName
SET Value = 'abc1'
WHERE Parameter1 = 'abc' AND Parameter2 = 123 AND Value <> 'abc1';
SET @RC = @@ROWCOUNT;
PRINT(@@ROWCOUNT)
END;
SET rowcount = 0;
그리고 또 다른 좋은 점은 반복해서 작업할 필요가 없다는 것입니다.update암호를 매기다
우선 여러분의 의견에 감사드립니다.나는 내것을 조정합니다.Query - 1원하는 결과가 나왔습니다.고든 리노프 말이 맞지만,PRINT를 엉망으로 과 같이 했습니다.
수정된 쿼리 - 1:
SET ROWCOUNT 5
WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
UPDATE TableName
SET Value = 'abc1'
WHERE Parameter1 = 'abc' AND Parameter2 = 123
IF @@ROWCOUNT = 0
BEGIN
COMMIT TRANSACTION
BREAK
END
COMMIT TRANSACTION
END
SET ROWCOUNT 0
출력:
(5 row(s) affected)
(5 row(s) affected)
(4 row(s) affected)
(0 row(s) affected)
언급URL : https://stackoverflow.com/questions/35903375/how-to-update-large-table-with-millions-of-rows-in-sql-server
'programing' 카테고리의 다른 글
| ASP에서 MS Ajax 대 jQuery의 장단점.NET MVC 앱? (0) | 2023.09.14 |
|---|---|
| 마리아에서 SELECT IF 내부에 INSERT를 실행할 수 있습니까?DB (0) | 2023.09.14 |
| 시퀀스에서 ID를 생성하기 위한 Oracle Trigger의 하이버넌트 문제 (0) | 2023.09.14 |
| Wordpress 블로그의 RSS 피드에서 MathJax 사용 (0) | 2023.09.14 |
| Excel의 열 너비를 R로 정의 (0) | 2023.09.14 |