0
\$\begingroup\$
class Generator
{
    public function createCodes($count, $project, $generator)
    {
        $batchSize = 250;
        $batches = ceil($count / $batchSize);

        $sharedColumns = [
            'timestamp' => time(),
            'parent_id' => $project->id,
            'source' => $generator->name,
            'generator_id' => $generator->id,
        ];

        $created = 0;
        for ($batch = 0; $batch < $batches; $batch++) {
            $size = min($batchSize, $count - $created);

            $time = time();
            $inserts = [];
            for ($i = 0; $i < $size; $i++) {
                $parentCode = self::generateCode();
                $inserts[] = [
                    'type' => 'parent',
                    'parent_code' => '',
                    'code' => $parentCode,
                    'series' => self::generateSeries(),
                ];

                for ($j = 0; $j < $generator->children; $j++) {
                    $inserts[] = [
                        'type' => 'child',
                        'parent_code' => $parentCode,
                        'code' => self::generateCode(),
                        'series' => self::generateSeries(),
                    ];
                }
            }

            $columns = array_keys($sharedColumns + $inserts[0]);
            $valueString = '(' . implode(', ', array_fill(0, count($columns), '?')) . ')';
            $queryString = 'INSERT INTO project_codes (' . implode(',', array_map('Database::quoteIdentifier', $columns)) . ') VALUES ';
            $queryString .= implode(',', array_fill(0, count($inserts), $valueString));

            // Flatten all entries and add shared columns
            $values = array_reduce($inserts, function ($carry, $item) use ($sharedColumns) {
                return array_merge($carry, array_values($sharedColumns), array_values($item));
            }, []);

            $query = \Database::getInstance()->prepare($queryString);
            $query->execute($values);

            $created += $size;
        }
    }

    static $codeChars = 'ABCDEFGHJKLMNPQRTUVWXY34789';

    protected static function generateCode()
    {
        return substr(str_shuffle(self::$codeChars), 0, 9);
    }

    protected static function generateSeries()
    {
        $numbers = range(1, 30);
        shuffle($numbers);
        $numbers = array_slice($numbers, 0, 5);

        return implode(',', $numbers);
    }
}

The software this code is used in manages prize draws. Companies let the software generate codes that they can e.g. print on their products. Buyers of these products can enter codes back into the system to enter a prize draw. At specified dates the system draws winners from the entered codes and notifies them.

What this particular piece of code should be capable of is to generate a specified amount of random (and unique) codes so they can later be exported by the company and printed on their products. The generated code is stored in the code column, but winners are determined by the series column which is hidden from the end user/customer.

The code doesn't yet guarantee uniqueness. My plan is to select all duplicates for code and series (after generation and inserts) and just generate new ones until there are no duplicates anymore. That way I don't have to check each of the million generated codes for uniqueness.


I tested this with a count of 1 million, which took 163s to complete and according to memory_get_peak_usage(true) consumed 4MB of RAM (which I find unlikely, but ok).

I experimented with different batch sizes but the gain of fewer queries seemed to pretty much cancel out with the additional function calls and higher array sizes for the array reduction.

Is there anything I can do to decrease execution time? I don't expect a million entries to be generated in 10 seconds, but if there's any gain to be had I'd appreciate it.

\$\endgroup\$
4
  • \$\begingroup\$ To review your code, we need to know what it's for. Not just "creating database entries", but what kind of entries, for what purpose - give us the real-world motivation for what you've written. It will probably help to provide the table schema, too. Also, did you really call the table my_table? This looks more like stub/example code than something from a real project - see help center for guidance. \$\endgroup\$ – Toby Speight Nov 2 '18 at 12:57
  • 1
    \$\begingroup\$ If the table is a InnoDB table I would suggest starting a transaction before you start the inserts and commit every few thousand inserts or even wait until you've done all of them. \$\endgroup\$ – Dave Nov 2 '18 at 13:08
  • \$\begingroup\$ @TobySpeight This is not example code. I just renamed some columns and the table name to make it easier to understand without explaining what every column is for \$\endgroup\$ – Padarom Nov 2 '18 at 13:09
  • \$\begingroup\$ @Dave Currently it is a MyISAM table, but I'll check if switching to InnoDB is an option. \$\endgroup\$ – Padarom Nov 2 '18 at 13:10
1
\$\begingroup\$

Prepare once. That's the very idea behind a prepared statement. It should give you like 5% gain.

Wrap all batches in a single transaction. It could also help you in several ways. In case your engine is myisam, consider dropping all indexes before inserts and adding them back after.

Reduce the record size. 'parent' is 5 megabytes written on the disk for 1 mil records 1 megabyte if your value is 1.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.