Какой SQL-запрос выполняется быстрее? Фильтровать по критерию Присоединиться или Где?

Сравните эти 2 запроса. Быстрее ли устанавливать фильтр на критерии соединения или в предложение. Я всегда чувствовал, что это быстрее по критериям соединения, потому что он уменьшает набор результатов в самый скорый момент, но я не знаю точно.

Я собираюсь собрать некоторые тесты, но я также хотел получить мнения, по которым было бы более понятным читать.

Запрос 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 ms
ЭЛЕМЕНТЫ ВРЕМЕНИ ПРИСОЕДИНЯЮТСЯ КРИТЕРИИ: 143256 мс

TEST

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

Ответ 1

По производительности, они одинаковы (и производят одни и те же планы)

Логически, вы должны сделать операцию, которая все еще имеет смысл, если вы замените 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) логически более согласован.

Ответ 2

Для внутренних объединений не имеет значения, где вы ставите свои критерии. Компилятор SQL преобразует оба в план выполнения, в котором фильтрация происходит под соединением (т.е., как если бы выражения фильтра отображались в условии соединения).

Внешние соединения - это другое дело, так как место фильтра изменяет семантику запроса.

Ответ 3

Что касается двух методов:

JOIN/ON предназначен для соединения таблиц
ГДЕ для фильтрации результатов

Пока вы можете использовать их по-разному, это всегда кажется мне запахом.

Относитесь к производительности, когда это проблема. Затем вы можете посмотреть на такие "оптимизации".

Ответ 4

С любым оптимизатором запросов worh cent... они идентичны.

Ответ 5

Я предполагаю, что первый, потому что он делает более конкретный фильтр по данным. Но вы должны видеть план выполнения, как и при любой оптимизации, потому что он может быть очень разным, зависящим от размера данных, серверного оборудования и т.д.

Ответ 6

Это быстрее? Попробуйте и посмотрите.

Что легче читать? Первый для меня выглядит более "правильным", поскольку перемещенное условие не имеет ничего общего с соединением.

Ответ 7

На самом деле маловероятно, что размещение этого соединения будет решающим фактором производительности. Я не очень хорошо знаком с планированием выполнения для tsql, но, скорее всего, они будут автоматически оптимизированы для подобных планов.

Ответ 8

Правило № 0: Запустите некоторые тесты и посмотрите! Единственный способ узнать, что будет быстрее - попробовать. Эти типы тестов очень просты в использовании с использованием профилировщика SQL.

Также рассмотрите план выполнения запроса, написанного с помощью JOIN, и с предложением WHERE, чтобы узнать, какие отличия отличаются.

Наконец, как говорили другие, эти два должны обрабатываться одинаково любым достойным оптимизатором, в том числе встроенным в SQL Server.