hello GSquared,

thank's for your answer.

Using the subselect as shown in the example or switching it to a cross apply ends up in exactly the same execution plan.

The table variables are only for making a quick example for understanding.

In our real database the two tables are permanent tables, each containing several 10thousands rows.

The query above is executed several times within a daily process. The outer table gets filtered by an Id and returs just a few rows most of the time. For some cases the outer table returns about 20.000 to 40.000 rows. The inner select does a clustered index seek, which is quite well. But the high number of index seeks results in a query duration of more than 10 seconds. That's too long.

I personally think making 40.000 index seeks is much slower than 1 partial index scan. For this I try to find a solution where each table is scanned only once.

In any other query the optimizer surely would switch to an index scan instead of making that much lookups but with the subquery I force him to loop. And that's the target: eliminate the loop.

Good evening, Wolf