SQL Server에서 커서를 사용하는 것이 잘못된 관행으로 간주되는 이유는 무엇입니까?

SQL Server에서 커서를 사용하는 것이 잘못된 관행으로 간주되는 이유는 무엇입니까?

SQL 7일 전에 몇 가지 성능상의 이유를 알고 있었지만 SQL Server 2005에서 동일한 문제가 여전히 발생합니까?저장 프로시저에 개별적으로 작업하려는 결과 집합이 있는 경우에도 커서가 여전히 잘못된 선택입니까?만약 그렇다면, 왜?

커서가 메모리를 차지하고 잠금을 생성하기 때문입니다.

실제로 여러분이 하고 있는 것은 세트 기반 기술을 비세트 기반 기능으로 강제하는 것입니다.공정하게 말하자면, 커서는 유용하지만, 세트 기반 솔루션을 사용하는 것에 익숙하지 않은 많은 사람들이 세트 기반 솔루션을 찾는 대신 커서를 사용하기 때문에, 커서는 눈살을 찌푸리게 됩니다.

그러나 커서를 열면 기본적으로 해당 행을 메모리에 로드하고 잠궈 잠재적인 블록이 생성됩니다.그런 다음 커서를 순환하면서 다른 테이블을 변경하고 커서의 모든 메모리와 잠금을 열어 둡니다.

이 모든 것은 다른 사용자에게 성능 문제를 일으킬 수 있습니다.

따라서 일반적으로 커서는 눈살을 찌푸립니다.특히 그것이 문제를 해결하기 위해 도달한 첫 번째 해결책이라면 더욱 그렇습니다.

SQL이 세트 기반 환경이라는 위의 언급은 모두 사실입니다.그러나 행별 작업이 유용할 때가 있습니다.메타데이터와 dynamic-sql의 조합을 고려합니다.

매우 간단한 예로 복사/잘라내기/아무것이나 할 테이블의 이름을 정의하는 100개 이상의 레코드가 테이블에 있다고 가정합니다.어떤 것이 가장 좋습니까?필요한 작업을 수행하기 위해 SQL을 하드 코딩하시겠습니까?아니면 이 결과 집합을 반복하고 dynamic-SQL(sp_executesql)을 사용하여 작업을 수행하시겠습니까?

집합 기반 SQL을 사용하여 위의 목표를 달성할 수 있는 방법은 없습니다.

커서를 사용하거나 잠시 루프(의사 커서)를 사용하시겠습니까?

SQL 커서는 올바른 옵션을 사용하는 한 괜찮습니다.

INSIVENT는 결과 집합의 임시 복사본을 만듭니다(의사 커서를 위해 직접 이 작업을 수행할 필요가 없습니다).

READ_ONLY는 기본 결과 집합에서 잠금이 유지되지 않도록 합니다.기본 결과 세트의 변경 내용은 후속 페치에 반영됩니다(의사 커서에서 TOP 1을 가져오는 경우와 동일).

FAST_FORWARD는 최적화된 전진 전용 읽기 전용 커서를 만듭니다.

모든 커서를 사악한 것으로 판단하기 전에 사용 가능한 옵션에 대해 읽으십시오.

커서가 필요할 때마다 사용하는 커서에 대한 작업이 있습니다.

ID 열이 있는 테이블 변수를 만듭니다.

작업에 필요한 모든 데이터를 그 안에 넣습니다.

그런 다음 카운터 변수로 시간 블록을 만들고 ID 열이 카운터와 일치하는 select 문이 있는 테이블 변수에서 원하는 데이터를 선택합니다.

이런 식으로 나는 아무것도 잠그지 않고 훨씬 적은 메모리와 안전을 사용하며, 나는 메모리 손상이나 비슷한 것으로 아무것도 잃지 않을 것입니다.

그리고 블록 코드는 보기 쉽고 다루기 쉽습니다.

다음은 간단한 예입니다.

DECLARE @TAB TABLE(ID INT IDENTITY, COLUMN1 VARCHAR(10), COLUMN2 VARCHAR(10))

DECLARE @COUNT INT,
        @MAX INT, 
        @CONCAT VARCHAR(MAX), 
        @COLUMN1 VARCHAR(10), 
        @COLUMN2 VARCHAR(10)

SET @COUNT = 1

INSERT INTO @TAB VALUES('TE1S', 'TE21')
INSERT INTO @TAB VALUES('TE1S', 'TE22')
INSERT INTO @TAB VALUES('TE1S', 'TE23')
INSERT INTO @TAB VALUES('TE1S', 'TE24')
INSERT INTO @TAB VALUES('TE1S', 'TE25')

SELECT @MAX = @@IDENTITY

WHILE @COUNT <= @MAX BEGIN
    SELECT @COLUMN1 = COLUMN1, @COLUMN2 = COLUMN2 FROM @TAB WHERE ID = @COUNT

    IF @CONCAT IS NULL BEGIN
        SET @CONCAT = '' 
    END ELSE BEGIN 
        SET @CONCAT = @CONCAT + ',' 
    END

    SET @CONCAT = @CONCAT + @COLUMN1 + @COLUMN2

    SET @COUNT = @COUNT + 1
END

SELECT @CONCAT

커서는 SQL 초보자들이 커서를 발견하고 "Heya for loop!"이라고 생각하기 때문에 나쁜 이름을 얻는 것 같습니다.저는 그것들을 사용하는 방법을 알고 있습니다!" 그리고 나서 그들은 계속해서 그것들을 모든 것에 사용합니다.

그것들을 용도에 맞게 사용한다면, 저는 그것에 대해 흠잡을 데가 없습니다.

SQL은 집합 기반 언어입니다. 이것이 가장 효과적인 방법입니다.

제한된 상황에서 커서를 사용하는 것을 정당화하기 위해 커서에 대해 충분히 이해하지 않는 한 커서는 여전히 좋지 않은 선택이라고 생각합니다.

커서를 좋아하지 않는 또 다른 이유는 명확성입니다.커서 블록이 너무 못생겨서 명확하고 효과적인 방법으로 사용하기가 어렵습니다.

앞서 말한 것처럼 커서가 정말로 가장 좋은 경우도 있습니다. 초보자가 커서를 사용하기를 원하는 경우는 대개 아닙니다.

커서는 일반적으로 질병이 아니라 질병의 증상입니다. 즉, 설정 기반 접근 방식을 사용하지 않습니다(다른 답변에 언급됨).

이 문제를 이해하지 못하고, 단순히 "악" 커서를 피하는 것이 문제를 해결할 것이라고 믿는 것은 상황을 더 악화시킬 수 있습니다.

예를 들어 데이터를 임시 테이블이나 테이블 변수로 이동하는 등의 다른 반복 코드로 커서 반복을 대체하여 다음과 같은 방식으로 행을 반복합니다.

SELECT * FROM @temptable WHERE Id=@counter 

또는

SELECT TOP 1 * FROM @temptable WHERE Id>@lastId

다른 답변의 코드에서 볼 수 있듯이 이러한 접근 방식은 상황을 훨씬 더 악화시키고 원래 문제를 해결하지 못합니다.그것은 화물 컬트 프로그래밍이라고 불리는 반패턴입니다: 왜 어떤 것이 나쁜지 모르고 그래서 그것을 피하기 위해 더 나쁜 것을 시행합니다!최근에 이러한 코드(#temptable을 사용하고 ID/PK에 인덱스가 없음)를 커서로 다시 변경했는데, 10000개가 조금 넘는 행을 업데이트하는 데 거의 3분이 걸리지 않고 1초밖에 걸리지 않았습니다.아직 세트 기반 접근법이 부족하지만(작은 악이 됨) 그 순간에 최선을 다했습니다.

이러한 이해 부족의 또 다른 증상은 제가 "하나의 개체 질병"이라고 부르는 것일 수 있습니다. 즉, 데이터 액세스 계층 또는 개체 관계 매퍼를 통해 단일 개체를 처리하는 데이터베이스 응용 프로그램입니다.일반적으로 코드는 다음과 같습니다.

var items = new List<Item>();
foreach(int oneId in itemIds)
{
    items.Add(dataAccess.GetItemById(oneId);
}

대신에

var items = dataAccess.GetItemsByIds(itemIds);

첫 번째는 일반적으로 데이터베이스에 수 톤의 SELECT로 넘쳐납니다. 각 SELECT는 왕복 한 번씩입니다. 특히 객체 트리/그래프가 작동하고 악명 높은 SELECT N+1 문제가 발생할 때 그렇습니다.

이는 T-SQL 또는 PL/SQL과 같은 절차 데이터베이스 코드를 사용할 때 커서가 사용되는 것과 동일한 방식으로 관계형 데이터베이스를 이해하지 못하고 접근 방식을 설정하지 못하는 응용 프로그램 측면입니다!

수행해야 하는 처리 특성에 따라 커서가 필요한 경우도 있지만, 성능상의 이유로 가능하면 설정 기반 논리를 사용하여 작업을 작성하는 것이 좋습니다.

커서를 사용하는 것을 "나쁜 관행"이라고 부르지는 않겠지만, 커서는 서버에서 (동일한 세트 기반 접근 방식보다) 더 많은 리소스를 소비하고 필요하지 않은 경우가 많습니다.따라서 커서를 사용하기 전에 다른 옵션을 고려하는 것이 좋습니다.

커서에는 여러 가지 유형(전진 전용, 정적, 키 세트, 동적)이 있습니다.각각의 성능 특성과 관련 오버헤드가 다릅니다.작업에 올바른 커서 유형을 사용해야 합니다.전달 전용이 기본값입니다.

커서를 사용하는 이유 중 하나는 개별 행을 처리하고 업데이트해야 하는 경우이며, 특히 고유한 키가 없는 데이터 집합의 경우입니다.이 경우 UPDATE...를 사용하여 커서를 선언하고 업데이트를 처리할 때 FOR UPDATE 절을 사용할 수 있습니다.현재 위치

"서버측" 커서는 ODBC 및 OLE DB에서 사용되었지만 ADO.NET은 커서를 지원하지 않으며 AFAIK는 지원하지 않습니다.

커서 사용이 정당화되는 경우는 매우 극소수입니다.관계형 집합 기반 쿼리를 능가하는 경우는 거의 없습니다.때때로 프로그래머가 루프의 관점에서 생각하는 것이 더 쉽지만, 예를 들어 테이블의 많은 수의 행을 업데이트하는 등의 집합 논리를 사용하면 SQL 코드의 행 수가 적을 뿐만 아니라 훨씬 더 빠르게 실행되는 솔루션을 얻을 수 있습니다.

SQL Server 2005의 빠른 순방향 커서도 집합 기반 쿼리와 경쟁할 수 없습니다.성능 저하 그래프는 종종 집합 기반에 비해 n^2 연산처럼 보이기 시작하며, 데이터 집합이 매우 커질수록 선형적인 경향이 있습니다.

Daniel P -> 당신은 그것을 하기 위해 커서를 사용할 필요가 없습니다.세트 기반 이론을 사용하여 쉽게 수행할 수 있습니다.예: SQL 2008 사용

DECLARE @commandname NVARCHAR(1000) = '';

SELECT @commandname += 'truncate table ' + tablename + '; ';
FROM tableNames;

EXEC sp_executesql @commandname;

당신이 위에서 말한 것을 간단히 할 것입니다.또한 Sql 2000에서도 동일한 작업을 수행할 수 있지만 쿼리의 구문은 다릅니다.

하지만, 저의 조언은 커서를 최대한 피하는 것입니다.

가얌

커서는 위치가 있지만 하나의 선택 문이 결과의 집계 및 필터링을 제공하기에 충분할 때 자주 사용되기 때문이라고 생각합니다.

커서를 피하면 SQL Server가 쿼리 성능을 보다 완벽하게 최적화할 수 있으며, 이는 대규모 시스템에서 매우 중요합니다.

기본적인 문제는 데이터베이스가 데이터의 관계에 따라 한 번의 빠른 단계에서 대량의 데이터를 선택, 업데이트 및 삭제하는 세트 기반 작업에 맞게 설계되고 조정된다는 것입니다.

반면 메모리 내 소프트웨어는 개별 작업을 위해 설계되었으므로 데이터 집합을 반복하여 각 항목에 대해 서로 다른 작업을 연속적으로 수행하는 것이 가장 좋습니다.

데이터베이스 또는 스토리지 아키텍처는 루프를 위해 설계된 것이 아닙니다. SQL Server 2005에서도 기본 데이터 세트를 사용자 지정 프로그램으로 가져와서 최대한 가벼운 데이터 개체/구조를 사용하여 메모리에서 루프를 수행하면 성능이 향상되지 않습니다.

언급URL : https://stackoverflow.com/questions/58141/why-is-it-considered-bad-practice-to-use-cursors-in-sql-server