[MSSQL] UPDATE SELECT 구문 쿼리 튜닝(성능 및 속도 개선)

update를 진행 할 경우 임시테이블을 사용해서 성능 개선이 가능하다

아래의 예제를 통해서 비용차이를 확인해보자

 

use INDEX_TEST

GO

 

테이블 생성

CREATE TABLE OrderT(
주문번호 int
,상품 varchar(100)
,주문일자 datetime,
 CONSTRAINT PK_OrderT_주문번호_상품 PRIMARY KEY CLUSTERED
(
주문번호 ASC,
상품 ASC
)
)
GO

CREATE TABLE OrderT2(
주문번호 int
,상품 varchar(100)
,주문일자 datetime,
 CONSTRAINT PK_OrderT2_주문번호_상품 PRIMARY KEY CLUSTERED
(
주문번호 ASC,
상품 ASC
)
)
GO

 

데이터 입력

--100,000개의 데이터를 입력

DECLARE @i INT

DECLARE @i2 INT

DECLARE @Lastnum INT

DECLARE @date datetime

SET @i = 1

SET @i2 =1

SET @Lastnum = 100000

SET @date = '2023-03-01 00:00:00.000'



WHILE @i<@Lastnum

BEGIN

insert into OrderT values (@i,'꿀'+convert(varchar(100), @i2),@date+@i)

insert into OrderT2 values (@i,'꿀'+convert(varchar(100), @i2),@date+@i)

SET @i = @i+1 --숫자 더하기



IF @i=95000

SET @i2 = @i2+4999 --숫자 더하기



END;

GO

 

데이터 확인

select * from OrderT
select * from OrderT2
GO

2개의 테이블에서 데이터를 변경할 부분이다.

---

임시테이블 생성 및 업데이트할 데이터 입력

임시테이블을 사용하면 테이블의 행수를 줄일 수 있다.

CREATE TABLE #Temp_OrderT(
주문번호 int
,상품 varchar(100)
,주문일자 datetime,
 CONSTRAINT PK_OrderT_주문번호_상품 PRIMARY KEY CLUSTERED
(
주문번호 ASC,
상품 ASC
)
)
GO

insert into #Temp_OrderT select * from OrderT  where 주문일자 between '2296-12-01 00:00:00.000' and '2296-12-13 00:00:00.000'

 

--데이터 확인
select * from  #Temp_OrderT

I/O 비용 및 실행계획 활설화

I/O 비용 : SET STATISTICS IO ON

실행계획 : Ctrl + M

 

비용확인

2개의 쿼리를 동시에 실행해서 비용 차이를 확인해 보자

update OrderT set 상품='변경된꿀' from OrderT a inner join OrderT2 b on a.주문번호=b.주문번호  where a.주문일자 between '2296-12-01 00:00:00.000' and '2296-12-13 00:00:00.000'

GO

 

테이블 'OrderT'. 스캔 수 1, 논리적 읽기 402, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

테이블 'Worktable'. 스캔 수 0, 논리적 읽기 0, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

테이블 'OrderT2'. 스캔 수 13, 논리적 읽기 26, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

 

update OrderT2 set 상품='변경된꿀' from OrderT2 a inner join #Temp_OrderT b on a.주문번호=b.주문번호

테이블 'OrderT2'. 스캔 수 13, 논리적 읽기 78, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

테이블 'Worktable'. 스캔 수 0, 논리적 읽기 0, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

테이블 '#Temp_OrderT_000000000026'. 스캔 수 1, 논리적 읽기 2, 실제 읽기 0, 페이지 서버 읽기 0, 미리 읽기 읽기 0, 페이지 서버 미리 읽기 읽기 0, lob 논리적 읽기 0, lob 실제 읽기 0, lob 페이지 서버 읽기 0, lob 미리 읽기 읽기 0, lob 페이지 서버 미리 읽기 읽기 0.

 

실행계획의 비용 비교를 보면 임시테이블에 넣고 실행한 실행계획 비용이 5%로 원본테이블을 가지고 업데이트 하는 것 보다 많은 차이가 나는 것을 볼 수 있다.

테이블에 업데이트 된 데이터 확인

OrderT테이블과 OrderT2 데이터가 변경된 것을 확인 할 수 있다