sql server - SQL - inner join between large table and small table, with filters -
i have simple sql query inner join between tables , b, filtering. table large, whereas table b small. want return rows match filter, e.g., table_b.some_col in ('a', 'b', 'c', 'd'). of course, have filter clause. however, recommended me duplicate same filter, table , put in join. example,
select * table_a inner join table_b on table_a.pk = table_b.pk , table_a.some_col in ('a', 'b', 'c', 'd') table_b.some_col in ('a', 'b', 'c', 'd')
it claimed doing improves query plan. unfortunately, due security restrictions on machine, cannot inspect query plans. claim true?
adding filter @ and
or where
clause doesn't make difference..
here tests ..
query used..
select * orders o join hr.employees h on h.empid=o.empid
filter @ , clause..
, empid=4
set statistics io:
table 'orders'. scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'employees'. scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
filter @ clause:
empid=4
statistics io:
table 'orders'. scan count 1, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. table 'employees'. scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
in each case,sqlserver smart enough rearrange query (since inner join) , relevant rows
Comments
Post a Comment