>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)
You can only understand the difference if you compare execution plans for these queries.
There are blogs on this issue (one by Gail Shaw) and another by Brad Schulz, that may shed some light on the issue.
I'll invite Brad to give his 2 cents here.
If it's not broken, fix it until it is.
My Blog