Hello sqldba_icon,

seeing your query there are two things focused my attention and looked strange to me:

The NOT EXISTS clause. As pointed by Kevin it could be bad for the optimization process. You can try to substitute it for another LEFT OUTER JOIN; maybe it is done by SQL Server when creating a plan, or maybe don't.

The ROW_NUMBER operator, it implies a SORT over the data result. You create a temp table so you will use it sooner or later, and at first glance I can imagine two scenarios:

- When you use this temp table you need it sorted by c.rid, Row_Number. In this case you will sort the temp table so you don't need to sort your original query, you can calculate this row_number after when needed.

- When you use this temp table you need it filtered by row_number (i.e. Row_number = 1). In this case you can filter data at first, in your original query.

Maybe you use other scenarios but anyway you should detect these scenarios, perceive the type of process you are doing with the temp table, and think about if some process can be optimized in the original query.

Regards,

Francesc