>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.

Well, what does execution plan show? You have indexes in the Table2 and TableStatus on the foreign key, right?

You can re-write the last query as

FROM Table T (NOLOCK)
> INNER JOIN Table2 ON T.NoTable2=Table2.Numero
> INNER JOIN TableStatus ON T.NoTableStatus=TableStatus.Numero
> WHERE exists  (SELECT 1 FROM #Temp tmp WHERE tmp.LXPrimaryKey = T.Numero)

Also, how often do you re-build statistics and with full scan in your tables?

You may also try adding OPTION (RECOMPILE) to your last query and see if the performance improves.