소개
관계형 데이터베이스에서 작업은 행 집합에서 이루어집니다. 예를 들어, SELECT 문은 결과 집합이라고하는 행 집합을 반환합니다. 애플리케이션 로직은 한 번에 전체 결과 세트가 아닌 한 번에 한 행으로 작업해야하는 경우가 있습니다. T-SQL에서이를 수행하는 한 가지 방법은 CURSOR을 사용하는 것입니다.
프로그래밍 기술을 보유하고 있다면 FOR 또는 WHILE과 같은 루프를 사용하여 한 번에 하나의 항목을 반복하고 데이터와 작업이 완료됩니다. T-SQL에서 CURSOR는 유사한 접근 방식이며 동일한 논리를 따르므로 선호 될 수 있습니다. 그러나이 길을 택하면 문제가 발생할 수 있습니다.
CURSOR를 사용하는 것이 그렇게 엉망이되지 않는 경우도 있지만 일반적으로 피해야합니다. 아래에서는 CURSOR를 사용하여 성능 문제가 발생하는 몇 가지 예를 보여주고 동일한 작업이 다른 여러 방법으로 수행 될 수 있음을 확인할 것입니다.
이 데모의 목적을 위해 AdventureWorks2012 데이터베이스를 사용합니다. 에서 데이터를 얻고 싶다고 가정 해 보겠습니다. 제조에 하루도 걸리지 않는 모든 제품에 대해 테이블 ..
커서 예
CURSOR를 사용하여 시작하고 다음 구문을 작성해 보겠습니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
AdventureWorks2012 사용
GO
DECLARE @id int
DECLARE cursorT CURSOR
–LOCAL STATIC
–LOCAL FAST_FORWARD
–LOCAL READ_ONLY FORWARD_ONLY
FOR
SELECT ProductId
FROM AdventureWorks2012.Production.Product
WHERE DaysToManufacture < = 1
OPEN cursorT
FETCH NEXT FROM cursorT INTO @id
WHILE @@ FETCH_STATUS = 0
BEGIN
SELECT * FROM Production.ProductInventory
WHERE ProductID = @ id
FETCH NEXT FROM cursorT INTO @id
END
CLOSE cursorT
DEALLOCATE cursorT
|
짧은 커피 휴식 후 , 쿼리 실행이 완료되어 아래 표시된 시간에 833 개의 행이 반환되었습니다. 위에서 선택한 구문은 데모 목적으로 만 사용된다는 점을 언급하는 것이 중요하며 속도를 높이기 위해 색인을 조정하지 않았습니다.
커서 실행에는 두 단계가 있습니다. 커서가 결과 세트의 행으로 위치를 설정할 때 1 단계, 위치 지정. 2 단계, 검색은 FETCH라는 작업의 특정 행에서 데이터를 가져올 때입니다.
이 예에서 커서는 첫 번째 SELECT에서 반환 한 첫 번째 행으로 위치를 설정하고 @id 변수의 WHERE 조건과 일치하는 ProductID 값을 가져옵니다. 그런 다음 두 번째 SELECT는 변수 값을 사용하여. 다음 행을 가져옵니다. 이러한 작업은 더 이상 작업 할 행이 없을 때까지 반복됩니다.
마지막으로 CLOSE 구문은 현재 결과 집합을 해제하고 커서가 사용하는 행에서 잠금을 제거하고 DEALLOCATE는 커서 참조를 제거합니다.
데모 테이블은 약 1,000 개를 포함하는 상대적으로 작습니다. 500 행. 수백만 개의 행이있는 테이블을 반복해야한다면 상당한 시간이 걸리고 결과가 만족스럽지 않습니다.
커서에 다시 한 번 기회를주고 –LOCAL STATIC 행의 주석 처리를 제거하겠습니다. 커서 정의에 사용할 수있는 인수가 많이 있습니다.이 링크에 대한 자세한 내용은 CURSOR 인수이지만 지금은이 두 단어의 의미에 초점을 맞추겠습니다.
LOCAL 키워드를 지정할 때 커서의 범위는 커서가 생성 된 배치에 국한되며이 범위에서만 유효합니다. 일괄 처리 실행이 완료되면 커서가 자동으로 할당 해제됩니다. 또한 커서는 저장 프로 시저, 트리거 또는 일괄 처리의 로컬 커서 변수에 의해 참조 될 수 있습니다.
STATIC 키워드는 임시 테이블의 tempdb에서 커서가 사용하는 데이터의 임시 복사본을 만듭니다. . 여기에 몇 가지 문제가 있습니다. STATIC 커서는 읽기 전용이며 열린 시점의 데이터에서만 작동하기 때문에 스냅 샷 커서라고도합니다. 즉, 데이터베이스에서 사용하는 데이터 세트에 대한 변경 사항을 표시하지 않습니다. 커서.기본적으로 커서를 닫았다가 다시 열지 않는 한 커서가 열린 후에 만들어진 업데이트, 삭제 또는 삽입은 커서 결과 집합에 표시되지 않습니다.
이러한 인수를 사용하기 전에이를 숙지하고 필요에 맞는지 확인하십시오. 하지만 커서가 더 빠른지 살펴 보겠습니다. 결과는 다음과 같습니다.
12 초가 4 분 47 초보다 훨씬 낫지 만 위에서 설명한 제한 사항입니다.
이 인수 LOCAL READ_ONLY FORWARD_ONLY를 사용하여 구문을 실행하면 동일한 결과를 얻습니다. READ_ONLY FORWARD_ONLY 커서는 첫 번째 행에서 마지막 행까지만 스크롤 할 수 있습니다. STATIC 키워드가 누락 된 경우 커서는 동적이며 사용 가능한 경우 동적 계획을 사용합니다. 그러나 동적 계획이 정적 계획보다 나쁜 경우가 있습니다.
–LOCAL FAST_FORWARD의 주석을 해제하면 어떻게됩니까?
FAST_FORWARD는 READ_ONLY 및 FORWARD_ONLY 커서와 동일하지만 정적 또는 동적 계획 중에서 더 나은 계획을 선택할 수 있습니다.
LOCAL FAST_FORWARD는 유연성 때문에 최상의 선택 인 것 같습니다. 정적 또는 동적 계획 중에서 선택하지만 FORWARD_ONLY도 작업을 매우 잘 수행합니다. 이 두 가지 방법을 모두 사용하여 평균 12 초 만에 결과를 얻었지만 이러한 인수는 둘 중 하나를 선택하기 전에 제대로 테스트해야합니다.
동일한 결과를 얻는 방법은 여러 가지가 있습니다. 성능에 미치는 영향이 적습니다.
커서 대안
한 가지 방법은 JOIN을 사용하는 것이며 다음에 볼 수 있듯이 결과가 훨씬 더 좋습니다.
첫째, 통계 시간을 활성화하여 SQL Server 실행 시간을 측정하고 두 테이블 사이에 INNER JOIN을 만들어야합니다. 및. ProductID 열에 있습니다. EXECUTE 버튼을 눌렀을 때 커서 방식으로 04:47 시간에 비해 330ms 만에 동일한 결과를 얻었으며 얼굴에 미소를지었습니다.
|
1
2
3
4
5
6
7
8
9
|
AdventureWorks2012 사용
GO
SET STATISTICS TIME ON
SELECT * FROM Production.ProductInventory as pinv
INNER JOIN Production.Product as pp
ON pinv.ProductID = pp.ProductID
WHERE pp.DaysToManufacture < = 1
|
필요한 것을 얻기 위해 모든 행을 반복 할 필요가 없습니다. 더 이상 루프도없고 while 절도없고 반복도 없습니다. 대신 데이터 세트로 작업하여 원하는 것을 더 빨리 얻고 코드를 적게 작성합니다.
커서의 적절한 사용
이제 얼마나 많은 손상이 있는지 살펴 보았습니다. 커서를 사용할 수 있습니다. 사용할 수있는 예를 살펴 보겠습니다.
데이터베이스에서 특정 테이블에 대해서만 행의 크기와 수를 선택한다고 가정 해 보겠습니다. 이를 위해 information_schema.tables의 기준에 따라 모든 테이블 이름을 가져오고 CURSOR를 사용하여 각 테이블 이름을 반복하고 필요한 정보를 얻기 위해 한 번에 하나의 테이블 이름을 전달하여 sp_spaceused 저장 프로 시저를 실행합니다. .
동일한 AdventureWorks2012 데이터베이스를 사용하고 ‘Sales’라는 이름이 포함 된 Sales 스키마에서 모든 테이블을 가져옵니다. 반환 된 모든 테이블 이름에 대해 information_schema.tables의 모든 정보를보고 싶습니다.
아래에는 T-SQL 구문과 얻은 결과가 있습니다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
AdventureWorks2012 사용
GO
DECLARE @TableName VARCHAR (50)- “Sales”스키마의 테이블 이름
DECLARE @Param VARCHAR (50)- “sp_spaceused”프로 시저에 대한 매개 변수
DECLARE db_cursor CURSOR FOR
– “Sales”tabl 만 선택 es
select TABLE_NAME from information_schema.tables
WHERE TABLE_NAME like “% Sales %”and TABLE_TYPE = “BASE TABLE”
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @TableName
WHILE @@ FETCH_STATUS = 0
BEGIN
-변수의 각 테이블 이름을 연결하고 저장 프로 시저에 전달
SET @ Param = “Sales.”+ @ TableName
-한 번에 모든 테이블 이름에 대한 저장 프로 시저 실행
EXEC sp_spaceused @Param
FETCH NEXT FROM db_cursor INTO @TableName- 다음 테이블 이름을 가져옵니다.
END
CLOSE db_cursor
DEALLOCATE db_cursor
|
이것은 CURSOR가 한 번에 한 행씩 일부 데이터를 반복하여 필요한 결과를 가져 오는 데 도움이되는 한 가지 방법입니다.이 경우 커서는 성능에 영향을주지 않고 작업을 완료하고 사용하기 쉽습니다.
결론
거기에 있습니다. 커서를 사용할 때 좋은 것, 나쁜 것, 못생긴 것 등 몇 가지 예를 보여주었습니다. 대부분의 경우 JOINS, 심지어 WHILE 절, SSIS를 사용할 수 있습니다. 성능 출력에 미치는 영향을 줄이고 구문을 더 적게 작성하면서 동일한 결과를 더 빨리 얻을 수있는 패키지 또는 기타 대체 방법.
OLTP 환경과 처리 할 대규모 데이터 세트를 다룰 때 ‘CURSOR’라는 단어를 말해서는 안됩니다.
SQL에서는 생각하기보다는 데이터 세트에 대한 작업을 수행 할 때 생각하는 것이 좋습니다. 반복 또는 루프를 사용하여 프로그래밍 방식으로 사용하는 것이 좋습니다. 이런 종류의 접근 방식은이 용도로 권장되거나 의도되지 않았기 때문입니다. 프로그래밍 언어에서 FOR 또는 FOREACH와 같은 루프를 사용하고 해당 논리를 SQL 작업과 연결하려는 것은 우리의 요구에 맞는 솔루션을 얻는 데 장애물입니다. 우리는 필요한 데이터를 얻기 위해 한 번에 한 행이 아닌 집합 기반 작업을 생각해야합니다.
커서는 위의 예와 같이 직렬화 된 작업을 위해 일부 응용 프로그램에서 사용할 수 있지만 일반적으로 그래야합니다. 특히 많은 양의 데이터를 작업 할 때 성능에 부정적인 영향을 미치므로 피해야합니다.
- 작성자
- 최근 게시물
컴퓨터 공학 학사 학위를 받았으며 .NET, C # 및 Windows Server에 대한 기술도 보유하고 있습니다. 성능 튜닝 및 모니터링, T-SQL 개발, 백업 전략, 관리 및 데이터베이스 구성에 대한 경험을 쌓았습니다.
나는 IT에 대한 열정, 비디오 게임, 좋은 영화, 일렉트로 하우스 음악이 사무실에서 일하는 데 도움이됩니다.
Sergiu Onet의 모든 게시물보기
- SQL Server 커서 사용 – 장점 및 단점-2016 년 3 월 23 일
