SQL Server에서 커서를 사용하는 것이 나쁜 습관으로 간주되는 이유는 무엇입니까?

SQL Server에서 커서를 사용하는 것이 나쁜 습관으로 간주되는 이유는 무엇입니까?

---

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

---

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

당신이 정말로하고있는 것은 세트 기반 기술을 비 세트 기반 기능으로 강제하려는 것입니다. 그리고 공평하게 커서 는 용도가 있지만 집합 기반 솔루션을 사용하는 데 익숙하지 않은 많은 사람들이 집합 기반 솔루션을 알아내는 대신 커서를 사용하기 때문에 커서 가 눈살을 찌푸립니다.

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

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

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

---

SQL이 집합 기반 환경이라는 위의 의견은 모두 사실입니다. 그러나 행 단위 작업이 유용한 경우가 있습니다. 메타 데이터와 동적 SQL의 조합을 고려하십시오.

매우 간단한 예로서, 내가 복사 / 잘라 내기 / 무엇이든 원하는 테이블의 이름을 정의하는 테이블에 100 개 이상의 레코드가 있다고 가정합니다. 어느 것이 최고입니까? 필요한 작업을 수행하기 위해 SQL을 하드 코딩합니까? 아니면이 결과 집합을 반복하고 동적 SQL (sp_executesql)을 사용하여 작업을 수행합니까?

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

그렇다면 커서 나 while 루프 (의사 커서)를 사용하려면?

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

INSENSITIVE는 결과 집합의 임시 복사본을 만듭니다 (의사 커서에 대해이 작업을 직접 수행하지 않아도 됨).

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

FAST_FORWARD는 최적화 된 정방향 전용, 읽기 전용 커서를 생성합니다.

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

---

커서가 필요할 때마다 사용하는 커서에 대한 해결 방법이 있습니다.

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

작업하는 데 필요한 모든 데이터를 삽입하십시오.

그런 다음 카운터 변수로 while 블록을 만들고 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 초보자가 커서를 발견하고 "이봐 for 루프! 나는 그것들을 사용하는 방법을 안다!" 모든 것에 계속해서 사용합니다.

If you use them for what they're designed for, I can't find fault with that.

---

SQL is a set based language--that's what it does best.

I think cursors are still a bad choice unless you understand enough about them to justify their use in limited circumstances.

Another reason I don't like cursors is clarity. The cursor block is so ugly that it's difficult to use in a clear and effective way.

All that having been said, there are some cases where a cursor really is best--they just aren't usually the cases that beginners want to use them for.

---

Sometimes the nature of the processing you need to perform requires cursors, though for performance reasons it's always better to write the operation(s) using set-based logic if possible.

I wouldn't call it "bad practice" to use cursors, but they do consume more resources on the server (than an equivalent set-based approach) and more often than not they aren't necessary. Given that, my advice would be to consider other options before resorting to a cursor.

There are several types of cursors (forward-only, static, keyset, dynamic). Each one has different performance characteristics and associated overhead. Make sure you use the correct cursor type for your operation. Forward-only is the default.

One argument for using a cursor is when you need to process and update individual rows, especially for a dataset that doesn't have a good unique key. In that case you can use the FOR UPDATE clause when declaring the cursor and process updates with UPDATE ... WHERE CURRENT OF.

Note that "server-side" cursors used to be popular (from ODBC and OLE DB), but ADO.NET does not support them, and AFAIK never will.

---

@ Daniel P -> you don't need to use a cursor to do it. You can easily use set based theory to do it. Eg: with Sql 2008

DECLARE @commandname NVARCHAR(1000) = '';

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

EXEC sp_executesql @commandname;

will simply do what you have said above. And you can do the same with Sql 2000 but the syntax of query would be different.

However, my advice is to avoid cursors as much as possible.

Gayam

---

There are very, very few cases where the use of a cursor is justified. There are almost no cases where it will outperform a relational, set-based query. Sometimes it is easier for a programmer to think in terms of loops, but the use of set logic, for example to update a large number of rows in a table, will result in a solution that is not only many less lines of SQL code, but that runs much faster, often several orders of magnitude faster.

Even the fast forward cursor in Sql Server 2005 can't compete with set-based queries. The graph of performance degradation often starts to look like an n^2 operation compared to set-based, which tends to be more linear as the data set grows very large.

---

Cursors are usually not the disease, but a symptom of it: not using the set-based approach (as mentioned in the other answers).

Not understanding this problem, and simply believing that avoiding the "evil" cursor will solve it, can make things worse.

For example, replacing cursor iteration by other iterative code, such as moving data to temporary tables or table variables, to loop over the rows in a way like:

SELECT * FROM @temptable WHERE Id=@counter 

or

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

Such an approach, as shown in the code of another answer, makes things much worse and doesn't fix the original problem. It's an anti-pattern called cargo cult programming: not knowing WHY something is bad and thus implementing something worse to avoid it! I recently changed such code (using a #temptable and no index on identity/PK) back to a cursor, and updating slightly more than 10000 rows took only 1 second instead of almost 3 minutes. Still lacking set-based approach (being the lesser evil), but the best I could do that moment.

Another symptom of this lack of understanding can be what I sometimes call "one object disease": database applications which handle single objects through data access layers or object-relational mappers. Typically code like:

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

instead of

var items = dataAccess.GetItemsByIds(itemIds);

The first will usually flood the database with tons of SELECTs, one round trip for each, especially when object trees/graphs come into play and the infamous SELECT N+1 problem strikes.

This is the application side of not understanding relational databases and set based approach, just the same way cursors are when using procedural database code, like T-SQL or PL/SQL!

---

Cursors do have their place, however I think it's mainly because they are often used when a single select statement would suffice to provide aggregation and filtering of results.

Avoiding cursors allows SQL Server to more fully optimize the performance of the query, very important in larger systems.

---

The basic issue, I think, is that databases are designed and tuned for set-based operations -- selects, updates, and deletes of large amounts of data in a single quick step based on relations in the data.

In-memory software, on the other hand, is designed for individual operations, so looping over a set of data and potentially performing different operations on each item serially is what it is best at.

Looping is not what the database or storage architecture are designed for, and even in SQL Server 2005, you are not going to get performance anywhere close to you get if you pull the basic data set out into a custom program and do the looping in memory, using data objects/structures that are as lightweight as possible.

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