Fatest string comparison using eloquent

137 views Asked by At

I'm importing data using Maatwebsite from an excel file and before creating a new row in my model I check if the register already exists to avoid duplicates. But this takes too long.

In my ProductImport.php:

public function model(array $row)
{
    
    $exists = Product::
        where('product_description', $row["product_description"])
        ->where('product_code', $row["product_code"])
        ->first();
    
    if($exists ){
        return null;
    }

    ++$this->rows;
    
    // Autoincrement id
    return new Product([
        "product_description" => $row["art_descripcion"],
        "product_code" => $row["cui"],
        "id_user" => $this->id_user,
        ...
    ]);
}

public function chunkSize(): int
{
    return 1000;
}

As you see, I'm also using chunkSize, because there are 5000 rows per excel.

The problem:

The size of the product_description varies between 800 to 900 characters (varchar[1000]) and it makes the query (where()) very slow per iteration within the foreach.

Is there a better way to handle this? Maybe using updateOrCreate instead of searching first and then creating? Because I think it will be the same approach.

So the main problem is how do I compare those 800 - 900 size string quicker? Because this search is taking a lot of time to execute:

 $exists = Product::
        where('product_description', $row["product_description"])
        ->where('product_code', $row["product_code"])
        ->first();
0

There are 0 answers