View Single Post
Old 2014-09-11, 03:47   #689
Madpoo
Serpentine Vermin Jar
 
Madpoo's Avatar
 
Jul 2014

2×1,637 Posts
Default

Quote:
Originally Posted by Prime95 View Post
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.
Hmm... I think I see.

I'll email you the SQL query I was using to test (basically pulled from the PHP with static values in place of some variables). I'll also toss out a few proposals for optimizing it.

In the meanwhile if you're having performance issues like that, I'd recommend leaving the min/max values blank. It'll pick the lowest possible values. If you actually want some higher ones, enter that for the minimum value but put in 1 billion for the max. That will keep it running good and fast.
Madpoo is offline   Reply With Quote