View Single Post
Old 2014-09-10, 21:24   #684
Prime95
P90 years forever!
 
Prime95's Avatar
 
Aug 2002
Yeehaw, FL

23·311 Posts
Default

Quote:
Originally Posted by Madpoo View Post
It's an interesting thing for sure. If the range min/max are left blank, it actually does the same query but with default values of 0 and 1 billion for the min/max. And for whatever reason, that actually does execute a lot faster (sub-second).
It could probably be made even quicker with a few other minor tweaks or doing it as one single query rather than 3 sub-queries unioned together but in the back of my mind I think George will post a reply and tell me why it does it the way it does.
That query was a bear to optimize in SQL2005. If you save the query in c:\code\badquery.sql I'll take a look at it. I do this by using "Display Estimated Execution Plan" to determine if MSSQL is using the best possible index.

The reason 3 queries are unioned together is we must find the smallest (or close to it) exponent available. For an LL request, we can hand out an exponent that is marked available for TF, P-1, or LL.

Since we prefer to hand out an LL assignment if we can, the difficulty column is a manufactured column that helps us do this. Difficulty groups all exponents in the same million range together, then adds a value so exponents needing P-1 and TF sort higher than ones not needing LL. An example, if M60999999 is ready for LL it has a difficulty of 60000. If M60000001 needs 3 more levels of TF then it has a difficulty of 60003. Thus, requesting an LL test will return 60999999 rather than the smaller 60000001 because of the smaller difficulty value. Such shenanigans are mostly obsolete now as GPU72 as pretty much assured that all exponents have been TFed.
Prime95 is offline   Reply With Quote