laravel too slow to update 1k records

That really shouldn't take that long. I do imports of ~1.000.000 rows (csv) in 10-20 minutes with a lot more data / logic / queries.

Are the indexes in mysql ok? (on the where part of the products table and the product_id on both the other 2)

Is DB logging / debugging off while importing? (This can add more time and memory allocation)

Yes, try disabling the query log before large imports: \DB::disableQueryLog(); or \DB::connection()->disableQueryLog();

I am not using query log or debugging. Yes, I am using MySQL and I used artisan to create tables in MySQL.

Schema::create('products', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->charset = 'utf8';
        $table->collation = 'utf8_unicode_ci';
        $table->increments('product_id');
        $table->string('seller_id');
        $table->string('EAN')->nullable();
        $table->string('Model')->nullable();
        $table->timestamps();
    });

Schema::create('products_description', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->charset = 'utf8';
        $table->collation = 'utf8_unicode_ci';
        $table->increments('product_description_id');
        $table->string('product_name')->nullable();
    ...
        $table->unsignedInteger('product_id');
        $table->foreign('product_id')->references('product_id')->on('products');
        $table->timestamps();
    });

Schema::create('products_attributes', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->charset = 'utf8';
        $table->collation = 'utf8_unicode_ci';
        $table->increments('product_attribute_id');
        $table->integer('quantity')->nullable();
        $table->double('price')->nullable();
        $table->double('ship')->nullable();
    ....
        $table->unsignedInteger('product_id');
        $table->foreign('product_id')->references('product_id')->on('products');
        $table->timestamps();
    });

ok, the tables are setup properly so there shouldnt be any issues there. That makes me think the problem is within the mysql server itself.

Have you used this server setup before with better speeds? (i've had terrible results on some AWS instances and local low memory setups).

Since it's an easy migrationscript, you could try to find out if it's eloquent models that are making it slow by trying it via pdo / DB class to circumvent eloquent. ( $pdo = DB::connection()->getPdo(); and use it from there ) If it's not much better, then mysql / server setup is at fault and we can look at that.

You could also add some measurements in the import script to see how much time is being spent on each of those 3 queries in total to see if there's a big difference between selecting and creating / updating (if there is there are mysql settings to improve this)

In the meantime:

• what are the server specs? (cpu / memory)
• what is the load during this import (top command) in cpu / memory for both php and mysql?
• what array data is passed in the $whereColumn & $updateORNewInsertColumns ?

btw you are increasing the load on mysql/php by splitting products over three tables when you dont really NEED TO.. having looked at your migration ONE table will suffice..

in my experience for some reason php/laravel was TOO slow on my local machine but on server it was quite fast.

the other things:

1. if you are doing inserts then do batch inserts.
2. try doing RAW sql update to see if that speeds it up.

show us the whole script if you can

Hey @lostdreamer_nl, @shez1983

I do update DB via iterating(loop for single query)...

uname -a : Linux newpixel24 4.4.0-116-generic #140-Ubuntu SMP Mon Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

processor       : 0 (4 processors in total)
vendor_id       : GenuineIntel
cpu family      : 6
model           : 42
model name      : Intel(R) Xeon(R) CPU E31220 @ 3.10GHz
stepping        : 7
microcode       : 0x1b
cpu MHz         : 2094.921
cache size      : 8192 KB (x4)

Ram is 16gb;

TOP command: http://prntscr.com/is5iic

$updateORNewInsertColumns is $product

$products["seller_id"] = $value["seller_id"];
$products["EAN"] = $value["EAN"];
$products["updated_at"] = Carbon::now()->toDateTimeString();

$whereColumn is just = ["seller_id" => $value["seller_id"]]
$result = Products::createORUpdate("App\Model\Products", ["seller_id" =>$value["seller_id"]], $products);
$product_id = $result->getKey();

part of script http://prntscr.com/is5pbw

I would start by commenting big chunks of the code out and see how long it takes to just iterate over your source data

I am not using query log or debugging.

You might not explicitly be using it, but it's on by default. Try turning it off as I suggested and seeing if it speeds things up. It definitely does on my end and saved a lot of memory as well.

@Snapey it takes 5-8sec to iterate all lines of .txt file

you marked it solved?

@Snapey by mistake. Sorry I don't know how to remove that one.

@Cronix Why would query logging be on by default? I'm pretty sure it's off by default in my projects. You can always check with DB::logging() and throw an exception if it's on before doing mass database updates.

@phoenix404 I don't see anything in your sample code that points in that direction but are you doing anything that requires time consuming calculations like validation of a bcrypt hash, remote I/O / network requests, or something similar?

How do you call that import, console command, HTTP request, ...?

So you can now introduce functionality and see where the performance is killed?

@skliche Hallo, No, I am doing it nothing bcrypt or remote... I am reading file .txt which has 25-30k records (I think, it takes 5-10 seconds) and uploading as shown in that picture. I am importing that with HTTP request called in crontab. Crontab runs call it three times in a day morning, afternoon and in evening.

@skliche I don't mean logging like it's saving queries to log files. It saves them in memory. They removed it from the manual the last few releases, but here's where it talks about it in the older docs and it still applies. https://laravel.com/docs/4.2/database#query-logging

Do some benchmarking with it on (default) and run the same benchmarks after it's disabled. It's not really noticeable on regular small queries, but it sure does slow down after thousands in a loop like the OP is doing. I'm inserting hundreds of thousands of real estate records at a go, and it was overrunning php memory until I discovered this, and it sped things up as well. I thought there was a memory leak as it was killing 500M of ram after about 20 minutes. After I discovered this it completed 500k records and didn't use more then 8M for the whole thing. It was also a lot faster.

By default, Laravel keeps a log in memory of all queries that have been run for the current request. However, in some cases, such as when inserting a large number of rows, this can cause the application to use excess memory. To disable the log, you may use the disableQueryLog method:

Another thing that really speeds up inserts is bulk inserts. I'd try that as well. Insert 100 records at a time instead of just one.

@Cronix Thanks for answering. I don't know understand what I have to do?

If I try to upload in bulk (let's say 100) how can I do that? Because I am not sure that the record exists or not in DB yet. I am using updateOrCreate().

My main "table" is products. So first I check in that table whether a product id exists or not. If it doesn't exist it creates a new id.

   $result = Products::createORUpdate("App\Model\Products", ["seller_id" =>$value["seller_id"]],  $products);
   $product_id = $result->getKey();

    //genralmodal::createORUpdate()
    public static function createORUpdate($model, array $whereColumn, array 
    $updateORNewInsertColumns)
    {
         return $model::updateOrCreate($whereColumn, $updateORNewInsertColumns);
    }

Once I get Product id then I run the following lines:

   ProductsAttributes::createORUpdate("App\Model\ProductsAttributes", ["product_id" => $product_id], $pAttr);
   ....
   ProductsDescription::createORUpdate("App\Model\ProductsDescription", ["product_id" => $product_id], $pDesc);

I have run/test on localhost windows 7 and xampp php7.1.*. It is still running for last 35mins.

Please, Guys, Help me.

How can I do in bulk if it can speed up? How should be array structure that laravel accept to update or insert in bulk?

you cannot insert in bulk if you need to check for existing records

make sure seller_id and product_id columns are indexed

its probably quicker to check if product exists already and then create or update as separate steps.

you can then update or create the other models without having to query first, and removing two queries per loop

Thanks for the answer. Should I add an index in every table or just product table? (ALTER TABLE products ADD INDEX product_id (product_id))

What do you mean? How? "the other models without having to query first, and removing two queries per loop" Little bit code if you can, please.

any table where you are doing a where should have an index in the column being searched.

So it appears all three table might need indexes

second point;

What I mean is, if you know that the product is new then you will need new records in the other tables also.

If you find an existing record in the first table you will use update for the other two tables

using updateOrCreate might be convenient but its not as efficient as it could be.

@phoenix404 How did you fix it? Facing similar situation