복붙노트

[SQL] 왼쪽은 SQL Server의 성능을 가입 대 INNER는 가입

SQL

왼쪽은 SQL Server의 성능을 가입 대 INNER는 가입

내가 사용 INNER 9 개 테이블에 가입하는 것이 SQL 명령을 만들었습니다, 어쨌든이 명령은 매우 긴 시간 (5 분 이상) 소요됩니다. INNER 왼쪽으로 가입 변경하라고 제안 내 민속 그래서 왼쪽의 성능이 내가 아는에도 불구하고, 더 나은 가입하기 때문에 가입하세요. 내가 변경 한 후, 쿼리의 속도가 크게 향상되었다.

왼쪽은 내부 조인보다 빠른 가입 이유를 알고 싶습니다?

아래처럼 내 SQL 명령보기 : INNER FROM SELECT *는 C가 ON INNER JOIN을 ... B ON INNER 가입 ... D는 가입 등등

최신 정보: 이것은 내 스키마의 간단한이다.

FROM sidisaleshdrmly a -- NOT HAVE PK AND FK
    INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK
        ON a.CompanyCd = b.CompanyCd 
           AND a.SPRNo = b.SPRNo 
           AND a.SuffixNo = b.SuffixNo 
           AND a.dnno = b.dnno
    INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine
        ON a.CompanyCd = h.CompanyCd
           AND a.sprno = h.AcctSPRNo
    INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix
        ON c.CompanyCd = h.CompanyCd
           AND c.FSlipNo = h.FSlipNo 
           AND c.FSlipSuffix = h.FSlipSuffix 
    INNER JOIN coMappingExpParty d -- NO PK AND FK
        ON c.CompanyCd = d.CompanyCd
           AND c.CountryCd = d.CountryCd 
    INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd
        ON b.CompanyCd = e.CompanyCd
           AND b.ProductSalesCd = e.ProductSalesCd 
    LEFT JOIN coUOM i -- PK = UOMId
        ON h.UOMId = i.UOMId 
    INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd
        ON a.CompanyCd = j.CompanyCd
            AND b.BFStatus = j.BFStatus
            AND b.ProductSalesCd = j.ProductSalesCd
    INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd
        ON e.ProductGroup1Cd  = g1.ProductGroup1Cd
    INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd
        ON e.ProductGroup1Cd  = g2.ProductGroup1Cd

해결법

  1. ==============================

    1.왼쪽은 가입 빠르게 내부에 비해 절대적으로하지 않습니다 가입하세요. 사실, 그것은 느리다; 정의에 의해, 외측 (왼쪽 가입 시키거나 가입 RIGHT) 널 연장 결과 내부의 모든 작업 JOIN 더하여 추가 작업을 수행하는 가입. 또한 더 간단 인한 결과 집합의 더 큰 크기로 총 실행 시간을 증가, 더 많은 행을 반환 할 것으로 예상된다.

    왼쪽은 가입 빠르게 내부에 비해 절대적으로하지 않습니다 가입하세요. 사실, 그것은 느리다; 정의에 의해, 외측 (왼쪽 가입 시키거나 가입 RIGHT) 널 연장 결과 내부의 모든 작업 JOIN 더하여 추가 작업을 수행하는 가입. 또한 더 간단 인한 결과 집합의 더 큰 크기로 총 실행 시간을 증가, 더 많은 행을 반환 할 것으로 예상된다.

    (그리고 때문에 단순히 다른 하나의 인스턴스를 모두 교체 갈 수 있도록 일부는 어려운 상상 요인의 합류를, 그것이 내부와 동일한 기능을 JOIN하지 않습니다에 빠르게 특정 상황에서 있었다 왼쪽 가입해도!)

    대부분의 경우 성능 문제는 제대로 인덱스 후보 키 또는 외부 키를 가지고 있지으로, 다른 곳에서 거짓말. 경기 둔화가 말 그대로 거의 모든 곳이 될 수 있도록 9 개 테이블은 꽤 많은 참여해야한다. 당신이 당신의 스키마를 게시 할 경우, 우리는 더 많은 정보를 제공 할 수 있습니다.

    편집하다:

    이에 더 반영, 나는 LEFT 빠른 INNER이 조인보다 수도 가입, 그 때되고있는 하나 개의 상황을 생각할 수 있습니다 :

    이 예제를 생각해 봅시다 :

    CREATE TABLE #Test1
(
    ID int NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL
)
INSERT #Test1 (ID, Name) VALUES (1, 'One')
INSERT #Test1 (ID, Name) VALUES (2, 'Two')
INSERT #Test1 (ID, Name) VALUES (3, 'Three')
INSERT #Test1 (ID, Name) VALUES (4, 'Four')
INSERT #Test1 (ID, Name) VALUES (5, 'Five')

CREATE TABLE #Test2
(
    ID int NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL
)
INSERT #Test2 (ID, Name) VALUES (1, 'One')
INSERT #Test2 (ID, Name) VALUES (2, 'Two')
INSERT #Test2 (ID, Name) VALUES (3, 'Three')
INSERT #Test2 (ID, Name) VALUES (4, 'Four')
INSERT #Test2 (ID, Name) VALUES (5, 'Five')

SELECT *
FROM #Test1 t1
INNER JOIN #Test2 t2
ON t2.Name = t1.Name

SELECT *
FROM #Test1 t1
LEFT JOIN #Test2 t2
ON t2.Name = t1.Name

DROP TABLE #Test1
DROP TABLE #Test2

    이 프로그램을 실행하고 실행 계획을 보면, 당신은 그것 때문에 만족이 개 기준 이상으로, 내부는 쿼리가 실제로 더 LEFT 이상 가입 비용 않습니다 가입 것을 볼 수 있습니다. 그것은 SQL 서버가 가입 내부의 해시 경기를하고 싶어하기 때문에,하지만 왼쪽에 중첩 루프 가입하지; 전자는 훨씬 더 빨리 일반적이지만, 행의 수는 너무 작은이며, 사용에 인덱스가 없기 때문에, 해시 작업이 쿼리의 가장 비싼 부분으로 밝혀졌습니다.

    당신은 5 개 요소와 해시 테이블 대, 5 개 요소 목록에서 조회의 큰 숫자를 수행하기 위해 좋아하는 프로그래밍 언어로 프로그램을 작성하여 같은 효과를 볼 수 있습니다. 때문에 크기, 해시 테이블 버전은 실제로 느립니다. 그러나, 요소 (50), 또는 5000 개 요소 및 기어의 목록 버전 감속으로 인해 증가 해시 테이블에 대한 그것의 O (N) 대 O (1).

    하지만 그 대신 이름의 ID 열 수 있도록이 쿼리를 변경하고 당신은 매우 다른 이야기를 볼 수 있습니다. 이 경우, 그것은 모두 쿼리에 대한 중첩 루프를 수행하지만, INNER 버전이 추구와 함께 클러스터 된 인덱스 스캔을 대체 할 수있는 가입 -이 말 그대로 많은 수의 행과 빠른 배 정도됩니다 것을 의미한다.

    결론은 더 많거나 적은 내가 위의 몇 단락을 언급 한 것입니다 그래서; 이 거의 확실히 가능 하나 개 이상의 매우 작은 테이블과 결합 된 인덱스 또는 인덱스 범위의 문제이다. 사람들은 SQL 서버 때로는 내부에 대한 더 나쁜 실행 계획을 선택할 수있는 아래 유일한 상황은 좌 가입보다 가입합니다.

  2. ==============================

    2.빠른 내부보다는 조인 인 즉 아직 논의되지 않은 외부 조인으로 이어질 수있는 하나 개의 중요한 시나리오가있다.

    빠른 내부보다는 조인 인 즉 아직 논의되지 않은 외부 조인으로 이어질 수있는 하나 개의 중요한 시나리오가있다.

    외부 조인을 사용하는 경우, 옵티마이 저는이 열을 외부 테이블의 PK 조인하면 항상 실행 계획에서 외부 조인 된 테이블을 드롭 무료이며, 열 아무도 외부 테이블에서 선택되지 않습니다. 좌측 OUTER FROM SELECT A. 예를 들어 * B = ON A.KEY B.KEY 및 B.KEY은 B. 양쪽 Oracle 대한 PK 조인 및 SQL 서버 (I는 I 릴리스 (10)를 이용하여 판단 하였다) (I 2008 R2 사용) 실행 계획에서 치기 테이블 B.

    INNER FROM SELECT A.의 *이 B ON A.KEY = B.KEY이 나 제약이 존재하는 내용에 따라 실행 계획에 B를 필요로하지 않을 수 있습니다 가입 : 내부 조인에 대해 동일한 반드시 사실이 아니다.

    A.KEY가 B.KEY을 참조하는 널 (NULL) 외래 키는 경우는 B 행은 모든 행입니다 존재를 확인해야하기 때문에, 다음 최적화 계획에서 B를 삭제할 수 없습니다.

    A.KEY가 B.KEY를 참조하는 필수 외래 키 인 경우, 옵티마이 저는 제약 행의 존재를 보장하기 때문에 계획에서 B를 드롭 무료입니다. 최적화 프로그램이 계획에서 테이블을 삭제할 수 있습니다해서, 그것은 것 의미하지 않는다. SQL 서버 2008 R2는 계획에서 B를 삭제하지 않습니다. 오라클 (10)는 계획에서 드롭 B를 않습니다. 그것은 외부가 내부이 경우 SQL 서버에 가입 아웃 수행에 참여하는 방법을 쉽게 알 수있다.

    이것은 사소한 예를 들어, 독립 실행 형 쿼리에 대한 실용적이지 못하다. 당신이 필요하지 않은 경우 왜 테이블 조인?

    뷰를 설계 할 때 그러나 이것은 매우 중요한 설계 고려 사항이 될 수 있습니다. 자주는 "할 - 모든 것을"보기는 사용자가 중앙 테이블에 관련된해야 할 수도 있습니다 모든 조인이 내장되어 있습니다. (임시 쿼리 관계형 모델을 이해하지 못하는하고 순진한 사용자가 특히 경우)보기가 많은 테이블에서 모든와 관계있는 열을 포함 할 수있다. 그러나 최종 사용자는 뷰에서 테이블의 부분 집합에서 열을 액세스 할 수 있습니다. 테이블이와 결합하는 경우 외부는 다음 최적화 (및 않습니다) 계획에서 해제 필요한 테이블을 놓을 수 있습니다 조인.

    보기가 외부를 사용하여 정확한 결과를 제공 조인 있는지 확인하는 것이 중요합니다. Aaronaught가 말한대로 - 당신은 맹목적으로 내부 조인과 같은 결과를 기대하기위한 외부 조인을 대체 할 수 있습니다. 그러나 뷰를 사용하는 경우는 성능상의 이유로 유용 할 수 있습니다 시간이 있습니다.

    마지막으로 참고 - 나는 위의 관점에서 성능에 미치는 영향을 테스트하지 않은,하지만 이론적으로는 안전하게 당신은 또한 조건을 추가하는 경우 INNER는 OUTER로 조인이 가입 교체 할 수 있어야한다 보인다 NULL을지지 않습니다 WHERE 절에.

  3. ==============================

    3.모든 그것이 안해야하지만 쿼리 최적화, 쿼리 계획 캐싱 및 통계에 관해서 특히 우리 모두가 알고있는 모든 것을가 예상대로 작동하지 않는 작동합니다.

    모든 그것이 안해야하지만 쿼리 최적화, 쿼리 계획 캐싱 및 통계에 관해서 특히 우리 모두가 알고있는 모든 것을가 예상대로 작동하지 않는 작동합니다.

    우선은 단지의 일을 망쳐하지 있는지 확인하기 위해 쿼리 계획 캐시를 삭제, 인덱스 및 통계를 다시 제안했다. 그러나 나는이 수행에도 문제를 경험했습니다.

    나는 왼쪽이 빠르게 내부 이상이었다 조인 경우를 경험했습니다.

    근본적인 이유는 이것이다 : 두 개의 테이블을 가지고 있고 (두 테이블)에 인덱스가있는 컬럼에 가입합니다. 인덱스 테이블이 매치에 인덱스의 항목을 통해 루프 : 내부는 당신이 역을 테이블이 인덱스 테이블 하나와 일치에 인덱스의 항목을 통해 루프를 할 것처럼 경우에 상관없이 동일한 결과를 생성하지 않습니다 가입 테이블 일인치 문제는 당신이 통계를 오해 한 경우, 쿼리 최적화 (다른 기준에 따라) 이상 일치하는 항목이 테이블을 찾기 위해 인덱스의 통계를 사용하는 것입니다. 당신은 테이블 하나에 각 1 백만 개의 테이블이있는 경우에는 10 개 행이 일치하는 테이블이 당신이 100,000 행 매칭을 가지고 있습니다. 가장 좋은 방법은 테이블에 두 테이블 하나와 일치하는 10 번 인덱스 스캔을 수행하는 것입니다. 반대의 루프 100000 이상의 행과 시도가 100000 번과 일치 10 성공하는 것을 인덱스 스캔이 될 것입니다. 그래서 통계가 정확하지 않은 경우 최적화를 반복 할 잘못된 테이블과 인덱스를 선택할 수 있습니다.

    왼쪽을 최적화 할 수있는 최적화 프로그램이 선택하는이 순서에 참여하는 경우 더 나은 내부 조인보다 수행에 기록됩니다.

    그러나 또한 좌측을 최적화 할 수있다 옵티마이 좌측 반 조인 서브 최적으로 조인. 당신이 당신이 힘 순서 힌트를 사용하여 원하는 하나를 선택하게합니다.

  4. ==============================

    4.마지막에 OPTION (FORCE ORDER)와 두 쿼리 (내부 및 왼쪽 조인과 함께 하나)을 시도하고 그 결과를 게시 할 수 있습니다. OPTION (FORCE의 ORDER)는이 쿼리에서 제공하는 조인 순서에 최적화 힘이 실행 계획을 구축 할 수있는 쿼리 힌트입니다.

    마지막에 OPTION (FORCE ORDER)와 두 쿼리 (내부 및 왼쪽 조인과 함께 하나)을 시도하고 그 결과를 게시 할 수 있습니다. OPTION (FORCE의 ORDER)는이 쿼리에서 제공하는 조인 순서에 최적화 힘이 실행 계획을 구축 할 수있는 쿼리 힌트입니다.

    좌 가입으로 INNER 최대한 빨리 실행 시작을 가입하는 경우가 있기 때문입니다 :

    이 질문에 대답하지만 난 완전히 최적화 엉망 계산을 만드는 매우 복잡한 쿼리 기능을 갖춘 프로젝트에서 예전 알고하지 마십시오. 우리는 FORCE ORDER 10 초 5 분에서 쿼리의 실행 시간을 줄일 경우가 있었다.

  5. ==============================

    5.왼쪽 외부 조인 내부와 consisten 차이를 찾을 수 없어 간 비교의 숫자를 했어요. 많은 변수가 있습니다. 필드의 많은 수의 많은 수천 개의 테이블과 함께보고 데이터베이스 일하고, 시간이 지남에 따라 많은 변화 (공급 업체 버전과 지역의 워크 플로우). 이 쿼리 등 다양한의 요구를 충족하고 기록 데이터를 처리하기 위해 커버 인덱스의 모든 조합을 만들 수 없습니다. 모두 필드의 큰 숫자를 당기고 더 포함하는 인덱스가 존재하지 않는 합류 내부 쿼리가 두 개의 큰 인해 테이블 ​​(수백만 수십 수백만 개의 행의에) 서버 성능을 죽일 안된다 보았다.

    왼쪽 외부 조인 내부와 consisten 차이를 찾을 수 없어 간 비교의 숫자를 했어요. 많은 변수가 있습니다. 필드의 많은 수의 많은 수천 개의 테이블과 함께보고 데이터베이스 일하고, 시간이 지남에 따라 많은 변화 (공급 업체 버전과 지역의 워크 플로우). 이 쿼리 등 다양한의 요구를 충족하고 기록 데이터를 처리하기 위해 커버 인덱스의 모든 조합을 만들 수 없습니다. 모두 필드의 큰 숫자를 당기고 더 포함하는 인덱스가 존재하지 않는 합류 내부 쿼리가 두 개의 큰 인해 테이블 ​​(수백만 수십 수백만 개의 행의에) 서버 성능을 죽일 안된다 보았다.

    가장 큰 문제 비록, 위의 논의에 appeaer하지 않는 것 같습니다. 어쩌면 데이터베이스 잘 트리거로 설계 잘 좋은 데이터를 확인하기 위해 트랜잭션 처리를 디자인했다. 광산 자주 그들이 예상하지 않는 NULL 값을 가지고있다. 예 테이블 정의는 더-널 (null)을 적용 할 수 없었다하지만 내 환경에서 옵션이 아닙니다.

    질문은 그래서 ... 당신은 단지 속도 시대의 동일한 코드 수천 분을 실행하는 트랜잭션 처리를위한 높은 우선 순위를 쿼리를 디자인 않습니다. 또는 왼쪽 외부가 제공 조인은 정확성을 위해 이동 않습니다. 예기치 않은 NULL은 두 테이블 만 정보의 가능한 모든 행에서 데이터를 제거하지 않도록, 내부 양쪽에 일치를 발견해야한다 조인 것을 기억하십시오. 그리고, 너무 잘 오류 메시지가 발생하지 않습니다.

    당신은 필요한 데이터의 90 %를 받고으로 매우 빠르게하고 내부가 자동으로 정보를 삭제 한 조인 검색 할 수 없습니다. 때로는 내부가 빠를 수 있습니다 조인,하지만 난 그들이 실행 계획을 검토하지 않는 사람이 그 가정을 믿지 않는다. 속도도 중요하지만 정확성이 더 중요하다.

  6. ==============================

    6.당신이 일을하고 열 여부가 결합 인덱스가 없거나 조인의 귀하의 성능 문제 때문에 숫자가 될 가능성이 더 높습니다.

    당신이 일을하고 열 여부가 결합 인덱스가 없거나 조인의 귀하의 성능 문제 때문에 숫자가 될 가능성이 더 높습니다.

    각 가입에 대한 최악의 경우 쉽게 구 전체 테이블 스캔을 수행 할 수있다.

  7. ==============================

    7.외부 뷰에 사용될 때 우수한 성능을 제공 할 수 있습니다 조인.

    외부 뷰에 사용될 때 우수한 성능을 제공 할 수 있습니다 조인.

    당신이보기를 포함하는 쿼리를 가지고 있고, 그 뷰가 함께 결합 (10 개) 테이블로 구성 말한다. 쿼리는 만 10 표 중 3의 열을 사용하는 일이 말.

    그 10 개 테이블 내부에 가입 함께했던 경우, 쿼리 최적화 프로그램은 쿼리 자체가 7 표 10에서 필요하지 않더라도 그들 모두에 가입해야합니다. 그의 내부 자체는 계산하기가 중요하고, 데이터를 필터링 할 수 있습니다 조인 때문이다.

    그 10 개 테이블이 있었던 경우, 쿼리 최적화 프로그램은 실제로 필요했던 것들을 결합 할 것입니다 함께 대신 외부에 가입 : 3이 경우 그 중 10 명 중. 즉 년대 자체가 더 이상 데이터를 필터링하고, 따라서 사용되지 않는 생략 할 수있다 조인 조인 없기 때문입니다.

    출처: http://www.sqlservercentral.com/blogs/sql_coach/2010/07/29/poor-little-misunderstood-views/

  8. ==============================

    8.내부는 빠르게 왼쪽 조인보다 조인 경우 검사 할 때 나는 SQL 서버에 뭔가 흥미로운 발견했다.

    내부는 빠르게 왼쪽 조인보다 조인 경우 검사 할 때 나는 SQL 서버에 뭔가 흥미로운 발견했다.

    당신이 선택 문에서 왼쪽 조인 된 테이블의 항목을 포함 해달라고하면, 가입 왼쪽 빠르게 내부 조인과 같은 쿼리보다됩니다.

    당신이 선택 문에서 왼쪽 조인 된 테이블을 포함 할 경우, 내부는 같거나 더 빠른 왼쪽에 비해 가입 같은 쿼리되었다 가입 할 수 있습니다.

    9. from https://stackoverflow.com/questions/2726657/inner-join-vs-left-join-performance-in-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] MySQL의 오류 : 키 길이없는 키 사양  (0) 2020.03.13
[SQL] MAX와 GROUP BY (DATE) [중복]  (0) 2020.03.13
[SQL] 어떻게 SQL Server의 테이블에서 열 이름을 얻을 수 있나요?  (0) 2020.03.13
[SQL] 이 쿼리는 쉼표로 구분 된 목록의 SQL 서버를 만들기 위해 무엇을?  (0) 2020.03.13
[SQL] 어떻게 MySQL은 여러 테이블에서 삭제하려면?  (0) 2020.03.13
,

티스토리툴바