Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Index become non optimized after a while
Message
 
 
To
18/07/2010 11:46:25
General information
Forum:
Microsoft SQL Server
Category:
Database management
Environment versions
SQL Server:
SQL Server 2008
Application:
Web
Miscellaneous
Thread ID:
01471721
Message ID:
01472862
Views:
27
>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
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform