[SQL] IN 대 큰 행 집합에 가입하세요

IN 대 큰 행 집합에 가입하세요

나는 기본 키는 다른 테이블에있는 경우 테이블의 행을 선택하고자하고있다. 나는 확실히 나는이 가입 또는 SQL Server 2005에서 IN 연산자가 큰 데이터 세트 (행 즉 수백만)와이 두 SQL 쿼리 사이에 상당한 성능 차이가 사용해야 아니에요 경우?

SELECT *
FROM a
WHERE a.c IN (SELECT d FROM b)

SELECT a.*
FROM a JOIN b ON a.c = b.d

해결법

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

   1.최신 정보:

   최신 정보:

   내 블로그에이 기사는 내 대답과 다른 대답에 내 의견, 그리고 쇼 실제 실행 계획을 모두 요약

   SELECT  *
   FROM    a
   WHERE   a.c IN (SELECT d FROM b)
   
   SELECT  a.*
   FROM    a
   JOIN    b
   ON      a.c = b.d
   

   이 쿼리는 해당되지 않습니다. 테이블 B가 보존 키가 아닌 경우 그들은 서로 다른 결과를 얻을 수 있습니다 (전. 전자. b.d의 값이 고유하지 않습니다).

   첫 번째 쿼리의 등가은 다음과 같다 :

   SELECT  a.*
   FROM    a
   JOIN    (
           SELECT  DISTINCT d
           FROM    b
           ) bo
   ON      a.c = bo.d
   

   b.d 독특하고 (고유 인덱스 또는 UNIQUE 제약 조건) 등으로 표시된 경우,이 쿼리는 동일하며 SQL Server가이 점을 고려해야 할 스마트 충분히이기 때문에 아마 동일한 계획을 사용합니다.

   SQL Server는이 쿼리를 실행하려면 다음 방법 중 하나를 사용할 수있다 :

   둘 다 이러한 방법의 전체 하위 쿼리 매번 재평가 없습니다.

   이 작동하는 방법에 대한보다 자세한 사항은 내 블로그에서이 항목을 참조하십시오 :

   모든 RDBMS의 빅 4의에 대한 링크가 있습니다.

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

   2.어느 쪽도 없습니다. 은 ANSI-92를 사용하여 가입 :

   어느 쪽도 없습니다. 은 ANSI-92를 사용하여 가입 :

   SELECT a.*
   FROM a JOIN b a.c = b.d
   

   가 존재하지만, 그것은 최선의

   SELECT a.*
   FROM a
   WHERE EXISTS (SELECT * FROM b WHERE a.c = b.d)
   

   이 조인에 의해 생성 될 수있는 중복을 제거 할 수 있지만 그냥 빨리하지 않을 경우 빠르게 실행

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

   3.(가) 사용 지수와 다른 깔끔한 페이징 트릭 최적화 조인 반면 IN은 각 행에 대해 평가 (및 B 재방송에서 선택)된다 ..

   (가) 사용 지수와 다른 깔끔한 페이징 트릭 최적화 조인 반면 IN은 각 행에 대해 평가 (및 B 재방송에서 선택)된다 ..

   하지만 대부분의 경우, 옵티마이 저는 가능성이 상관 하위 쿼리 밖으로 가입 어쨌든 같은 실행 계획으로 끝날 구축 할 수있을 것입니다.

   편집 : 친절이 답변의 유효성 및 영업 이익의 질문에 대한 실제 대답에 대한 자세한 ... 토론은 아래의 설명을 참조하십시오. =)

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

   4.49,000,000 행이 테이블에 경험에서 말하기 나는 LEFT OUTER JOIN을 추천 할 것입니다. IN을 사용하여, 또는 LEFT OUTER 1 초에 완료 가입 어디에 완료하는 데 5 분 툭 존재한다.

   49,000,000 행이 테이블에 경험에서 말하기 나는 LEFT OUTER JOIN을 추천 할 것입니다. IN을 사용하여, 또는 LEFT OUTER 1 초에 완료 가입 어디에 완료하는 데 5 분 툭 존재한다.

   SELECT a.*
   FROM a LEFT OUTER JOIN b ON a.c = b.d
   WHERE b.d is not null -- Given b.d is a primary Key with index
   

   실제로 내 쿼리에서 나는 9 표를 통해이 작업을 수행.

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

   5.이외에도 가서 실제로 자신을위한 테스트 데이터의 큰 한번 벤 면적에 그것을 밖으로 테스트에서, 나는 조인을 사용 말할 것입니다. 나는 항상 IN 하위 쿼리에 비해 대부분의 경우이를 사용하여 더 나은 성능을 했어, 그리고 무슨 일이 등없는 지금까지 선택한 항목에 가입하는 방법으로 당신은 더 많은 사용자 정의 옵션을 사용할 수 있습니다

   이외에도 가서 실제로 자신을위한 테스트 데이터의 큰 한번 벤 면적에 그것을 밖으로 테스트에서, 나는 조인을 사용 말할 것입니다. 나는 항상 IN 하위 쿼리에 비해 대부분의 경우이를 사용하여 더 나은 성능을 했어, 그리고 무슨 일이 등없는 지금까지 선택한 항목에 가입하는 방법으로 당신은 더 많은 사용자 정의 옵션을 사용할 수 있습니다

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

   6.그들은 서로 다른 결과를 다른 쿼리입니다. 은 IN 쿼리하면 'A'때마다 술어의 일치 테이블에서 1 개 행을 얻을 것이다. 내부와 조건의 일치에 가입 할 때마다 당신이 * B 행을 얻을 것이다 쿼리에 가입하세요. 그래서 {1,2,3}의의 값과 {1,2,2,3}의 B 당신은 IN에서이 가입에서 1,2,2,3 및 1,2,3을 얻을 것이다.

   그들은 서로 다른 결과를 다른 쿼리입니다. 은 IN 쿼리하면 'A'때마다 술어의 일치 테이블에서 1 개 행을 얻을 것이다. 내부와 조건의 일치에 가입 할 때마다 당신이 * B 행을 얻을 것이다 쿼리에 가입하세요. 그래서 {1,2,3}의의 값과 {1,2,2,3}의 B 당신은 IN에서이 가입에서 1,2,2,3 및 1,2,3을 얻을 것이다.

   편집 - 나는 당신이 당신에게 오해를 줄 것이다 여기에 몇 가지 답변 건너 올 수 있습니다 생각합니다. 스스로를 테스트 가서 당신은이 모든 좋은 쿼리 계획입니다 볼 수 있습니다 :

   create table t1 (t1id int primary key clustered)
   create table t2 (t2id int identity primary key clustered
       ,t1id int references t1(t1id)
   )
   
   
   insert t1 values (1)
   insert t1 values (2)
   insert t1 values (3)
   insert t1 values (4)
   insert t1 values (5)
   
   insert t2 values (1)
   insert t2 values (2)
   insert t2 values (2)
   insert t2 values (3)
   insert t2 values (4)
   
   
   select * from t1 where t1id in (select t1id from t2)
   select * from t1 where exists (select 1 from t2 where t2.t1id = t1.t1id)
   select t1.* from t1 join t2 on t1.t1id = t2.t1id
   

   처음 두 계획은 동일하다. I는 다른 의미를 갖는다 가입 전술 한 바와 같이 때문에 마지막 계획 중첩 루프이며,이 차이가 예상된다.

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

   7.하위 쿼리 기본 사항에 대한 MSDN 문서에서 :

   하위 쿼리 기본 사항에 대한 MSDN 문서에서 :

   당신이 제공 한 예에서, 중첩 된 쿼리는 외부 쿼리 결과 각각에 대해 하나의 시간을 처리 할 필요하므로 성능에 차이가있을 수 없습니다. 두 쿼리에 대한 실행 계획을 선택하면이 문제를 확인해야합니다.

   참고 : 질문 자체가 SQL 서버 2005를 지정하지 않았지만, 내가 질문 태그를 기준으로 그 가정에 대답했다. 다른 데이터베이스 엔진 (심지어 다른 SQL Server 버전)를 같은 방법으로 최적화 할 수 있습니다.

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

   8.두 가지 유형의 실행 계획을 관찰하고 결론을 그립니다. 은 "IN"문에서 하위 쿼리에 의해 반환 된 레코드의 수는 매우 작다 않는 한, IN 변형은 거의 확실하게 느립니다.

   두 가지 유형의 실행 계획을 관찰하고 결론을 그립니다. 은 "IN"문에서 하위 쿼리에 의해 반환 된 레코드의 수는 매우 작다 않는 한, IN 변형은 거의 확실하게 느립니다.

9. ==============================

   9.나는 그것이 더 빨리보다 많아서있을거야 내기, 조인을 사용합니다. 물론 정의 된 기본 키는, 따라서 엄청난 인덱싱 속도 일을시키는 있다는 것을이 가정.

   나는 그것이 더 빨리보다 많아서있을거야 내기, 조인을 사용합니다. 물론 정의 된 기본 키는, 따라서 엄청난 인덱싱 속도 일을시키는 있다는 것을이 가정.

10. ==============================

    10.일반적으로는 IN 하위 쿼리보다 더 효율적인 것 조인 열리는; 그러나 SQL * 서버 최적화 프로그램은 일반적으로 눈에 띄는 성능 차이가 발생합니다. 그럼에도 불구하고, 아마 코드에 가장 일관성 당신의 기준을 유지하기 위해 조건을 조인을 사용합니다. 데이터와 코드가 지금 필요로 향후 마이그레이션 할 경우에도, 데이터베이스 엔진이 그렇게 용서하지 않을 수 있습니다 (A는 IN 하위 쿼리 대신 조인을 사용 예를 들어 MySQL의에서 큰 차이가 있습니다).

    일반적으로는 IN 하위 쿼리보다 더 효율적인 것 조인 열리는; 그러나 SQL * 서버 최적화 프로그램은 일반적으로 눈에 띄는 성능 차이가 발생합니다. 그럼에도 불구하고, 아마 코드에 가장 일관성 당신의 기준을 유지하기 위해 조건을 조인을 사용합니다. 데이터와 코드가 지금 필요로 향후 마이그레이션 할 경우에도, 데이터베이스 엔진이 그렇게 용서하지 않을 수 있습니다 (A는 IN 하위 쿼리 대신 조인을 사용 예를 들어 MySQL의에서 큰 차이가 있습니다).

11. ==============================

    11.이론은이 같은 질문에 지금까지 당신을 얻을 것이다. 하루의 끝에서, 당신은 두 쿼리를 테스트하고 실제로 빠르게 실행되는보고 싶을 것이다. 나는 버전이 분을 인수하고, IN 버전이 적은 초 이상 걸렸다 가입의 경우 했어. 또한 실제로 더 빨랐다 가입의 경우 했어.

    이론은이 같은 질문에 지금까지 당신을 얻을 것이다. 하루의 끝에서, 당신은 두 쿼리를 테스트하고 실제로 빠르게 실행되는보고 싶을 것이다. 나는 버전이 분을 인수하고, IN 버전이 적은 초 이상 걸렸다 가입의 경우 했어. 또한 실제로 더 빨랐다 가입의 경우 했어.

    개인적으로, 나는 내가 서브 쿼리 테이블에서 모든 필드를 필요로하지 알고있는 경우 IN 버전으로 시작하는 경향이있다. 그 시작이 느리게 실행되면, 나는 최적화 것이다. 다행히도, 대규모 데이터 세트에 대한 쿼리를 다시 작성하면 간단하게 쿼리 분석기에서 시간을하고 당신에게있는 거 제작 진행 상황을 알 수 있도록 눈에 띄는 차이가 있습니다.

    행운을 빕니다!

12. ==============================

    12.필자는 항상 IN 방법론의 후원자이었다. 이 링크는 포스트그레스 SQL에 실시 된 시험의 세부 사항을 포함하고 있습니다. http://archives.postgresql.org/pgsql-performance/2005-02/msg00327.php

    필자는 항상 IN 방법론의 후원자이었다. 이 링크는 포스트그레스 SQL에 실시 된 시험의 세부 사항을 포함하고 있습니다. http://archives.postgresql.org/pgsql-performance/2005-02/msg00327.php

from https://stackoverflow.com/questions/1001543/in-vs-join-with-large-rowsets by cc-by-sa and MIT license