For those of you who were here back in 2004 and 2005, you may remember how the Submissions page took forever to confirm a page of stats. As I recall, it took over 1,000 database calls to confirm a single stat! Rolken optimized that some time ago, but it still took quite a while if you submitted over 50 stats at once. Last night, I looked over the processing code to figure out why it was taking so long. It turns out that there are (were) two major performance problems:
1. When inserting queue updates, the script built a long string using the PHP concatenation operator and then sent it to the database. This seems like it's more efficient, since it only does one query, but it's actually worse because the database has to process a long string.
2. The queue system checked the temporary queue for duplicates before adding new stats. The checking system was linear; that is, it checked all elements in the array to see if there were any duplicates. This works fine for a few stats, but the time grows exponentially as more stats are added to the list.
How I fixed both of these:
1. Instead of building a huge string, use prepared statements. This makes it so the script only sends the query once, and then it simply sends the data. The data is sent in binary form, so it's much faster for the database to process. I've converted the biggest user of this method so far, and it knocked off around 20 seconds (~120s vs. ~140s) for processing 100 suspended stats. There's still some more functions that are using the old concatenation method that I need to convert later.
2. Maintain two arrays; one with the original data, and one as an indexing array. This increases memory usage, but it changes execution time from exponential to linear.
Overall: TSC should now be able to confirm your submissions faster. :)
