커서는 개발자에게 바로 가기처럼 보일 수 있습니다. 할 때에는 복잡한 작업을 수행하고 당신은 필요합을 조작하는 행 테이블에서는 가장 빠른 방법으로 보일 수 있습니다 반복을 통해 행해 하나 하나를 사용하여 Transact-SQL 커서입니다. 모든 후기 때문이 있을 반복을 통해 데이터 구조에서 자신의 코드는 클라이언트 측에서,당신은 유혹 될 수 있습니다 같은 일을 할 때 당신과 함께 다루고 있어 SQL 서버 데이터입니다., 그러나 반복을 통해 데이터 Transact-SQL 을 사용하여 커서는 자주 잘 조절되지 않고,나는 당신을 설득하는 것도 좋은 디자인 또는 건축습니다.
커서 경험
I 기 때문이 몇 달 전,가격으로 공급업체와의 Transact-SQL 스크립트는 업그레이드된 데이터베이스 구성 요소의 새로운 버전이 공급업체 응용 프로그램입니다. 그들은 매우 큰 테이블을 피벗하고 연결된 문자열로 새 테이블에 관련 데이터를 수평으로 저장하도록 스크립트를 설계했습니다., 공급업체 성능을 향상시키고자 했으로써 테이블의 작은,그래서 그들은 결정을 저장할 세부 데이터를 수평으로,쉼표로 구분된 문자열에 대한 각 부모 id 입니다. 클라이언트 응용 프로그램를 쿼리할 수 있습 결과 쉼표로 구분된 문자열을 보다 더 빨리 받고 그들 각각의 개인으로 행,그리고 맥락에서 변경 내용을 감았고 향상 응용 프로그램의 성능을 제공합니다.
그러나,공급업체의 Transact-SQL 스크립트를 피벗의 데이터를 업그레이드하는 동안 했 16 시간을 실행하는 테스트에서는 기계,그리고 고객이 감당할 수없는 몇 시간 이상의 다운타임에 대한 업그레이드합니다., 때 우리는 검사 공급업체 스크립트를 우리가 보는 개발자 코드 회전 프로세스에 두 개의 단계:커서를 반복해 모든 부모의 테이블 id 을 구축하는 빈 사전 형식의 테이블,그리고 다른 스크립트를 연결 문자열은 다시 사용하여 커서입니다.
집합 기반 접근 방식을 사용하여 처리 시간을 16 플러스 시간에서 5 분 미만으로 줄일 수있었습니다. 우리는 개발자의 원래 전략을 따라 SELECT 문을 사용하여 빈 테이블을 작성했으며 해당 단계의 시간을 2 분 미만으로 줄였습니다., 그런 다음 상위 id 당 실행되는 UPDATE 문을 사용하여 문자열을 연결했습니다. 부모 id 를 통한 우리의 반복은 WHILE 루프를 사용했으며 3 분 이내에 완료되었습니다.
필연성의 반복
많은에 액세스하는 데이터베이스의 데이터복적서는 패션을 준비하기 위해 데이터에 대한 추가 조작. SQL Server 엔진조차도 사용 가능한 다양한 유형의 조인을 사용하여 데이터를 검색하거나 조인 할 때 데이터를 반복합니다. 대용량 데이터 세트에서 많은 행을 반환하는 쿼리에 대한 SQL Server 쿼리 계획을 검사 할 때이를 볼 수 있습니다., 조인의 경우 가장 일반적으로 중첩 루프가 표시되지만 때로는 병합 또는 해시 조인이 표시됩니다. 더 간단한 쿼리의 경우 클러스터 또는 비 클러스터 인덱스 검사가 표시 될 수 있습니다. SQL Server 가 단일 행 또는 작은 행 세트를 반환 할 수 있고 테이블에 적절한 인덱스가 있으면 인덱스를 사용하여 seek 가 표시됩니다.
그것에 대해 생각:마이크로소프트는 최적화하고 튜닝하는 SQL Server 엔진 몇 년 동안 반복을 통해 사용 가능한 데이터를 효율적으로 가능합니다., 는 경우,상상했던 시간이었다 지출하고자하는 에너지,당신은 아마 쓰는 낮은 수준에 액세스하는 데이터베이스 데이터는 파일 아 효율적입니다. 그러나,그것은 것이 효율적으로만 개인을 위해 당신은 당신의 앞에 작업,그리고 당신은 디버그 수 있습니다 그것을 완전하게 다시 작성하는 경우가 그것의 범위는 데이터 액세스를 변경합니다. 그것은 아마도 당신이 정말로 코드를 얻을 완벽하게 최적화 및 일반화,심지어 당신은 없을까 효율성의 코드가 내부에 SQL 서버 스토리지 엔진.,
그렇다면 바퀴를 다시 발명하는 데있어 이득은 어디에 있습니까? 그것은 단지 때문에 SQL Server 엔진은 그렇게 잘 최적화 및 디버깅,그것의 더 나은 그것을 가지 반복한 당신의 활용한 광범위한 개발하고 테스트하는 것이 이미 포함된 데이터베이스에서. 데이터 처리 작업을보다 자세히 살펴보면 커서가 필요한 경우가 거의 없다는 것을 알 수 있다고 생각합니다. 우선,종종 Transact-SQL 의 set 기반 SQL 명령에 의존하고 테이블의 행 순서를 무시함으로써 목표를 달성 할 수 있습니다., 둘째,Transact-SQL 커서는 테이블을 행 단위로 반복하는 한 가지 방법 일뿐입니다. 는 경우에 당신은 고유하게 식별할 수 있습의 모든 행하는 테이블을 반복되어야 합니다,사용할 수 있습하는 동안 반복보다 커서,그리고 잠재적으로 얻는 더 나은 성능을 제공합니다. 이유를 보여주기 위해 예를 통해 당신을 걸어 보자.
비교 반복 전략
정을 고유하게 식별할 수 있는 각 행 테이블의 테이블에 있기 때문에 고유 키나 독특한 그룹의 열이 있습니다., 는 루프에,당신이해야 할 모든 것을 찾을 가장 낮은 값의 독특한 조건은,그 다음 가장 높은 값을 각 시간을 반복. 다음은 SQL Server2005AdventureWorks 샘플 데이터베이스 프로덕션의 예입니다.TransactionHistory 테이블. 기본 키에 클러스터 된 인덱스가 있으며 WHILE 루프는 매번 행을 탐색 할 수 있습니다.,
USE AdventureWorksGODECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int SET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory)WHILE @TransactionID IS NOT NULLBEGINSET @TransactionID = (SELECT MIN(TransactionID)FROM Production.TransactionHistory WHERE TransactionID > @TransactionID)END여기에는 동일한 루프를 사용하여 빠르게 앞으로 커서는 가장 효율적인 유형의 Transact-SQL 커서 대한 데이터를 읽는다.
DECLARE @TransactionID int, @TransactionType nchar(1), @Quantity int DECLARE AW_Cursor CURSOR FORWARD_ONLYFORSELECT TransactionID, TransactionType, QuantityFROM Production.TransactionHistory OPEN AW_Cursor FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @Quantity WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM AW_CursorINTO @TransactionID, @TransactionType, @QuantityEND CLOSE AW_Cursor DEALLOCATE AW_Cursor내 컴퓨터에 후,나는 그것을 실행은 몇 시간을 확인하는 데이터는 모두에서 캐시 동안 루프는 구 초고 커서 17 초입니다. 자신의 기간은 다를 수 있습니다. 예제가 실제로 데이터로 아무 것도하지 않더라도 WHILE 루프가 더 빠르다는 점에 유의하십시오. 커서는 분명히 더 많은 오버 헤드를 추가합니다.,커서는 또한 코드를 어수선하게 보이게하는 추가 명령이 필요합니다. 으로 하지 않고의 세부 사항을 어떻게 커서 작동하는 Microsoft 설명합에서 완전히 Microsoft SQL Server2005 온라인 예고할 때 사용하는 동안 반복 요구 사항은 없을 선언하고,열림,닫기,deallocate 니다. 논리는 더 간단하며 길을 따라 자유롭게 행을 업데이트 할 수도 있습니다. 커서를 사용하여 행을 업데이트하려면 커서 유형을 변경해야합니다.
도 잠시 루프는 반복의 오버 헤드를 추가합니다., Set 기반 SELECT 명령으로 바꾸거나 루프에서 수행하려는 모든 업데이트를 set 기반 UPDATE 명령으로 바꾸고 sql Server 엔진에 iterating 을 맡길 수 있습니다. 선택한 문이 동일한 데이터를 얻으로 우리는 커서하고있는 동안 반복 위 takes less than3 초 동안 그것은 반환하는 행 클라이언트에는보다 더 많은 노력이 필요한 일이 두 가지 이전 루프는 않습니다.
SELECT *FROM Production.TransactionHistory이 선택에 의존한 SQL 서버를 통해 반복하는 데이터이며,지금까지 가장 빠른 세 가지 방법의 데이터 액세스를 우리가 보았다.,
가방에서 세트로
때때로 커서가 필요한 것처럼 보일 수 있습니다. 단순히 실제 순서로 행 단위로 데이터베이스 데이터를 반복해야 할 때 때로는 커서 만 작동합니다. 이것은 중복 행이 있고 테이블에서 주어진 행을 고유하게 식별 할 수있는 방법이없는 경우에 가장 일반적으로 발생합니다. 이 테이블은’가방’이 세트처럼 중복 값을 제거하지 않으므로 데이터 세트가 아닌 가방입니다.
가방 등의 데이터 일반적으로 발생할 때 당신 데이터 가져오기에서 외부 원본과할 수 없습니다 완전히 신뢰할 수 있는 데이터입니다., 는 경우,예를 들어,우리의 AdventureWorks 거래 역사를 표했고 그룹이 없는 열의를 부를 수 있는 독특한,및/또는 행을 복제할 수 있습을 생각하는 당신은 사용해야 합니다.
그러나 항상 행의 가방을 정규화 된 테이블로 바꿀 수 있습니다. 는 경우에도 당신은 복제 행 테이블에는 설정의할 수 있는 열에 의존하는 고유성을 추가할 수 있습니다 id 테이블 종자 id 를 시작 번호 매기기 1. 이렇게하면 테이블에 고유 한 키가 추가되어 커서 대신 WHILE 루프를 사용할 수 있습니다., 고유 키가 있으면 Transact-SQL set-based UPDATE 명령을 사용하여 중복을 제거 할 수 있습니다.
논리 API 데이터베이스 데이터
설정을 사용하여 기반 작업이보다 더 잘 반복하는 데이터 자신을에서는 적어도 두 가지 방법이 있습니다.
첫째,set 기반 SQL 명령은 sql Server 의 고도로 최적화 된 엔진을 사용하여 반복을 수행하므로 더 효율적입니다. 데이터를 직접 반복하는 경우 sql Server 스토리지 엔진을 최적으로 사용하지 않습니다. 대신 한 번에 하나의 행만 검색하는 명령으로 페퍼링하고 있습니다., 단일 행을 요청할 때마다 명령이 sql Server optimizer 를 거쳐야 저장소 엔진에 들어가기 전에 SQL Server storage engine 의 최적화 된 코드를 사용하지 않게됩니다. 자신을 반복 한 경우 데이터를 처리 할 때 테이블에 대한 외부 물리적 정보,즉 행의 순서에 의존하기도합니다. Set-base Transact-SQL SELECT,UPDATE 및 DELETE 명령은 행의 순서를 무시하고 데이터의 특성에 따라 영향을 줄 수있는 방법을 제공합니다.,
두 번째로,세트-기반으로 명령이 더 논리적이기 때문에 사고에 대한 데이터 세트에서는 초록 당신은에서 불필요한 정보는가에 더 관심이 어떻게 데이터를 실제로 정렬됩니다. 사실에서,세트 기반의 같은 명령어를 선택하고,업데이트하고,삭제할 때,적용하는 테이블지 않고 바로에 커서 또는 루프에 가까운 당신을 논리적으로 귀하의 데이터이기 때문에 정확하게 무시할 수 있습의 순서는 데이터입니다.,
여기에는 다른 방법에 대해 생각하는 두 번째 이점로 저장되는 절차가 가장 자연적인 API 프로그램과 인터페이스 SQL 서버 프로그래밍 방식으로,그래서 집합 기반 SQL 명령은 적절한 API 에 액세스하기 위한 관계형 데이터입니다. 저장 프로 시저는 응용 프로그램을 데이터베이스 내부와 분리하므로 임시 쿼리보다 효율적입니다. 마찬가지로,세트-기 SQL 명령을 내 Transact-SQL 당신에게 논리적 인터페이스는 관계형 데이터,그리고 그들은 더 효율적이기 때문에 당신은에 의존하는 SQL 서버 스토리지 엔진을 위한 반복을 통해 데이터입니다.,결론은 데이터를 반복하는 것이 나쁜 것이 아닙니다. 실제로,종종 피할 수없는 일입니다. 오히려,지점,그 스토리지 엔진을 위해 그것을 할 의존하는 대신에 논리적 인터페이스의 세트-기반 Transact-SQL 명령입니다. 실제로 Transact-SQL 커서를 사용해야하는 상황이 있으면 몇 가지를 찾을 수 있다고 생각합니다.피>