The where exists approach you gave resolves the issue.
DECLARE @NoMember Integer
SET @NoMember=1
SELECT Thread.Numero,Thread.Title,Thread.Url,Thread.AddDate
FROM Thread (NOLOCK)
WHERE EXISTS
(SELECT 1 FROM (SELECT TOP 10 Reply.NoThread FROM Reply (NOLOCK)
WHERE Reply.NoMember=@NoMember ORDER BY 1 DESC) Temp
WHERE Temp.NoThread=Thread.Numero)
Duration: 0.0000 Count: 10
It is even faster than the initial query after an index.
Now, I would like to understand why using WHERE EXISTS is optimized and the other not. This is a mystery.
Basically, I didn't even reindex. I simply ran both queries one after the other and found out that yours was the key. I also ran this:
SELECT TOP 10 Reply.NoThread FROM Reply (NOLOCK) WHERE Reply.NoMember=@NoMember ORDER BY 1 DESC
And, that one was optimized as well. So, basically, the problem was with the corelation with the Thread table. In my test, I ran the following pattern:
1. Ran the initial query (took 1.7 seconds)
2. Ran the query just above in the Reply table only just to see that the query in its table run as is was optimized
3. Executed point #1 (same)
4. Executed point #2 (same)
5. Executed your query (took 0.00 second)