Why i getting duplicates when i import XML?

user_3598779 0
Viewed 194

I spent half a day to understand and I don't know what to do. I have an XML file with 146 565 records of name and class with import XML

    public function parse() {
        $start = microtime(true);
        $q = 1;        
        while ($this->reader->read() && $this->reader->name !== 'licenses');
        while ($this->reader->localName === 'licenses') {
            $node = simplexml_import_dom($this->doc->importNode($this->reader->expand(), true));
            $inn = (string) $node->inn;
            $name = (string) $node->name;            
            $this->service->create($name, $inn);
        }
        echo 'Time script: ' . round(microtime(true) - $start, 4) . ' sec.';
    }

when i use echo $name instead of $this->service->create($name, $inn); it works fine. I get 146 565 names on my screen. But when I add it in MySql with my service, the script doesn't stop. When I to a test file with 1000 lines it imported into the database well.

MariaDB [dbbase]> SELECT COUNT(1) FROM names_table;
+----------+
| COUNT(1) |
+----------+
|   248778 |

My service

public function create(string $name, string $inn): bool {

        try {
            $this->sth->execute([
            ':uuid' => Uuid::uuid4(),
            ':name' => $name,
            ':inn' => $inn
        ]); 
        return true;
        } catch (RuntimeException $e) {
            die($e->getMessage());
        }
    }

How to solve it? I understand than it is something wrong with service but i don't know what to do. I try without return true and with it - script doesn't stop. P.S. Added id (primary, auto_increment) instead of uuid - result the same.

xml-parsing php
1 Answers

Begin by splitting the processing in two parts:

public function parse() {
        $start = microtime(true);
        $q = 1;        
        while ($this->reader->read() && $this->reader->name !== 'licenses');
        $pairs = [ ];
        while ($this->reader->localName === 'licenses') {
            $node = simplexml_import_dom($this->doc->importNode($this->reader->expand(), true));
            $pairs[] = [ (string) $node->name, (string) $node->inn, Uuid::uuid4() ];
        }
        echo 'Time script: ' . round(microtime(true) - $start, 4) . ' sec.';

        foreach ($pairs as $pair) {
            list($name, $inn, $uuid) = $pair;
            print date('H:i:s') . " inserting {$name}\n";
            $this->service->create($name, $inn, $uuid);
        }

        echo 'Time script: ' . round(microtime(true) - $start, 4) . ' sec.';
    }

If the problem is that the inserts take too long, you can change your query strategy by having two prepared statements, one with say 200 pairs, one with a single one, so that you can insert multiple lines with a single INSERT. That should accelerate things nicely. Otherwise, there are more gruesome solutions :-).

Notice that above I have offloaded the UUIDv4 creation to the first cycle, which requires you to change the service create call to read uuid from the parameters. This is because I once hit a beautiful bug in the use of the PHP random_bytes() function, that blocked the system if the source of randomness (which could have been /dev/urandom but actually somehow was /dev/random) depleted.

If the revised code blocks in the first loop (before any SQL code got even called), then it is your UUIDv4 call that is flawed and we can see about fixing it.

user_14286790
Related