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

execution plan:
enter image description here

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

execution plan:
enter image description here

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

Popular posts from this blog

Spring Boot + JPA + Hibernate: Unable to locate persister -

go - Golang: panic: runtime error: invalid memory address or nil pointer dereference using bufio.Scanner -

c - double free or corruption (fasttop) -