[MSSQL] NL JOIN(Nested Loops Join)

 

기본 매커니즘 

 

중첩루프(Nested Loops) 조인은 가장 기본적인 조인 방식이며, 

 

단어가 의미하는 것처럼 반복적으로 안쪽 테이블을 탐색한다. 

여기서 바깥쪽에 있는 테이블을 outer table 이라고 부르고

 

안쪽에 있는 테이블을 inner table 이라고 부른다. 

즉, outer table 을 기준으로 inner table 을 반복적으로 탐색하는 방식이라고 생각하면 된다

 

 

 

 

NL JOIN의 세부처리

 

1. 결합 대상 테이블 (TABLE A)에서 레코드를 하나씩 반복해가며 스캔. 

 

이 테이블을 구동 테이블(driving table) 또는 외부 테이블(outer table)이라고 부른다. 

 

다른 테이블(TABLE B)는 내부 테이블(inner table)이라고 부른다.

 

2. 구동 테이블의 레코드 하나마다 내부 테이블의 레코드를 하나씩 스캔해서 결합 조건에 맞으면 리턴.


3. 이러한 작동은 구동 테이블의 모든 레코드에 반복.

 

 

 

 

다음 2개의 테이블을 사용하여 NESTED LOOP JOIN의 예시를 들어보자.

현재 두 테이블 모두 insaseq 에 대한 pk 값을 지니고 있다.

 

 

 

SELECT 
	tsf.insaseq
,	tsf.name
,	tsf.ssn
,	tsfl.svc_nation_cd
,	tsfl.kind
FROM dbo.TBLINSA_SH_FINAL tsf WITH(NOLOCK)
INNER LOOP JOIN dbo.TBLINSA_SH_FINAL_LOG tsfl WITH(NOLOCK) ON tsf.insaseq = tsfl.insaseq

 

 

실행계획의 첫 번째 줄에 나타난 것처럼, 

 

옵티마이저는 NL방식으로 조인을 수행하려고 한다. (LOOP JOIN HINT 를 쓴 결과)

위 실행계획에서 OUTER TABLE 은 TBLINSA_SH_FINAL 테이블이고 

 

INNER TABLE 은 TBLINSA_SH_FINAL_LOG 테이블이다.



실행계획을 보면 OUTER TABLE 에 해당하는 TBLINSA_SH_FINAL 테이블을 스캔하면서 

 

insa_seq 컬럼 값으로 INNER TABLE에 해당하는 TBLINSA_SH_FINAL_LOG 테이블의 

 

PK 인덱스인 PK__TBLINSA_SH_FINAL_LOG__INSASEQ 를 탐색(SEEK)한다.



다른 프로그램으로 코딩을 하다가 SQL 을 처음 접해본 개발자들은 

 

위와 같은 JOIN 알고리즘 자체를 왜 써야하는지 의문이 들 수도 있다.  

 

예를들어 아래와 같이 JOIN 대신에 CURSOR 를 써주면 개발자들은 기능을 좀 더 명확하게 인지 할 수 있다. 

 

 

		DECLARE 	@insaseq INT 
		,		@name NVARCHAR(40) 
		,		@ssn VARCHAR(14) 
		,		@svc_nation_cd CHAR(2)
		,		@kind CHAR(1)
        
		DECLARE C_TBLINSA_SH_FINAL CURSOR FOR 
		SELECT insaseq,name,ssn FROM dbo.TBLINSA_SH_FINAL WITH(NOLOCK) 

		OPEN C_TBLINSA_SH_FINAL 
		FETCH NEXT FROM C_TBLINSA_SH_FINAL 
		INTO @insaseq,@name,@ssn

		WHILE(@@FETCH_STATUS = 0) 
		BEGIN 
			SELECT 
				@svc_nation_cd = svc_nation_cd
			,	@kind = kind 
			FROM dbo.TBLINSA_SH_FINAL_LOG WITH(NOLOCK) 
			WHERE insaseq = @insaseq

			SELECT @insaseq,@name,@ssn,@svc_nation_cd,@kind

			FETCH NEXT FROM C_TBLINSA_SH_FINAL 
			INTO @insaseq,@name,@ssn
		END 

        	CLOSE C_TBLINSA_SH_FINAL 
		DEALLOCATE C_TBLINSA_SH_FINAL

 

 

렇게 절차형 코드로 구현하더라도, 테이블 액세스 순서뿐만 아니라 결과 집합까지 

 

NL JOIN 하도록 구현한  쿼리의 결과집합이랑 동일하다. 

 

또한 어떠한 로직인지 이해하는 데에 있어서 그저 JOIN을 사용하였을 때보다 더욱 알기가 쉽다.

(WHILE 문을 사용하면서 서로 돌아가면서 비교해주는구나~ 라고)



그렇다면 왜 위의 CURSOR 대신에 NL JOIN을 사용할까?

답은 간단하다. 

 

JOIN을 쓰는 방식이 CURSOR 를 쓰는 방식보다 성능이 훨씬 좋기 때문이다.

위와 같이 CURSOR 를 쓰레되면 SQL 수행횟수에서 큰 낭비가 발생한다. 



CURSOR 에서 두 테이블을 비교하기 위해 insaseq 컬럼 값을 추출하려면 

 

TBLINSA_SH_FINAL 테이블에서 추출된 로우 수만큼 반복해서 수행해야 한다.

 

예를들어 OUTER 집합이 10000건이라면 INNER 쪽을 10000회 호출해야한다.

위의 CURSOR 에서는 아래의 쿼리를 OUTER 테이블의 건수만큼 반복시켜야한다.

 

 

 

SELECT 
	@svc_nation_cd = svc_nation_cd
,	@kind = kind 
FROM dbo.TBLINSA_SH_FINAL_LOG WITH(NOLOCK) 
WHERE insaseq = @insaseq

그러나, JOIN 방식을 사용하면 위와 같은 쿼리를 반복적으로 수행하는게 아니라 

 

PK 인덱스만 반복해서 탐색시켜준다.

위 쿼리를 반복적으로 수행하는 것과 조인을 통해 PK인덱스를 탐색하는 것의 성능 차이는 어마어마하다.

수십 건 정도의 데이터를 처리해서는 차이를 느끼기 어렵지만, 

 

수만 건 이상의 데이터를 처리해 보면 속도 차이를 확실히 느낄 수 있다.



NL 조인을 사용한 방법과 커서를 쓴 쿼리의 성능을 비교해보자

 

 

1) NL JOIN 문법을 사용하여 쿼리한 경우

SELECT 
	tsf.insaseq
,	tsf.name
,	tsf.ssn
,	tsfl.svc_nation_cd
,	tsfl.kind
FROM dbo.TBLINSA_SH_FINAL tsf WITH(NOLOCK)
INNER LOOP JOIN dbo.TBLINSA_SH_FINAL_LOG tsfl WITH(NOLOCK) ON tsf.insaseq = tsfl.insaseq

 

 

2) CURSOR를 사용하여 쿼리한 경우

		DECLARE 	@insaseq INT 
		,		@name NVARCHAR(40) 
		,		@ssn VARCHAR(14) 
		,		@svc_nation_cd CHAR(2)
		,		@kind CHAR(1)
        
		DECLARE C_TBLINSA_SH_FINAL CURSOR FOR 
		SELECT insaseq,name,ssn FROM dbo.TBLINSA_SH_FINAL WITH(NOLOCK) 

		OPEN C_TBLINSA_SH_FINAL 
		FETCH NEXT FROM C_TBLINSA_SH_FINAL 
		INTO @insaseq,@name,@ssn

		WHILE(@@FETCH_STATUS = 0) 
		BEGIN 
			SELECT 
				@svc_nation_cd = svc_nation_cd
			,	@kind = kind 
			FROM dbo.TBLINSA_SH_FINAL_LOG WITH(NOLOCK) 
			WHERE insaseq = @insaseq

			SELECT @insaseq,@name,@ssn,@svc_nation_cd,@kind

			FETCH NEXT FROM C_TBLINSA_SH_FINAL 
			INTO @insaseq,@name,@ssn
		END 

        	CLOSE C_TBLINSA_SH_FINAL 
		DEALLOCATE C_TBLINSA_SH_FINAL

 

위와 같이 NL JOIN 을 사용하였을 때에는 

 

정상적으로 실행계획이 하나가 나오면서 끝나지만,

CURSOR 를 사용했을 경우에는 수만건의 실행계획이 나오면서 

 

26초가 지나도 해당 실행문이 끝나지 않는다.



즉, SQL 문을 다룰때는 CURSOR 을 쓸 일이 있더라도 

 

성능을 위해서 NL JOIN을 대신 써주는것이

가독성과 DBMS 성능을 위해서도 더욱 이점이 많다.