April 2, 2019 at 4:15 pm
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
April 2, 2019 at 4:37 pm
Have you looked at the execution plan for the query?
April 2, 2019 at 5:02 pm
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
April 2, 2019 at 6:10 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply