어떤 SQL 쿼리가 더 빠릅니까?가입 조건 또는 장소 절에 따라 필터링하시겠습니까?
이 2개의 쿼리를 비교합니다.필터는 가입기준에 붙이는 것이 빠른가요, 아니면 가입기준에 붙이는 것이 빠른가요?WHERE절을 클릭합니다.가능한 한 빨리 설정되는 결과를 줄이기 때문에 가입기준이 빠를 것이라고 항상 생각해 왔습니다만, 확실히는 모르겠습니다.
몇 가지 테스트를 할 예정이지만, 어떤 것이 읽기 쉬운지에 대한 의견도 듣고 싶었습니다.
쿼리 1
SELECT *
FROM TableA a
INNER JOIN TableXRef x
ON a.ID = x.TableAID
INNER JOIN TableB b
ON x.TableBID = b.ID
WHERE a.ID = 1 /* <-- Filter here? */
쿼리 2
SELECT *
FROM TableA a
INNER JOIN TableXRef x
ON a.ID = x.TableAID
AND a.ID = 1 /* <-- Or filter here? */
INNER JOIN TableB b
ON x.TableBID = b.ID
편집
몇 가지 테스트를 해봤는데 결과는 매우 근접한 것으로 나타났습니다만WHERE절이 실제로 조금 더 빠릅니다!=)
필자는 필터가 더 타당하다는 것에 전적으로 동의합니다.WHERE조항에 따르면 퍼포먼스에 어떤 의미가 있는지 궁금했을 뿐입니다.
경과시간 기준: 143016 밀리초
경과시간 가입기준: 143256밀리초
시험
SET NOCOUNT ON;
DECLARE @num INT,
@iter INT
SELECT @num = 1000, -- Number of records in TableA and TableB, the cross table is populated with a CROSS JOIN from A to B
@iter = 1000 -- Number of select iterations to perform
DECLARE @a TABLE (
id INT
)
DECLARE @b TABLE (
id INT
)
DECLARE @x TABLE (
aid INT,
bid INT
)
DECLARE @num_curr INT
SELECT @num_curr = 1
WHILE (@num_curr <= @num)
BEGIN
INSERT @a (id) SELECT @num_curr
INSERT @b (id) SELECT @num_curr
SELECT @num_curr = @num_curr + 1
END
INSERT @x (aid, bid)
SELECT a.id,
b.id
FROM @a a
CROSS JOIN @b b
/*
TEST
*/
DECLARE @begin_where DATETIME,
@end_where DATETIME,
@count_where INT,
@begin_join DATETIME,
@end_join DATETIME,
@count_join INT,
@curr INT,
@aid INT
DECLARE @temp TABLE (
curr INT,
aid INT,
bid INT
)
DELETE FROM @temp
SELECT @curr = 0,
@aid = 50
SELECT @begin_where = CURRENT_TIMESTAMP
WHILE (@curr < @iter)
BEGIN
INSERT @temp (curr, aid, bid)
SELECT @curr,
aid,
bid
FROM @a a
INNER JOIN @x x
ON a.id = x.aid
INNER JOIN @b b
ON x.bid = b.id
WHERE a.id = @aid
SELECT @curr = @curr + 1
END
SELECT @end_where = CURRENT_TIMESTAMP
SELECT @count_where = COUNT(1) FROM @temp
DELETE FROM @temp
SELECT @curr = 0
SELECT @begin_join = CURRENT_TIMESTAMP
WHILE (@curr < @iter)
BEGIN
INSERT @temp (curr, aid, bid)
SELECT @curr,
aid,
bid
FROM @a a
INNER JOIN @x x
ON a.id = x.aid
AND a.id = @aid
INNER JOIN @b b
ON x.bid = b.id
SELECT @curr = @curr + 1
END
SELECT @end_join = CURRENT_TIMESTAMP
SELECT @count_join = COUNT(1) FROM @temp
DELETE FROM @temp
SELECT @count_where AS count_where,
@count_join AS count_join,
DATEDIFF(millisecond, @begin_where, @end_where) AS elapsed_where,
DATEDIFF(millisecond, @begin_join, @end_join) AS elapsed_join
퍼포먼스 면에서는 동일(및 동일한 계획 작성)
논리적으로 대체해도 여전히 의미가 있는 작업을 수행해야 합니다.INNER JOIN와 함께LEFT JOIN.
바로 이 경우 다음과 같이 표시됩니다.
SELECT *
FROM TableA a
LEFT JOIN
TableXRef x
ON x.TableAID = a.ID
AND a.ID = 1
LEFT JOIN
TableB b
ON x.TableBID = b.ID
또는 다음과 같습니다.
SELECT *
FROM TableA a
LEFT JOIN
TableXRef x
ON x.TableAID = a.ID
LEFT JOIN
TableB b
ON b.id = x.TableBID
WHERE a.id = 1
이전 쿼리는 실제 일치하는 항목을 반환하지 않습니다.a.id이외에1후자의 구문(와WHERE)는 논리적으로 보다 일관성이 있습니다.
내적 참여의 경우 어디에 기준을 두느냐는 중요하지 않습니다.SQL 컴파일러는 둘 다 join 아래에서 필터링이 발생하는 실행 계획으로 변환합니다(필터식이 join 조건에 있는 것처럼 보입니다).
외부 조인은 필터 위치에 따라 쿼리의 의미가 달라지기 때문에 다른 문제입니다.
두 가지 방법에 관한 한.
- JOIN/ON은 테이블을 결합하기 위한 것입니다.
- WHERE는 결과를 필터링하기 위한 것입니다.
당신은 그것들을 다르게 사용할 수 있지만, 나에게는 항상 냄새처럼 느껴집니다.
퍼포먼스가 문제가 될 때는 대처합니다.그 후, 그러한 「최적화」를 조사할 수 있습니다.
postgresql에서는 동일합니다.우리가 이걸 아는 이유는 만약 당신이explain analyze각 쿼리에 대해 동일한 계획이 나타납니다.다음 예를 들어보겠습니다.
# explain analyze select e.* from event e join result r on e.id = r.event_id and r.team_2_score=24;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Hash Join (cost=27.09..38.22 rows=7 width=899) (actual time=0.045..0.047 rows=1 loops=1)
Hash Cond: (e.id = r.event_id)
-> Seq Scan on event e (cost=0.00..10.80 rows=80 width=899) (actual time=0.009..0.010 rows=2 loops=1)
-> Hash (cost=27.00..27.00 rows=7 width=8) (actual time=0.017..0.017 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on result r (cost=0.00..27.00 rows=7 width=8) (actual time=0.006..0.008 rows=1 loops=1)
Filter: (team_2_score = 24)
Rows Removed by Filter: 1
Planning time: 0.182 ms
Execution time: 0.101 ms
(10 rows)
# explain analyze select e.* from event e join result r on e.id = r.event_id where r.team_2_score=24;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Hash Join (cost=27.09..38.22 rows=7 width=899) (actual time=0.027..0.029 rows=1 loops=1)
Hash Cond: (e.id = r.event_id)
-> Seq Scan on event e (cost=0.00..10.80 rows=80 width=899) (actual time=0.010..0.011 rows=2 loops=1)
-> Hash (cost=27.00..27.00 rows=7 width=8) (actual time=0.010..0.010 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on result r (cost=0.00..27.00 rows=7 width=8) (actual time=0.006..0.007 rows=1 loops=1)
Filter: (team_2_score = 24)
Rows Removed by Filter: 1
Planning time: 0.140 ms
Execution time: 0.058 ms
(10 rows)
둘 다 최소 비용과 최대 비용이 동일할 뿐만 아니라 쿼리 계획도 동일합니다.또한 상위 쿼리에서도 team_score_2가 'Filter'로 적용됩니다.
어떤 쿼리 옵티마이저라도...그것들은 아주 똑같은 겁니다.
규칙 0: 몇 가지 벤치마크를 실행하여 확인하세요!어느 것이 더 빠를지 정말 알 수 있는 유일한 방법은 시도해 보는 것입니다.이러한 유형의 벤치마크는 SQL 프로파일러를 사용하여 매우 쉽게 수행할 수 있습니다.
또한 JOIN 및 WHERE 절을 사용하여 작성된 쿼리의 실행 계획을 검토하여 눈에 띄는 차이를 확인합니다.
마지막으로, 다른 사람들이 말했듯이, SQL Server에 내장된 옵티마이저를 포함하여 모든 적절한 옵티마이저에 의해 이 두 가지를 동일하게 취급해야 합니다.
이 조인 배치가 퍼포먼스의 결정 요소가 될 가능성은 거의 없습니다.tsql의 실행 계획에 대해서는 잘 모르지만 비슷한 계획에 따라 자동으로 최적화될 가능성이 높습니다.
그것이 더 빠릅니까?먹어보고 보세요.
어느 쪽이 읽기 쉬운가요?이동 조건은 조인과는 전혀 관계가 없기 때문에 첫 번째 항목은 더 "올바른" 것으로 보입니다.
첫 번째는 데이터에 대한 보다 구체적인 필터를 만들기 때문입니다.그러나 실행 계획은 다른 최적화와 마찬가지로 데이터 크기나 서버 하드웨어 등에 따라 크게 달라질 수 있으므로 확인해야 합니다.
언급URL : https://stackoverflow.com/questions/2509987/which-sql-query-is-faster-filter-on-join-criteria-or-where-clause
'programing' 카테고리의 다른 글
| 영구 환경 변수를 cmd에서 설정합니다.실행 (0) | 2023.04.17 |
|---|---|
| 표준 윈도 .ini 파일은 코멘트를 허용합니까? (0) | 2023.04.17 |
| 셸 스크립트의 인스턴스를 한 번에 하나만 실행할 수 있는 빠르고 더러운 방법 (0) | 2023.04.17 |
| Bash 문자열에서 고정 접두사/서픽스 제거 (0) | 2023.04.17 |
| 파일을 일괄 정렬하는 방법 (0) | 2023.04.17 |