Quelle requête SQL est plus rapide? Filtrer sur les critères de participation ou la clause Where?

Question

Comparer ces 2 requêtes. Est-il plus rapide de mettre le filtre sur les critères de jointure ou l'ont été de la clause. J'ai toujours pensé qu'il est plus rapide sur les critères de jointure, car il réduit le jeu de résultats au moment plus tôt possible, mais je ne sais pas pour sûr.

Je vais construire des tests pour voir, mais je voulais avoir des avis sur ce qui serait plus clair à lire.

Requête 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? */

Requête 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

MODIFIER

J'ai couru quelques tests, et les résultats montrent qu'il est en fait très proches, mais l' WHERE clause est en fait légèrement plus vite! =)

Je suis absolument d'accord qu'il est plus judicieux d'appliquer le filtre sur l' WHERE clause, j'étais juste curieux de savoir les conséquences de performance.

TEMPS ÉCOULÉ OÙ CRITÈRES: 143016 ms
TEMPS ÉCOULÉ CRITÈRES de JOINTURE: 143256 ms

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

Answer 1

En terme de Performance, ils sont les mêmes (et de produire les mêmes plans)

Logiquement, vous devez faire l'opération qui a encore un sens, si vous remplacez INNER JOIN avec un LEFT JOIN.

Dans votre cas, cela va ressembler à ceci:

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

ou ceci:

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

L'ancien requête ne renvoie aucun matchs réels pour a.id autres qu' 1, de sorte que la dernière syntaxe (avec WHERE) est logiquement plus cohérente.

Answer 2

Pour les jointures internes, peu importe où vous mettez vos critères. Le compilateur SQL se transforme à la fois en un plan d'exécution dans lequel le filtrage a lieu au-dessous de la jointure (c'est-à-dire comme si les expressions de filtrage apparaissaient dans la condition de jointure).

Les jointures externes sont une autre affaire, car l'emplacement du filtre modifie la sémantique de la requête.

Answer 3

En ce qui concerne les deux méthodes.

• JOIN / ON est pour joindre des tables
• O est pour filtrer les résultats

Bien que vous puissiez les utiliser différemment, cela me semble toujours une odeur.

Traitez la performance quand c'est un problème. Ensuite, vous pouvez examiner de telles "optimisations".

Answer 4

Avec n'importe quel optimiseur de requêtes, les centimes sont identiques.

Answer 5

Je suppose que le premier, car il crée un filtre plus spécifique sur les données. Mais vous devriez voir le plan d'exécution , comme pour toute optimisation, car il peut être très différent en fonction de la taille des données, du matériel du serveur, etc.