본문 바로가기

Develop/MSSQL28

SPOOL 연산자 성능 개선 - 3 1. 사전구성 SELECT a.number id , NEWID() txt , DATEADD(dd, a.number-1, '2020-01-01') date INTO TB_FSpool04 FROM master..spt_values a WHERE a.type = 'P' AND a.number BETWEEN 1 AND 100 ; CREATE CLUSTERED INDEX CIDX_FSpool04 ON TB_FSpool04 (date); SELECT ROW_NUMBER () OVER (ORDER BY a.number) id , CHAR(LEFT(CONVERT(NVARCHAR(4), high), 1) +64) code , RIGHT(number * high * low, 2) value INTO TB_SSpool04 .. 2022. 11. 30.
SPOOL 연산자 성능 개선 - 2 1. 사전구성 SELECT a.number num , CHAR(LEFT(b.high, 1) + 64) txt , LEFT(a.low, 1) group_id INTO TB_FSpool03 FROM master..spt_values a, master..spt_values b WHERE a.type = 'P' AND a.number BETWEEN 1 AND 100 AND b.type = 'P' AND b.number BETWEEN 1 AND 100 ; CREATE INDEX NIDX01_FSpool03 ON TB_FSpool03 (group_id) INCLUDE (num, txt); table_name index_name index_description Is_Unique indexed_columns inclu.. 2022. 11. 24.
NOT IN 조건으로 사용된 서브쿼리 성능 이슈 1. 사전구성 SELECT c.id , CASE WHEN c.id 2022. 11. 22.
SPOOL 연산자 성능 개선 - 1 1. 사전구성 SELECT a.number id , NEWID() txt , DATEADD(dd, a.number-1, '2020-01-01') date INTO TB_FSpool02 FROM master..spt_values a, master..spt_values b WHERE a.type = 'P' AND a.number BETWEEN 1 AND 10 AND b.type = 'P' AND b.number BETWEEN 1 AND 5 ; CREATE CLUSTERED INDEX CIDX_FPool02 ON TB_FSpool02 (date); SELECT ROW_NUMBER () OVER (ORDER BY a.number) id , CAST(LEFT(a.number*b.high, 2) AS INT) va.. 2022. 11. 21.
IN 조건절 성능 이슈 1. 사전구성 CREATE TABLE TB_ORFirst (seq INT, date DATETIME); CREATE TABLE TB_ORSecond (seq INT, gid INT, txt NVARCHAR(36) DEFAULT (NEWID() ) ); INSERT INTO TB_ORFirst SELECT ROW_NUMBER() OVER (ORDER BY a.number) seq , DATEADD(d, a.number, '2020-01-01') date FROM master..spt_values a (NOLOCK) , (SELECT number FROM master..spt_values (NOLOCK) WHERE type = 'P' AND number 2022. 11. 20.
내부 조인 스캔 범위 개선 (EXISTS) 1. 사전 구성 SELECT *, CONVERT(INT, RIGHT(num,1)) AS code INTO TB_DATA11 FROM (SELECT ROW_NUMBER() OVER (ORDER BY A.number) AS num , LEFT(CONVERT(CHAR(8), DATEADD(HH, A.number, '2020-01-01'), 112), 6) AS date FROM master..spt_values A , (SELECT * FROM master..spt_values B WHERE type = 'P' AND number '20200101' GROUP BY A.code ORDER BY A.code ; 최종 행 수가 7건 뿐임에도 [TB_DATA11] 테이블과 내부 조인이 수행되면서 약 45만건의 행을.. 2022. 11. 17.
CROSS JOIN 반복 사용 테이블 통합 1. 사전 구성 SELECT ROW_NUMBER () OVER (ORDER BY a.number) seq , CONVERT(INT, RIGHT(a.number*b.number*a.high, 2)) AS value , NEWID() txt , DATEADD(dd, b.number, '2020-01-01') date INTO TB_Sum FROM master..spt_values a, master..spt_values b WHERE a.type = 'P' AND b.type = 'P' AND a.number BETWEEN 1 AND 1000 AND b.number BETWEEN 1 AND 1000 ; CREATE CLUSTERED INDEX CIDX_CNT ON TB_Sum(date) ; 2. AS-IS S.. 2022. 11. 16.
CORSS APPLY 부분 범위 처리 1. 사전 구성 SELECT number id , CASE WHEN number 2022. 11. 14.
Show Me The Index 😁 SELECT '[' + s.NAME + '].[' + o.NAME + ']' AS 'table_name' , i.NAME AS 'index_name' , LOWER(i.type_desc) + CASE WHEN i.is_unique = 1 THEN ', unique' ELSE '' END + CASE WHEN i.is_primary_key = 1 THEN ', primary key' ELSE '' END AS 'index_description' , i.is_unique AS 'Is_Unique' , STUFF(( SELECT ', [' + sc.NAME + ']' AS "text()" FROM syscolumns AS sc INNER JOIN sys.index_columns AS ic ON ic.objec.. 2022. 10. 1.
MSSQL 통계 업데이트 & 인덱스 리빌딩 1. MSSQL 모든테이블 통계 업데이트 DECLARE @SQL VARCHAR(5000) SELECT @SQL = '' DECLARE TAB_CUR CURSOR FOR SELECT A = 'UPDATE STATISTICS ' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' OPEN TAB_CUR FETCH NEXT FROM TAB_CUR INTO @SQL WHILE(@@FETCH_STATUS = 0) BEGIN EXEC (@SQL) FETCH NEXT FROM TAB_CUR INTO @SQL END CLOSE TAB_CUR DEALLOCATE TAB_CUR 2. 인덱스 리빌드 1) 이유 조각화는 디스크상에 페이지들이 .. 2022. 10. 1.

티스토리툴바