I thought I had it working but I discovered something.

The tests were done on cache environment so it did not provide the real observations.

Once a particular SQL is done, SQL Server caches that data so it'll be faster to retrieve it on subsequent requests. So, my tests were being done on a date time for a specific month (from the first day to the last day) and as soon as I changed for another month that I didn't use before, it went extremely slow. Basically, the retrieval of keys is extremely fast. This is not the problem. The problem is on the second request to collect all the fields based on usage of that #Temp table. This takes up to 2 minutes to retrieve 11000 records. It takes a fraction of a second to get the keys. But, it is very slow on the collect of fields.

So, I thought I would create an index on the #Temp field. That did not change anything. It took another 2 minutes to retrieve the 11000 records even with an index on #Temp.PrimaryKey.

Basically, the logic is as follow:

DECLARE @Success Bit
DECLARE @LXListOverallFilterModDate DateTime
DECLARE @LXListOverallFilterModDate2 DateTime

SET @Success=1
SET @LXListOverallFilterModDate='2013-08-01 00:00:00'
SET @LXListOverallFilterModDate2='2013-08-30 23:59:59'

SELECT Table.Numero AS LXPrimaryKey INTO #Temp
 FROM Table (NOLOCK)
 INNER JOIN Table2 ON Table.NoTable2=Table2.Numero
 INNER JOIN TableStatus ON Table.NoTableStatus=TableStatus.Numero
 WHERE (Table.Success=@Success) AND Table.NoField=1 AND Table.ModDate>=@LXListOverallFilterModDate AND
  Table.ModDate<=@LXListOverallFilterModDate2

CREATE CLUSTERED INDEX LXPrimaryKey ON #Temp(LXPrimaryKey)

SELECT Table.Numero AS LXPrimaryKey,Table.AddDate,Table.ModDate,Table2.Something,'' AS Output2,'' AS Input2,
 TableStatus.Title_E AS Status,'' AS Message2,Table.Process,Table.Numero,Table.Output,Table.Input,Table.Message,
 Table.NoTableStatus
 FROM Table (NOLOCK)
 INNER JOIN Table2 ON Table.NoTable2=Table2.Numero
 INNER JOIN TableStatus ON Table.NoTableStatus=TableStatus.Numero
 WHERE Table.Numero IN (SELECT #Temp.LXPrimaryKey FROM #Temp)
 ORDER BY Table.ModDate DESC

The last SQL is where this is taking long. On the previous approach, when I was not going into a temporary table and getting they keys from a WHERE IN clause, this was fast. It is difficult to understand why that method was faster than this one. However, the previous method was not bullet proof as it was failing when there was too many keys. So, I need to find what is causing this with this one. I believe this is in the good direction to do it. There is just something that need to be improved.