>It doesn't have to be a temp table, it can be a CTE, e.g.

CTE results in the same situation.

However, here is something interesting.

This is beyond whatever I had imagined as far as boosting performance on what I have.

We know that the first stage to collect the keys is immediate. As I am only selecting the primary keys, with all the conditions, including JOIN conditions, this has proven to be immediate until now and up to several tens of thousands of records.

The remaining part to optimize was to collect the fields necessary to show in the list. Trying to do it all in one SQL was way out of proportion.

My framework tells me the number of records per table, if it is higher than 1000, then I go in this special logistic.

1. Collect the keys as mentioned above
2. Start to display the list, usually 50 or 100 records at a time
3. For every primary key, do another select to get the fields needed where the WHERE clause is Table.PrimaryKey=that primary key

For 50 to 100 records, this is all processed in 0.000 second. The only difference is that is sollicitates more SQL Server but the performance is completely in a different scale.

I have a very solid framework and well designed. So, I only had to do a few changes here and there.

The problem is resolved and very welcomed.

Thanks for your input, the more brainstorming, the more it helps to find new things to try.