안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 9-3. 커서 - 현업에서 커서 사용 및 주의사항을 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번에 진행할 강좌는 현업에서 커서 사용입니다.
TL;DR
현업의 커서 사용 패턴에 대해 다루며, 커서가 배치성 작업에서 어떻게 사용되는지 설명합니다. 주의사항으로는 배치성 작업에서 커서가 잠금과 블로킹을 유발할 수 있으므로 사용자가 적은 시간대에 수행하는 것이 좋으며, 커서를 남용하지 않도록 주의합니다.
커서는 생각보다 현업에서 많이 사용됩니다. 이런 배치성(Batch style) 작업을 수행할 경우가 종종 있기 때문입니다. 특히, 시스템 카탈로그나 테이블 간 연동 작업 등에 유용합니다. 몇몇 사례를 소개해 드립니다.
로그성 테이블에서 사용자 중요 데이터를 커서로 복구
게임 서버로 SQL Server를 운영하다가, 운영자의 실수로 하루 전 백섭(서버를 특정 시점으로 되돌림)을 하게 되었고, 하루 동안의 사용자의 주요 포인트(또는 중요 아이템) 획득을 다른 로그 테이블을 보고 맞춰줘야 합니다.
-- 회원 포인트 테이블
CREATE TABLE MemberPoint(
mem_point_idx INT IDENTITY(1,1) PRIMARY KEY
, member_id NVARCHAR(20)
, member_point INT
);
INSERT INTO MemberPoint(member_id, member_point)
VALUES (N'김대우', 100),
(N'박은빈', 200),
(N'손석구', 300);
SELECT * FROM MemberPoint;
GO
-- 회원 포인트 획득 로그 테이블
CREATE TABLE MemberPointLog(
mem_point_log_idx INT IDENTITY(1,1) PRIMARY KEY
, member_id NVARCHAR(20)
, member_point INT
, member_point_event NVARCHAR(20)
, member_point_log_dt DATETIME DEFAULT GETDATE()
);
INSERT INTO MemberPointLog(member_id, member_point, member_point_event, member_point_log_dt)
VALUES (N'김대우', 110, N'보스1 격파', '2023-07-28'),
(N'김대우', 130, N'보스2 격파', '2023-07-29'),
(N'손석구', 350, N'보스5 격파', '2023-07-28'),
(N'손석구', 400, N'보스6 격파', '2023-07-29');
SELECT * FROM MemberPointLog;
GO
-- 개별 회원의 1일간(7월 28일~29일)의 포인트 로그에서 max값을 가져옴.
SELECT member_id, max(member_point) as final_member_point
FROM MemberPointLog
WHERE member_point_log_dt BETWEEN '2023-07-28' AND '2023-07-29'
GROUP BY member_id;
GO
-- 위의 쿼리로 커서를 실행하고, 회원포인트 테이블을 업데이트
위와 같이 회원포인트 테이블과 회원포인트로그 테이블을 생성합니다. 로그 테이블로부터 회원포인트 테이블로 업데이트해야 합니다.
커서 실행 및 회원포인트 테이블로 업데이트
-- 커서 구문 실행
SET NOCOUNT ON;
--커서 선언
DECLARE curMemberPointUpdate CURSOR FAST_FORWARD
FOR
SELECT member_id, max(member_point) as final_member_point
FROM MemberPointLog
WHERE member_point_log_dt BETWEEN '2023-07-28' AND '2023-07-29'
GROUP BY member_id;
--커서 오픈
OPEN curMemberPointUpdate
--변수 선언
DECLARE @member_id NVARCHAR(20)
DECLARE @final_member_point INT
--첫 로우 FETCH
FETCH NEXT FROM curMemberPointUpdate INTO @member_id, @final_member_point
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE MemberPoint SET member_point = @final_member_point
WHERE member_id LIKE @member_id
--다음 로우 FETCH - 루프
FETCH NEXT FROM curMemberPointUpdate INTO @member_id, @final_member_point
END
--커서 CLOSE
CLOSE curMemberPointUpdate;
--커서 DEALLOCATE
DEALLOCATE curMemberPointUpdate;
GO
-- 완료 후 회원포인트 테이블 UPDATE 확인
SELECT * FROM MemberPoint;
GO
이런 형태로 현업에서 다양하게 이용할 수 있습니다.
커서 사용 주의사항
배치성 작업
커서는 배치성 작업으로 대부분의 경우 로우 단위(행 단위)로 작업(로우의 수만큼 쿼리 작업)이 이루어지는, 시간과 리소스를 많이 사용하는 작업입니다. 일반 작업에 커서와 리소스 경합으로 잠금(Lock)이나 블로킹(Blocking)이 발생해 실행에 영향이 클 수 있으니, 커서 배치 작업은 가능한 사용자가 적은 시간대에 수행하는 것이 좋습니다.
커서 남용 주의
개발자가 오랜 시간 프로그래밍을 했던 경험으로 로우 단위 작업에 익숙할 경우, 프로그래밍 언어의 DB 라이브러리와 같은 콘셉트로 커서를 이해해, 이를 남용하는 경우가 있습니다. 애플리케이션 개발자에게는 세트(Set) 기반 작업인 SQL 쿼리보다 이런 로우 단위 작업이 더 익숙하기 때문입니다.
☑️ 챗GPT 활용: python 데이터셋과 커서를 사용한 로우 단위 작업 예제 알려줘
최초 MVP 개발에서는 테스트 데이터 수십 수백 건 대상 1초 미만으로 처리를 완료해 커서가 충분히 빠르다는 생각으로, 일반 SQL 구문이 아니라 커서를 동시 다수 사용자가 실행하는 루틴이나 ad-hoc(자주 실행하는 일반 처리 패턴)으로 사용하면, 이후 수만, 수백만 건 데이터가 적재되면서 심각한 성능 문제가 발생하게 됩니다.(커서는 모든 행에 대해 작업을 수행합니다.)
일반적인 동시 다수 사용자 애플리케이션의 기능이나 ad-hoc으로 커서를 사용해야 한다면, 데이터베이스 스키마 디자인 변경을 심각하게 고려하는 것이 좋습니다.
커서는 배치성 작업에만 사용하고, 애플리케이션 기능으로 사용하지 마시길 바랍니다.
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.
책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022
