findFirst super slow inside large loop

So I have to use QueryBuilder for that ?

use something like:

$modelsManager = $this->modelsManager;

     $dir = new FilesystemIterator("files/path/to/my/files/");
     foreach ($dir as $file) {
         if ($file->getExtension() == 'json') {
             $filename = $file->getFilename();
             $explode_filename = explode("_", $filename);
             $date = $explode_filename[0];

             $unformatted_date = DateTime::createFromFormat("Ymd-His", $date);
             $date_server = $unformatted_date->format("Y-m-d H:i:s");

             $timestamp_app = $explode_filename[2];
             $date_app = date("Y-m-d H:i:s", $timestamp_app/1000);
             echo $date_server;
             $json_data = json_decode(file_get_contents($file), true);

             $query = $modelsManager->createQuery("SELECT * FROM Scans WHERE name = :name:");
             $cars  = $query->execute(
               [
                    "name" => $filename,
               ]
           );

             if  (!$scan) {
                 ...
             }
         }
     }

Wow this is really fast thanks you. PHQL give a good performance !! I did something like this :

$scan = $this->modelsManager->createBuilder()
                                ->from("Scans")
                                ->where("name = :name:", ["name" => $filename])
                                ->limit(1)
                                ->getQuery()
                                ->execute();

There is a difference of performance between createQuery and queryBuilder ?

I'm not expert, so I can't tell you.

https://docs.phalcon.io/en/3.0.1/reference/models-cache.html#caching-of-phql-planning

Maybe quertBuilder offer the same approach ...

Okay. Thanks for you help :)

There is a difference of performance between createQuery and queryBuilder

https://docs.phalcon.io/en/latest/reference/phql.html#creating-queries-using-the-query-builder

in fact, query builder is just anther way to build phql query, so finally, I guess so

Hum, in fact I got the same performance :/. I made a mistake previously when I used queryBuilder and it was ignored. That is why I had "good perf". But now it's super slow again :(

look at the example that is shown in the doc I've mentionned.

What is important, is the way you call the query builder (or phql query) in your foreach loop.

See that post also: https://forum.phalcon.io/discussion/15027/allowed-memory-size-of-134217728-bytes-exhausted-after-many-mode

I wrote you solution on stackocerflow, instead of doing one find 4000 times find all scans or 1000 or more depending on memory limit at once and filter resultset to check if your filename what you want exists. I will try to post solution for you soon.

I think the use case from @Johngtrs is close from the example given in the doc: https://docs.phalcon.io/en/latest/reference/models-cache.html#caching-of-phql-planning

<?php

$phql = "SELECT * FROM Store\Robots WHERE id = ?0";

$query = $this->modelsManager->createQuery($phql);

for ($i = 1; $i <= 10; $i++) {
    $robots = $query->execute(
        $phql,
        [
            $i,
        ]
    );

    // ...
}

$dir = new FilesystemIterator("files/path/to/my/files/");
$fileNames = [];
foreach ($dir as $file) {
    if ($file->getExtension() == 'json') {
        $filesNames[] = $file->getFilename();
    }
}

$existingFileNames = Scans::find(
    [
        'columns'    => 'name',
        'conditions' => 'name IN ({names:array})',
        'bind'       => [
            'names' => $fileNames,
        ],
    ]
)->toArray();
// maybe you could even remove this condition or use name IS NOT NULL, your choice

array_walk(
    $existingFileNames,
    function (&$v) {
        $v = $v['name'];
    }
);

foreach ($fileNames as $fileName) {
    if (!in_array($fileName, $existingFileNames)) {
        $explode_filename = explode("_", $fileName);
        $date = $explode_filename[0];

        $unformatted_date = DateTime::createFromFormat("Ymd-His", $date);
        $date_server = $unformatted_date->format("Y-m-d H:i:s");

        $timestamp_app = $explode_filename[2];
        $date_app = date("Y-m-d H:i:s", $timestamp_app / 1000);
        echo $date_server;
        $json_data = json_decode(file_get_contents("files/path/to/my/files/".$fileName), true);
        // do rest of stuff
    }
}

How about something like this? Keep in mind to add index on name column.

If you are expecting that Scans::find will return big resultset then you could use code like this:

$dir = new FilesystemIterator("files/path/to/my/files/");
$fileNames = [];
while($dir->valid()) {
    $i = 0;
    $file = $dir->current();
    $filesNames[] = $file->getFilename();
    $i++;
    if($i == 1000) { // change this number to your needs
        $existingFileNames = Scans::find(
            [
                'columns'    => 'name',
                'conditions' => 'name IN ({names:array})',
                'bind'       => [
                    'names' => $fileNames,
                ],
            ]
        )->toArray();
// maybe you could even remove this condition or use name IS NOT NULL, your choice

        array_walk(
            $existingFileNames,
            function (&$v) {
                $v = $v['name'];
            }
        );

        foreach ($fileNames as $fileName) {
            if (!in_array($fileName, $existingFileNames)) {
                $explode_filename = explode("_", $fileName);
                $date = $explode_filename[0];

                $unformatted_date = DateTime::createFromFormat("Ymd-His", $date);
                $date_server = $unformatted_date->format("Y-m-d H:i:s");

                $timestamp_app = $explode_filename[2];
                $date_app = date("Y-m-d H:i:s", $timestamp_app / 1000);
                echo $date_server;
                $json_data = json_decode(file_get_contents("files/path/to/my/files/".$fileName), true);
                // do rest of stuff
            }
        }

        $i = 0;
        $fileNames = [];
    }
    $dir->next();
}