I averaged out the times from each test, which ended up as follows: I spun up a new server on Linode (with PHP 7.4 & MySQL 8) and ran the artisan command 5 times. This updated raw data is then also written to the database whilst being timed. Every item in the original array of product data is then updated with a new price, and a further 25,000 products added. To test the performance of the new upsert() method, I started a new Laravel project and created a simple artisan command that performs 3 tests - one using the updateOrCreate method, one using the upsert() method for singular rows, and one for using upsert() method with batches of rows (5,000 at a time).įor each test, an array of 125,000 "products" are written to an empty table - measuring the time it takes to complete. Once complete, it will move onto the next set of data values. This will update the existing database row with the set of values. The SQL following that, in this case an UPDATE statement, will then get run. However, if that fails because the unique identifier (in our example, that's "item_ref") already exists (i.e the row of data is already in the table), then it will be caught by the ON DUPLCIATE KEY part of the query. If that's successful, it will remove on to the next set. I'm going to assume you're using MySQL, although it does work with other database engines.įollowing on from our example of a products database, simplified down for a batch of just 3 products, this is the SQL that would be run: INSERT INTO productsĪs you can see, the query starts with a normal INSERT statement and it will attempt to insert the first set of data values into the table. This way we will only execute 30 queries rather than 300,000 if we'd used the updateOrCreate() method (remember, it's actually 2 queries for each use of it). We can then pass each batch of product data to the the new upsert() method on the Illuminate\Database\Query\Builder class, which for each row of data in the CSV file will either update an existing product in our database, or create a new one. Rather than using the updateOrCreate() 150,000 times for each row in the CSV file, let's start by breaking down the CSV file into smaller batches, of say 5,000 products. To do this, we need to know which products we already have, and which are new so we can perform either an INSERT or an UPDATE query. In our database we need to update the existing products and insert the new ones. No problem, we can use the insert() method on the query builder to insert multiple rows at the same time.įast forward to 4 weeks later, we have a new CSV file of product data - the details of the 125,000 products have been updated and 25,000 new products have been added. So let's imagine we have a CSV file containing data for 125,000 products that we needed to import into our database as efficiently as possible. Behind the scenes, this actually executes two queries in the database - one to check for the existing entry and another to perform the INSERT or UPDATE - so that would mean running 250,000 queries. However, this could only be used on a single row / model at a time - if we had 125,000 rows to insert or update, we'd have to call that Eloquent method 125,00 times. We also had the updateOrCreate() method on Eloquent models which given a set of attributes, updated an existing model, or if it didn't exist, it created it. There was also a lesser known insertOrIgnore() method which would insert a batch of data, ignoring any errors (such as a unique key already existing). Up until now, the Laravel database query builder supported the insertion or updating of multiple rows (batches of data) at once via the insert() and update() methods. TLDR: Why use it?, How does it work?, How does it perform? How do I use it? A practical example of using upsert() This allows you to perform "insert or update" queries far more efficiently - which will help when importing large batches of data. Released on 13 October 2020, Laravel 8.10 came with the addition of the new upsert() method on the database query builder (and therefore Eloquent based queries too).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |