Performance for creating millions of database entries class Generator
{
    public function createCodes($count, $project, $generator)
    {
        $batchSize = 250;
        $batches = ceil($count / $batchSize);

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

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

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

                for ($j = 0; $j &lt; $generator-&gt;children; $j++) {
                    $inserts[] = [
                        'type' =&gt; 'child',
                        'parent_code' =&gt; $parentCode,
                        'code' =&gt; self::generateCode(),
                        'series' =&gt; 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()-&gt;prepare($queryString);
            $query-&gt;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.
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.