View Single Post
Old 2014-09-10, 20:21   #683
Serpentine Vermin Jar
Madpoo's Avatar
Jul 2014

2×1,637 Posts

Originally Posted by garo View Post
Thanks for looking at this. I tried to get 20 assignments. And it took ~ 2 minutes. Just repeated that right now and same deal. Yes I guess a big part of the problem is repeating the SQL query for each assignment. It should really do only a single query for each request. Note that if I don't specify a range it is more or less instant. (And I get 64M assignments or Cat3). So I don't think the problem has to do with the checkout or PHP and much more likely inefficient SQL.
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).

Trying to get a first-time test actually pulls 3 different exponents on each iteration... The lowest LL candidate, lowest P-1 candidate, and lowest TF candidate, and then picks the one with the lowest "difficulty" rating as the final choice. The difficulty is based on exponent size, whether an LL has already been trial factored or not, etc.

So it does this each time, grabbing 3 types of results and then picking one of them.

By process of elimination (running each of the 3 sub-queries at a time), I saw that the one that picks the smallest TF is the one that slows things down. The other two run splendidly fast, but in that third case, if there are zero matching records, as is the case when using a low range, below where TF work has already been done, it seems to take a long time. Whereas, running that 3rd part with basically no min/max (the default 0 to 1 billion) it finds the smallest one lickety split.

It probably has to do with the way the table is organized or if there's a good index at work. If it has to scan the entire table before realizing there aren't any matching results, yeah, that would take a while. Otherwise without any range, it finds an entry before scanning the whole table.

An interesting little puzzle there. I presume that trying to get "first time work" isn't exclusive to LL tests on purpose. In my mind when I saw "smallest available first time tests" I just assumed first time *LL* tests.

Anyway, in my fictional and artificial test, doing the SQL queries themselves, instead of picking the "top 1" result from each sub query, I could do, for example, the top 12 of each type, as if I were going to try and reserve 12 exponents at once. Still no TF results in that low range, but it easily found 12 P-1 and 12 LL tests, so 24 altogether, and then still sorted by difficulty.

*In theory* if you then took the top 12 of those, it *should* give you the same result as if you went through this 12 separate times, picking the top 1 of each type and then the top 1 of those.

Plus, in whatever SQL magic way, I can get those top 12 results faster than I could (2 seconds) than I could even just getting one at a time (3 seconds). Someone at Microsoft would have to explain that to me... that's like arriving at your destination before you've left, or something.

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.
Madpoo is offline   Reply With Quote