MS SQL 임시테이블-임시테이블을 많이 쓰면 느려진다.??

MS SQL 임시테이블

임시테이블을 사용하는 이유

쿼리내에 재사용 쿼리가 빈번히 발생될 때 사용합니다.

 

임시테이블 생성

방법 1. with 임시테이블명 as (  SELECT A.A, A.B, B.C, B.D                               FROM TABLE A                               INNER JOIN TABLE B ON A.A = B.A )

방법 2. SELECT A,B,C,D INTO #TEMP FROM  TABLE A INNER JOIN TABLE B ON A.A = B.A

방법 3. CREATE TABLE #TEMP   (  MATERIAL_CODE NVARCHAR(20) COLLATE Korean_Wansung_CI_AS      ,MATERIAL_NAME NVARCHAR(200) COLLATE Korean_Wansung_CI_AS      ,BUY_IDX             BIGINT      ,CUSTOMER_NORMAR NVARCHAR(50) COLLATE Korean_Wansung_CI_AS      ,GORSS_WEIGHT DECIMAL(18, 4)     )   SELECT A,B,C,D INTO #TEMP FROM  TABLE A INNER JOIN TABLE B ON A.A = B.A

임시테이블 삭제

OBJECT_ID 를 이용하여 임시테이블 존재 여부를 확인 후 삭제 하도록 합니다. 없는데 삭제를 하면 없거나 권한이 없어 삭제 할 수 없다며 에러 발생할 테니까요. IF OBJECT_ID('#TEMP') IS NOT NULL    DROP TABLE #TEMP

임시테이블 변수 생성

임시테이블 변수는 데이터 량이 적을때 명령실행 후 삭제 되므로 별도의 삭제문장은 필요하지 않습니다. DECLARE @TABLE TABLE  ( ID INT IDENTITY(1,1),  STYLE_CODE NVARCHAR(15) COLLATE Korean_Wansung_CI_AS, TOTAL_QTY DECIMAL(18, 0) ) INSERT INTO @TABLE  (STYLE_CODE ,TOTAL_QTY) SELECT STYLE_CODE, TOTAL_QTY FROM SALES_ORDER_STYLE WHERE ORDER_CODE = @ORDER_CODE AND ORDER_INDEX = @ORDER_INDEX AND DELETE_CHECK <> 'T'

 

 

임시테이블을 사용하는 경우 지켜야 할 규칙

1. 임시테이블도 WHERE 절로 최소화 해서 사용한다.

2. 인덱스를 활용할 수 있도록 쿼리한다. (네거티브조건절 안됨)

3. 캐시에 두는 것은 8M 미만이 되도록 해야 한다.

 

 

문제 1 : 필요하지 않은 임시 테이블 사용 임시 테이블

임시 테이블은 다양한 용도로 사용되지만 (나중에 사용하기 위해 중간 결과 집합을 저장하는 것이 가장 일반적 일 수 있음) 임시 테이블을 쿼리에 도입하면 데이터 흐름이 중단된다는 점을 기억해야합니다. 쿼리 프로세서.

 

Think of the population of a temporary table as a hard stop, as there's a query (let's call it the producer) to produce the intermediate result set, which is then stored in the temporary table in tempdb, and then the next query (let's call it the consumer) has to read the data from the temporary table again.

 

중간 결과 집합을 생성하기위한 쿼리 (생산자라고하자)가 임시 테이블의 모집단을 하드 스톱으로 생각한 다음 임시 테이블에 tempdb에 저장 한 다음 다음 쿼리 (콜을 호출) 소비자) 임시 테이블에서 데이터를 다시 읽어야합니다.

 

I've often found that some parts of a workload actually perform better when the temporary table is completely removed, so the data flows from the producer part of the query to the consumer part of the query without having to be persisted in tempdb, and the query optimizer can produce a more optimal overall plan.

 

나는 종종 임시 테이블이 완전히 제거 될 때 작업 부하의 일부가 실제로 더 잘 수행된다는 것을 알았습니다. 따라서 데이터는 tempdb에 유지되지 않고 쿼리의 생산자 부분에서 쿼리의 소비자 부분으로 흐릅니다. 쿼리 최적화 프로그램은보다 최적의 전체 계획을 생성 할 수 있습니다.

 

You might now be thinking, "so why would someone use a temporary table if it makes things slower?" – and rightly so! In cases like that, I've found that the use of a temporary table has become institutionalized in the development team; someone found that using a temporary table increased performance many years ago, so temporary tables became the default design choice.

 

당신은 지금 생각하고 있을지도 모릅니다. "왜 누군가가 테이블을 느리게 만들면 임시 테이블을 사용합니까?" – 그리고 그렇습니다! 그런 경우 개발 팀에서 임시 테이블 사용이 제도화되었다는 것을 알게되었습니다. 누군가는 임시 테이블을 사용하면 수년 전에 성능이 향상되어 임시 테이블이 기본 디자인 선택이 된 것을 발견했습니다.

 

This can be a hard thing to change, especially if you have a senior developer or manager who's convinced that temporary tables should always be used. The simple thing to try is to pick an expensive query (for instance, a long-running one, or one that's executed many times per second) and remove one or more of the temporary tables to see whether performance increases without them. And if so, there's your proof to show the intransigents!

 

임시 테이블을 항상 사용해야한다고 확신하는 선임 개발자 또는 관리자가있는 경우에는 변경하기가 어려울 수 있습니다. 가장 간단한 방법은 값 비싼 쿼리 (예 : 오래 실행되는 쿼리 또는 초당 여러 번 실행되는 쿼리)를 선택하고 하나 이상의 임시 테이블을 제거하여 쿼리없이 성능이 향상되는지 확인하는 것입니다. 그렇다면, 비 일시적인 사람들을 보여줄 증거가 있습니다!

 

Problem 2: Lack of Filtering When Populating Temporary Tables
Even if you can't remove a temporary table, you may be able to drastically improve performance by making sure that the code that populates the temporary table is correctly filtering the data pulled from source tables.

I've lost count of the number of times I've seen a temporary table being populated with code that starts as SELECT *, includes a few unrestrictive joins, and has no WHERE clause, and then the later query that uses the temporary table only uses a few columns and has a WHERE clause to hugely scope down the number of rows.

I remember one case where a temporary table in a stored procedure was aggregating 15 years' worth of data from the main database, and then only the current year's data was being used. This was repeatedly causing tempdb to grow until it ran out of space on the disk volume, and the stored procedure would then fail.

Whenever you're populating a temporary table, only use the source table columns that are necessary, and only use the rows that are necessary – i.e. push the filter predicates up into the temporary table population code. Not only will this save space in tempdb, it will also save a lot of time from not having to copy unneeded data from the source table (and potentially remove the need to read source database pages from disk in the first place).

 

문제 2 : 임시 테이블을 채울 때 필터링 부족
임시 테이블을 제거 할 수없는 경우에도 임시 테이블을 채우는 코드가 소스 테이블에서 가져온 데이터를 올바르게 필터링하는지 확인하여 성능을 크게 향상시킬 수 있습니다.

SELECT *로 시작하고 무제한 조인을 포함하고 WHERE 절이없는 임시 테이블 만 사용 된 코드로 채워진 임시 테이블 수를 잃어 버렸습니다. 몇 개의 열을 사용하고 WHERE 절을 사용하여 행 수를 크게 줄입니다.

저장 프로 시저의 임시 테이블이 주 데이터베이스에서 15 년 분량의 데이터를 집계 한 다음 현재 연도의 데이터 만 사용 된 사례를 기억합니다. 이로 인해 디스크 볼륨의 공간이 부족해질 때까지 tempdb가 계속 커져서 저장 프로 시저가 실패했습니다.

임시 테이블을 채울 때마다 필요한 소스 테이블 열만 사용하고 필요한 행만 사용하십시오. 즉, 필터 술어를 임시 테이블 채우기 코드로 푸시하십시오. 이렇게하면 tempdb의 공간이 절약 될뿐만 아니라 불필요한 소스 데이터를 소스 테이블에서 복사하지 않아도되므로 디스크에서 소스 데이터베이스 페이지를 읽을 필요가 없게됩니다.

 

 

문제 3 : 잘못된 임시 테이블 인덱싱
일반 테이블과 마찬가지로 나중에 쿼리 코드에서 쿼리 성능을 높이기 위해 실제로 사용할 인덱스 만 만들어야합니다. 임시 테이블 열당 클러스터되지 않은 인덱스가있는 경우를 많이 보았으며 이후 코드를 분석하지 않고 선택한 단일 열 인덱스는 종종 쓸모가 없습니다. 이제 임시 테이블을 채울 때 쓸모없는 비 클러스터형 인덱스와 필터링 부족을 결합하여 tempdb를 크게 부 풀릴 수있는 레시피를 얻었습니다.

또한 일반적으로 테이블이 채워진 후 인덱스를 만드는 것이 더 빠릅니다. 이를 통해 인덱스에 정확한 통계가 제공되므로 쿼리 최적화 프로그램이 정확한 카디널리티 추정을 수행 할 수 있으므로 쿼리에 도움이됩니다.

클러스터되지 않은 많은 인덱스를 사용하면 디스크 공간뿐만 아니라 인덱스를 만드는 데 필요한 시간도 낭비됩니다. 이것이 자주 실행되는 코드에있는 경우 코드가 실행될 때마다 생성되는 불필요한 인덱스를 제거하면 전체 성능에 큰 영향을 줄 수 있습니다.

문제 4 : tempdb 래치 경합
tempdb에 임시 테이블 사용으로 다시 추적 할 수있는 병목 현상이 발생하는 것이 일반적입니다. 임시 테이블을 생성 및 삭제하는 코드를 실행하는 많은 동시 연결이있는 경우 메모리에서 데이터베이스의 할당 비트 맵에 액세스하면 병목 현상이 발생할 수 있습니다.

This is because only one thread at a time can be changing an allocation bitmap to mark pages (from the temp table) as allocated or deallocated, and so all the other threads have to wait, decreasing the workload throughput. Even though there has been a temporary table cache since SQL Server 2005, it's not very large, and there are restrictions on when the temporary table can be cached (e.g. only when it's less than 8MB in size). 

Traditional ways to work around this problem have been to use trace flag 1118 and multiple tempdb data files (see this blog post for more info), but another thing to consider is to remove the temporary tables altogether!
한 번에 하나의 스레드 만이 할당 비트 맵을 변경하여 페이지를 할당 또는 할당 해제 된 것으로 표시 할 수 있으므로 다른 모든 스레드는 대기해야하므로 워크로드 처리량이 줄어 듭니다. SQL Server 2005 이후에 임시 테이블 캐시가 있었지만 그 크기는 크지 않으며 임시 테이블을 캐시 할 수있는시기 (예 : 크기가 8MB 미만인 경우)에 대한 제한이 있습니다.

이 문제를 해결하는 전통적인 방법은 추적 플래그 1118과 여러 tempdb 데이터 파일을 사용하는 것이었지만 (자세한 내용은이 블로그 게시물 참조) 임시 테이블을 모두 제거하는 것이 좋습니다.