Referring to your original post in this thread:
tony28 (8/25/2014)
I have one small table about 150rows and second bigger about 50000rows
Three tables are referenced in your query:
Table [TB_WORK_ORDERDTL_CPM01] 10,856,500 rows
Table [TB_CODE_PRTFORM] 1,165 rows
Table [TB_CODE_ALC] 31,234 rows
If I join together and I selected just one item from bigger table I have this
(51 row(s) affected)
Table 'TB_CODE_ALC'. Scan count 0, logical reads 102, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_CODE_PRTFORM'. Scan count 101, logical reads 303, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_WORK_ORDERDTL_CPM01'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
If I selected all what I need I have
(53624 row(s) affected)
Table 'TB_CODE_ALC'. Scan count 0, logical reads 107248, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_CODE_PRTFORM'. Scan count 104750, logical reads 302940, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TB_WORK_ORDERDTL_CPM01'. Scan count 1, logical reads 879, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Why is possible that data from small data are still load to memory ?
Both queries require all three tables to be read.
Do you have any strategy for these joins ?
Yes. Change them to inner joins in the query. The logic of the query dictates that they should be inner joins as explained earlier, and as shown by the execution plans.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden