Laravel Upsert 1M Rows into MySQL from CSV: 5 Ways with Benchmarks

Hello guys, in this video I will have a benchmark for upserting 1 million records to existing 1 million records in the database. So imagine this scenario. You have a lot of products, typical e-commerce project and then you have new CSV every day for example with also million records. Some of them updated, some of them are new and stuff like that. And this is kind of a follow-up to my recent YouTube and Laravel daily video about inserting 1 million rows. So I received a few comments requesting follow-ups. So one comment, what about absurd? And this is the topic of this video. and also export 1 million like into CSV that's going to be covered in a future video quite soon. So I will compare five different strategies for such absert. This is the part of that benchmark script. So update or create versus query builder upsert versus eloquent upsert also SQL insert on duplicate and also I will try kind of a creator mode to split the insert and update on PHP level and then separate them in separate SQL statements and I've tried those five strategies on four different CSV files one case 1 million records totally new so 1 million inserts then another file existing CSV V. So all million records but all need to be updated with like new prices or something. Then third option was half new. So 500k new and 500k updated. And then probably the most typical is mostly unchanged which means 900k records unchanged then 50k updated and then 50k new records. So 10% change from the original database. And this was the result Excel sheet with seconds and rows per second for now blurred out but I will show you the results in a minute. And also I've tested on four different environments locally I tested MySQL SQLite posgrsql and also I have Laravel forge cheapest digital ocean $6 per month server and I've compared the same benchmark in local MySQL versus cheap MySQL. local is MacBook Pro M4 which is pretty good hardware. And similarly to that last video about inserting 1 million records, there will be a free and a premium version of this video. In free version here on YouTube, I will talk about MySQL. So I will compare different strategies with four different CSV cases but for MySQL only. And for premium members of Laravel Daily, there's a full 20 minutes video where I also compare SQLite and posgrql and forge remote server. The link to that full video will be in the description below. So all of the numbers and all of the conclusions now in this video. Let's dive in. So first let's benchmark the update or create strategy which means each row has this which means 1 million records are parsed one by one which is very slow. So first let's run this on feed all new CSV and this is the artisan command. I specifically specify limit 1 million because otherwise this command was tested with lower amount of data just to make it faster otherwise it takes minutes. How many minutes exactly? Let's wait for it to finish and I will show you. So I will save this data somewhere but so that you understand five and a half minutes for such import which is very long and this is on my MacBook Pro M4. Imagine the live server. But we have the first kind of benchmark. It's around 3,000 rows per second processed. Now I will try to run the same benchmark but on different CSV file all existing records which means there will be just update. Will it change the benchmark results? Let's see. And this is the result. Even longer, quite a lot longer actually. 438 seconds. And by the way, the difference between absurd time and total time. So total includes CSV parsing and any PHP operations. And absurd is just database operations. So probably in all fairness, we should look at this absurd time as the main metric. But still, we had 3,000 rows processed per second. In this case, it's 2,000. So it depends on the content of that CSV or whatever is your input and then on the contents of your existing database. So it may be slower or faster depending on the situation. Let me try other two CSV files and we will compare the difference case by case. And these are the results from other two files. So have new records and then also after receding the initial data mostly unchanged CSV and a better way to compare those is this Google sheet that I've created and probably this line is the easiest to understand. So see how faster is the file mostly unchanged. So in that case only 10% of 1 million records are changed. 50,000 new rows and 50,000 updated rows which makes the update or create faster just because there's less things to update or create. Just select compare and no update or create operations or fewer operations. So basically the time spent on importing CSV does depend a lot on the contents of CSV and current database and probably this is most realistic scenario. So from 1 million records probably only a few% of records like product prices would be updated on each for example daily update or something like that. But even those almost 3 minutes are quite a lot and again this is on my MacBook Pro. So can we improve on that and for that we have other strategies to test. So the next strategy instead of for each update or create we have upsert on query builder not on eloquent on DB table. Eloquent will be next product upsert and I will explain you the difference a bit later but this is the operation DB table upsert. Here it is in the Laravel documentation of query builder. So this is the syntax but important detail we need to have a primary or unique index on the columns that you search for. So these ones departure and destination in this case and also MySQL ignores the second argument and requires the primary and unique indexes. So in our case we're working with MySQL for now. Skew is the indexed field and in the database migration it is unique. So I launched the same benchmark command with new all new CSV but with different strategy upsert query builder and these are the results after I launched the same command with four different CSV files. These are the results which is better to look at Excel sheet which I expanded and changed because I noticed that there's a huge difference apparently in absurd time and total time. So total time is the time of full script from start to finish and this is what probably clients are interested in how much it would take. It doesn't matter that much how much of that time is absurd specifically. So that's why I left the same table but expanded it to new table and also I updated the seconds but see it's in seconds now not in minutes. So, kind of the first conclusion of this video, see how faster is query builder up absert than line by line update or create. It's probably kind of a no-brainer to understand, but you see the massive difference. But at the same time, another conclusion. See how those times are close to each other, almost equal, a little bit faster with all new records of CSV, but generally it doesn't matter that much what's inside of that CSV that you're importing. The time to process that CSV with upset with query builder is almost the same and probably this should be the actual benchmark we need to take a look at and even with rows per second you can see massive difference so 10 times faster except for of course mostly unchanged file which was faster to process with update or create. Now absert with eloquent and what is the difference between absert query builder and eloquent. Let's see that in action and let's take a look at the result of eloquent absert. This is the terminal and in the excel sheet this is the data and you may notice that absert with query builder and with eloquent is almost identical on time at least in this specific scenario. But there are a few things, a few gotchas that you need to know about what you lose with that speed versus update or create and what is the actual difference between those two. So let me show you first. With both Eloquent and Database Query Builder update, the updated ad field is updated automatically even if nothing was changed. So here's the record number one which wasn't changed in that absurd but updated at. This is the database behavior is updated. Anyway, this is the automatic behavior of SQL query that is run by Laravel and I haven't found any setting or something that would suppress that. You can suppress that by running that query manually. There's a syntax but if I understand correctly there's no setting on Laravel level for that. Also important note strategy query builder created at and updated at are not automatically set. So that's why you need to do that manually and if you don't do that this is the example of original record and then on top I see that million more records and those new records are without timestamps and this is the main difference between query builder and eloquent. If I comment that out in eloquent case the timestamps for new records if I refresh that database table the timestamps are set successfully. So this is the difference between eloquent and query builder. But the similarity and probably the most important differentiator between those two and update or create is that in case of absurd sorry for my voice losing it but what I want to say is that eloquent events and observers are not fired in absurd. So if you have model observers with events like created or creating or something like that or event listeners those will not be fired and this is exactly the reason of the speed with update or create each eloquent record is processed including those events and observers if they are present. The next strategy to talk about is called insert on duplicate. And this is a different syntax for MySQL and posgrql and SQLite. But let's take a look at MySQL. Basically, you're building insert query yourself which looks like this. Insert values on duplicate key update. So it's kind of the same absurd but on MySQL level on database level without Laravel or Eloquent and of course Eloquent events do not fire either. And the benchmark shows these numbers. So insert on duplicate is slightly very minimally faster than absert because it's basically doing the same thing and those upset query builder and eloquent add a little of overhead about Laravel. In this case it's just DB statement which is slightly faster again a little faster on new records but the difference is not that significant. But keep in mind in this case you need to manually build all the values take care of the sanitation of that validation question mark parameters and stuff like that. So this is a bit more risky to perform without too much benefit as my benchmark shows at least on my SQL. And now let's talk about one more strategy which I would call like get creative. If you don't want to call Eloquent or Laravel or BuildSQL, maybe you want PHP to do the work of splitting the data into actionable and non-actionable and then work only on the actionable data to perform SQL queries. And I call that strategy smart split. And the idea is that maybe if we filter out the data, then it would be faster. Let me show you what was the end result. So basically we're querying existing data first to group into insert count and update count and then we perform one SQL for insert operation and then bulk update which is another private function which looks like this. So when then and case else is added to update and set and where. So huge update statement but one statement per batch and batch in this case is thousand rows. And when I tried to launch this on those four different CSV files the results for those files were very different because obviously it depends on how many operations do we need to have insert and update at the end. But the main part was that preparation time loading the million records parsing them took 20 seconds alone. And then the rest of the operations insert and update were added on top of those 20 seconds. In some cases they were much slower. So this is the end result. As I said massively different between the files. So for example if we count just the amount of operations. So mostly unchanged CSV takes only 5 seconds for SQL which is logical and could be considered a win but altogether it still with all operations takes 45 seconds again on my MacBook Pro M4 with good processor so PHP operations should be fast too but still it wasn't fast even in that scenario altogether and in other scenarios it was much slower especially with updating all the existing records. So instead of half a minute we achieved on absurd operations smart split was at least a bit slower sometimes slower up to four times. So at least in this case my experiment to offload the work to PHP and to memory wasn't successful. On top of that it also consumed more memory. So you can see peak memory here which is a risk for your server to just run out of RAM which will cause even more issues. So yeah you can call it myth busted and probably database is exactly built for that as a tool to load the data and work with data instead of doing that in PHP and in memory. So that's why I didn't even fill the last line of rows per second because it doesn't really make sense in smartsplit experiment. And this is the end of free part of this video. The benchmark and the conclusions for MySQL. But for premium members, as I mentioned in the beginning of this video, there are other Excel sheet tabs for local SQLite, local posgrql, and also remote forge server. Cheapest digital ocean for $6 per month. how slower it is. I've measured and if you want to see that full 20 minute video and if you want to support my benchmarks like this one, the link will be in the description below. It's for premium members of Laravel Daily. That's it for this time and see you guys in other