[COLUMNSTORE INDEX] SQL 프로파일러 이용하여 성능 테스트

테이블 생성하고

CREATE TABLE COLUMTABLE(
	IDX INT NOT NULL,
	CDATA INT NULL,
	ODATA INT NOT NULL,
	DATA1 VARCHAR(20) NULL,
	DATA2 VARCHAR(20) NULL,
	DATA3 VARCHAR(20) NULL,
	DATA4 VARCHAR(20) NULL,
	DATA5 VARCHAR(20) NULL,
	DATA6 INT NULL,
	DATA7 INT NULL,
	DATA8 INT NULL,
	DATA9 INT NULL,
	DATA10 INT NULL,
	DATA11 INT NULL,
	DATA12 INT NULL,
	DATA13 INT NULL,
	DATA14 INT NULL,
	DATA15 INT NULL,
	DATA16 FLOAT NULL,
	DATA17 FLOAT NULL,
	DATA18 FLOAT NULL,
	DATA19 FLOAT NULL,
	DATA20 FLOAT NULL

);


DROP TABLE COLUMTABLE

 

 

 

데이터를 넣고

DECLARE @COUNT INT	--INSERT 14:25
SET @COUNT = 1
-- SELECT @COUNT = 1
WHILE @COUNT < 5000000
BEGIN
	INSERT INTO [dbo].[COLUMTABLE]
	VALUES(
		@COUNT,
		CAST
			(CONVERT
				(VARCHAR,
					DATEADD(DAY,ABS(CHECKSUM(NEWID()) % 365)
					,'2020-11-24'),112)AS INTEGER),


		CAST
			(CONVERT
				(VARCHAR,
					DATEADD(DAY,ABS(CHECKSUM(NEWID()) % 365)
					,'2020-11-24'),112)AS INTEGER),

		'TEST' + CAST(ROUND(RAND()*100,0)AS VARCHAR),
		'TEST' + CAST(ROUND(RAND()*100,0)AS VARCHAR),
		'TEST' + CAST(ROUND(RAND()*100,0)AS VARCHAR),
		'TEST' + CAST(ROUND(RAND()*100,0)AS VARCHAR),
		'TEST' + CAST(ROUND(RAND()*100,0)AS VARCHAR),

		ROUND(RAND()*100000,0),
		ROUND(RAND()*100000,0),
		ROUND(RAND()*100000,0),
		ROUND(RAND()*100000,0),
		ROUND(RAND()*100000,0),
		ROUND(RAND()*100000,0),
		ROUND(RAND()*100000,0),
		ROUND(RAND()*100000,0),
		ROUND(RAND()*100000,0),
		ROUND(RAND()*100000,0),

		ROUND(RAND()*100000,2),
		ROUND(RAND()*100000,2),
		ROUND(RAND()*100000,2),
		ROUND(RAND()*100000,2),
		ROUND(RAND()*100000,2)

	)
	SET @COUNT = @COUNT + 1
	-- SELECT @COUNT = @COUNT + 1
END
GO

 

 

 

 

 

SELECT * 로 하는데 걸린시간 측정

SELECT * FROM COLUMTABLE	-- 1:12

 

 

 

데이터 하나하나 찍어 걸린시간 측정(원래 4초정도 더 적게나옴)

SELECT		-- 1:12
	IDX,
	CDATA,
	ODATA,
	DATA1, DATA2, DATA3,DATA4,DATA5,
	DATA6, DATA7, DATA8,DATA9,DATA10,
	DATA11, DATA12, DATA13,DATA14,DATA15,
	DATA16, DATA17, DATA18,DATA19,DATA20
FROM COLUMTABLE

 

 

 

 

인덱스 생성

 

NONCLUSTERED INDEX

CREATE NONCLUSTERED INDEX
   IX_ColumTable_NON
ON
   ColumTable (data1, data16);
GO

 

NONCLUSTERED COLUMNSTORE INDEX

CREATE NONCLUSTERED COLUMNSTORE INDEX
   IX_ColumTable_ColStore
ON
   ColumTable (data1, data16);
GO

 

 

 

 

검색 성능 비교해보기

 

-- NONCLUSTERED INDEX 검색

SELECT DATA1, AVG(DATA16)
FROM ColumTable
GROUP BY DATA1
-- 힌트사용
OPTION (TABLE HINT(COLUMTABLE, INDEX(IX_COLUMTABLE_NON)))

 

 

 

-- NONCLUSTERED COLUMNSTORE INDEX 검색

SELECT DATA1, AVG(DATA16)
FROM ColumTable
GROUP BY DATA1

 

 

 

 

 

결과: 위(NONCLUSTERED INDEX) 아래(NONCLUSTERED COLUMNSTORE INDEX)

 

 

 

 

NONCLUSTERED COLUMNSTORE INDEX 가

NONCLUSTERED INDEX보다 성능이 훨 좋다!

 

 

 

 

 

---

 

columnstore 인덱스란?

Columnstore 인덱스는 columnstore 라는 칼럼 데이터 서식을 사용하여 데이터를 저장, 검색, 관리하는 기술입니다

 

 

columnstore

columnstore는 열과 행이 있는 테이블로 논리적으로 구성되는 데이터로, 열 데이터 서식으로 물리적으로 저장됩니다

 

 

Columnstore 인덱스를 사용해야 하는 이유

Columnstore 인덱스는 매우 높은 수준의 데이터 압축(일반적으로 10배)을 제공하여 데이터 웨어하우스 스토리지 비용을 크게 줄일 수 있습니다. 분석을 위해 columnstore 인덱스는 btree 인덱스보다 몇 배 더 나은 성능을 제공합니다. Columnstore 인덱스는 데이터 웨어하우징 및 분석 작업에 대한 기본 설정된 데이터 스토리지 형식입니다. SQL Server 2016(13.x)부터 운영 워크로드에 대한 실시간 분석에 columnstore 인덱스를 사용할 수 있습니다.

Columnstore 인덱스가 빠른 이유는 다음과 같습니다.

• 열이 동일한 도메인의 값을 저장하고 일반적으로 유사한 값을 가지므로 압축 비율이 높습니다. 시스템의 I/O 병목 상태가 최소화되거나 제거되며 메모리 사용 공간이 크게 감소합니다.

• 압축 비율이 높으면 메모리 내 사용 공간이 감소되어 쿼리 성능이 향상됩니다. 또한 SQL Server가 더 많은 쿼리 및 데이터 작업을 메모리 내에서 수행할 수 있으므로 쿼리 성능도 개선할 수 있습니다.

• 일괄 처리 실행은 여러 행을 함께 처리하여 쿼리 성능을 개선합니다(일반적으로 2~4배).

• 쿼리는 대개 테이블에서 적은 수의 열만 선택하므로 실제 미디어의 총 I/O가 감소됩니다.