게시판
단일 SQL 쿼리에서 여러 Row_Number() 호출
brewtine00292 ・ 2022. 3. 6. 5:45
URL 복사 이웃추가
본문 기타 기능
신고하기
SQL Server 2008에서 여러 중앙값을 계산하기 위해 일부 데이터를 설정하려고 하는데 성능 문제가 있습니다. 지금은 이 패턴 을 사용하고 있습니다([또 다른 예시 bottom ). 예, 저는 CTE를 사용하지 않지만 CTE를 사용해도 문제가 해결되지 않고 row_number 하위 쿼리가 병렬이 아닌 직렬로 실행되기 때문에 성능이 좋지 않습니다.
다음은 전체 예입니다. SQL 아래에서 문제를 더 자세히 설명합니다.
-- build the example table CREATE TABLE #TestMedian ( StateID INT, TimeDimID INT, ConstructionStatusID INT, PopulationSize BIGINT, SquareMiles BIGINT);INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)VALUES (1, 1, 1, 100000, 200000);INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)VALUES (1, 1, 1, 200000, 300000);INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)VALUES (1, 1, 1, 300000, 400000);INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)VALUES (1, 1, 1, 100000, 200000);INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)VALUES (1, 1, 1, 250000, 300000);INSERT INTO #TestMedian (StateID, TimeDimID, ConstructionStatusID, PopulationSize, SquareMiles)VALUES (1, 1, 1, 350000, 400000);--TruNCATE TABLE TestMedian SELECT StateID ,TimeDimID ,ConstructionStatusID ,NumberOfRows = COUNT(*) OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID) ,PopulationSizeRowNum = ROW_NUMBER() OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID ORDER BY PopulationSize) ,SquareMilesRowNum = ROW_NUMBER() OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID ORDER BY SquareMiles) ,PopulationSize ,SquareMiles INTO #MedianData FROM #TestMedian SELECT MinRowNum = MIN(PopulationSizeRowNum), MaxRowNum = MAX(PopulationSizeRowNum), StateID, TimeDimID, ConstructionStatusID, MedianPopulationSize= AVG(PopulationSize) FROM #MedianData T WHERE PopulationSizeRowNum IN((NumberOfRows + 1) / 2, (NumberOfRows + 2) / 2) GROUP BY StateID, TimeDimID, ConstructionStatusID SELECT MinRowNum = MIN(SquareMilesRowNum), MaxRowNum = MAX(SquareMilesRowNum), StateID, TimeDimID, ConstructionStatusID, MedianSquareMiles= AVG(SquareMiles) FROM #MedianData T WHERE SquareMilesRowNum IN((NumberOfRows + 1) / 2, (NumberOfRows + 2) / 2) GROUP BY StateID, TimeDimID, ConstructionStatusID DROP TABLE #MedianData DROP TABLE #TestMedian
이 쿼리의 문제는 SQL Server가 "ROW__NUMBER() OVER..." 하위 쿼리를 병렬이 아닌 직렬로 실행한다는 것입니다. 따라서 이러한 ROW__NUMBER 계산이 10개 있으면 차례로 계산하고 선형 증가를 얻습니다. 이 쿼리를 실행 중인 8방향 32GB 시스템이 있고 병렬 처리가 필요합니다. 5,000,000개의 행 테이블에서 이러한 유형의 쿼리를 실행하려고 합니다.
쿼리 계획을 보고 동일한 실행 경로에서 정렬을 확인하여 이 작업을 수행하는 것을 알 수 있습니다(쿼리 계획의 XML 표시는 SO에서 제대로 작동하지 않음).
제 질문은 이것입니다. ROW_NUMBER 쿼리가 병렬로 실행되도록 이 쿼리를 어떻게 변경할 수 있습니까? 다중 중앙값 계산을 위해 데이터를 준비하는 데 사용할 수 있는 완전히 다른 기술이 있습니까?
각 ROW_NUMBER에는 먼저 행을 정렬해야 합니다. 두 RN의 ORDER BY 조건이 다르기 때문에 쿼리는 결과를 생성한 다음 첫 번째 RN(이미 정렬될 수 있음)에 대해 주문하고 RN을 생성한 다음 두 번째 RN에 대해 주문하고 두 번째 RN 결과를 생성해야 합니다. 행이 필요한 순서대로 어디에 있는지 계산하지 않고 행 번호 값을 구체화할 수 있는 마법의 픽시 더스트는 없습니다.
몇 가지 측면적 사고: 이 데이터가 자주 그리고/또는 빠르게 필요하고 기본 데이터 세트가 자주 변경되지 않는 경우(합리적으로 높은 "자주" 값의 경우), 이러한 값을 미리 계산하여 다음 형식으로 저장할 수 있습니까? 사전 집계 테이블?
(예, 이것은 비정규화이지만 다른 모든 것보다 성능이 필요한 경우 고려할 가치가 있습니다.)
분할되지 않은(wrt 인구 대 평방 마일) 스캔을 수행해야 하기 때문에 병렬화할 수 있는지 확신할 수 없습니다. 디스크의 각각과 충돌하므로 먼저 모든 것을 메모리에 한 번 이상 가져와야 하며 충분히 큰 경우 병렬화에 적합할 수 있습니다.
어쨌든 다음은 나를 위해 훨씬 더 빠르게(40%) 수행됩니다.
;WITH cte AS ( SELECT StateID ,TimeDimID ,ConstructionStatusID ,NumberOfRows = COUNT(*) OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID) ,PopulationSizeRowNum = ROW_NUMBER() OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID ORDER BY PopulationSize) ,SquareMilesRowNum = ROW_NUMBER() OVER (PARTITION BY StateID, TimeDimID, ConstructionStatusID ORDER BY SquareMiles) ,PopulationSize ,SquareMiles FROM TestMedian), ctePop AS ( SELECT MinPopNum = MIN(PopulationSizeRowNum) , MaxPopNum = MAX(PopulationSizeRowNum) , StateID, TimeDimID, ConstructionStatusID , MedianPopulationSize= AVG(PopulationSize) FROM cte T WHERE PopulationSizeRowNum IN((NumberOfRows + 1) / 2, (NumberOfRows + 2) / 2) GROUP BY StateID, TimeDimID, ConstructionStatusID), cteSqM AS ( SELECT MinSqMNum = MIN(SquareMilesRowNum) , MaxSqMNum = MAX(SquareMilesRowNum) , StateID, TimeDimID, ConstructionStatusID , MedianSquareMiles= AVG(SquareMiles) FROM cte T WHERE SquareMilesRowNum IN((NumberOfRows + 1) / 2, (NumberOfRows + 2) / 2) GROUP BY StateID, TimeDimID, ConstructionStatusID)SELECT s.StateID, s.TimeDimID, s.ConstructionStatusID, MinPopNum, MaxPopNum, MedianPopulationSize, MinSqMNum, MaxSqMNum, MedianSquareMilesFROM ctePop pJOIN cteSqM s ON s.StateID = p.StateID AND s.TimeDimID = p.TimeDimID AND s.ConstructionStatusID = p.ConstructionStatusID
또한 정렬 자체가 충분히 커지면 병렬화되어야 합니다. 하지만 그렇게 되기 전에 테스트 행이 100,000개 이상 필요합니다.
네, 다음 문장으로 충분히 로드한 후에 병렬 처리를 얻습니다.
INSERT INTO TestMedian SELECT abs(id)%3,abs(id)%2,abs(id)%5, abs(id), colid * 10000 From master.sys.syscolumns, (select top 10 * from master.dbo.spt_values)a
댓글쓰기 이 글에 댓글 단 블로거 열고 닫기
인쇄
