Help me figure out what SQL does with this query

  • Mister Sachmo

    SSCommitted

    Points: 1618

    Can anyone explain what happens in the third join? Does SQL rescan the join tables after the initial joins are done?

    SELECT t3.col1, t2.col1

    FROM dbo.table0 t0

    INNER JOIN dbo.Table1 t1

    ON t1.col1 = t0.col1

    INNER JOIN dbo.Table2 t2

    ON t2.col1 = t1.col1

    INNER JOIN etl.Table3 t3

    ON t3.col1 = t1.col2

    AND t3.col2 = t0.col2

    AND t3.col3 = t2.col2

    WHERE t1.col4 = 1

    AND t0.col4 = 1

    • This topic was modified 4 months, 2 weeks ago by  Mister Sachmo.
  • Lynn Pettis

    SSC Guru

    Points: 442118

    Have you looked at the execution plan for the query?

  • Eddie Wuerch

    SSChampion

    Points: 12259

    The optimizer does not respect your join order unless you force it (which is usually the wrong thing to do). Based on the JOIN conditions and the filters in the WHERE clause, it will first check the distribution statistics for the indexes in each table to determine which path through which indexes is most efficient. It's generally looking for read elimination - how can it answer the query while touching as few rows as possible. I/O is expensive, and it doesn't waste processing time scanning memory to figure out if some of the target data pages are already in loaded into the data cache. Plans are cached and may be later reused when the cache is in a far different state, so the current warmth of the cache is not taken into account.

    Once it has established which indexes it will use, how it will use them(seek v. scan), and what order to read the indexes, it goes to work. It may begin by loading a vTable by scanning and filtering an index on Table1.col4, merging the vTable with a filtered scan of an index on Table0.col4 into another vTable, then LOOP JOINing the results of that to Table3 into a third vTable, then LOOP JOINing the third vTable with an index scan on Table2 into the final vTable, from which the query output is read. It could be a very different order with different operators depending on the row counts and index densities of each table.

    (vTable refers to the intermediate results stored in memory. When a query requests memory, it's for these vTables)

    Eddie Wuerch
    MCM: SQL

  • Mister Sachmo

    SSCommitted

    Points: 1618

    Yes, and it wasn't as disastrous as i thought it would be. I always thought different tables in a single join statement was a bad thing. Query plan didn't look too bad.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply