Laravel DB Optimization: From 1.5k to 26 Queries

hello guys in this video I will give you a practical example of optimizing the eloquent performance in terms of amount of queries based on example of laravel translations package which we've been talking about for almost a week now on this channel and I found another thing to optimize in this import translation method this part launches more than 1 000 queries to the database so in this video we will measure the exact amount of queries and perform three steps to optimize it to at least a few hundred instead of a thousand let's go step by step so our goal is to optimize the handle method of that Artisan command and by default what this command does and I will do that with fresh every time to recreate the same behavior it Imports the translations from your resources Lang files or Lang files into the database so if we refresh now we have 104 languages 127 phrases and translations so that is all imported and if we launch that again with fresh it would sink meaning looking for a change and then re-import that but currently it does that with too many queries to the database and how do we measure that I will use a tool called spots array it's not a free tool but I'm not getting paid for that it's not an affiliate video or anything I just like to use that because it has such a feature called Ray count queries and then inside you have callback function and it would count the amount of database queries inside of that function so I just cut and paste into here and that spicy array package is already installed in my composer Json you can see it here and Ray itself is a desktop application on my Mac so it would show the amount of queries when we launch that command again so let's do exactly that and this is how we'll calculate the queries I'm not using laravel debug bar because we don't have any visuals we have Artisan command so in my case Ray is a better solution you may want to use lateral telescope or any other Alternatives that you wish now I will relaunch that import fresh with truncating all the previous translations and phrases and the amount of queries is 1 and 500. it's not a huge amount of time so one second but still the amount of queries is totally unacceptable and let's try to improve that unless debug step by step what is actually happening here import languages is already optimized in my previous video when I made a pull request to that package this is just a few queries believe me now truncating the tables is also just a few queries just above truncating the old records nothing major here this is what causes the biggest amount of queries now typically in the past when I was shooting videos about queries in the database and the amount of queries it was usually related to n plus one query problem so called n plus one with relationships which means loading too many queries for each of the record and even in one of my courses called better eloquent performance I have a separate even section on N plus one query problem with different examples I will link that of course in the description below if you want to check that out but in this case it's not about n plus one query it's just about the logic of launching too many queries in for each loops and repeating some of the stuff although it's not really needed so step by step create or get Source language first thing I've noticed here when creating that language this is one database query but that is fine creating the translation for a language and then it's calling sync translations which is the main function of that whole Artisan command sync translation from translation to a new language but what I've noticed that this sync translation is being launched twice so first when creating the translation it sings the translations but then for all the available locaos it does sync translations again those locales come from just looking at the folders of Lang and just collecting all the languages so en language Spanish would be e s d e for Germany and stuff like that so array of those languages these are locales but the main Locale which is created here is also repeated here so we're syncing the translations for the en which is the main language and then en is also inside of locales and we're syncing the translations again so first thing I would do is optimize to not relaunch sync translations for English language or whatever is your main language and I came up with an if statement what we need to check here if Locale which is a string so e n is not equal to translation language code only then we need to launch sync translations so that's the first optimization and let's try to relaunch our Command import fresh yes and how many queries Rey will show us it's 777 so as I predicted it's twice faster now not necessarily in terms of total time but in terms of the amount of queries it's 50 less next we get inside of that sync translations method and see what is happening here two for each methods and this is exactly what I was referring to instead of n plus one query is just for each launching too many queries each time so we're getting the translations of the local meaning individual files so if I open the Lang folder these are files these are translations and then each file has its own translations and then we're going through all the translations getting key and value of every line of every translated phrase in that file and then we sync phrases what's inside of that sync phrases we're syncing key and value from translation to the Locale within the file and then for each of that sink see the language is queried again and why is it done here just to check if the language exists so if language doesn't exist we throw an error but we should do that earlier in the loop not for each of the phrase so let's make that higher so Locale is here for us and we can check that even before that for each happens so check for the language otherwise if it exists move on but that language variable is also used a bit later so we have language ID here and we can pass the language as an object from that higher check we need the language ID and language Locale so instead of that let's do language and then this will be language ID and this will be language code in fact it's pretty confusing to read all those terms they are really similar so Source translation language and Locale so I'll probably refactor that code even more for clarity of the variables and items but for now let's stick to that and we pass instead of Locale we pass language object here let's try to relaunch that I will just double check if we haven't forgotten anything I don't think so let's try to relaunch the command clear the terminal import fresh will it work fingers crossed it does work and the amount of queries is 651 so that's better next in sync phrases we're syncing one phrase but again we're querying not only the language which we've refactored already but also translation and translation file although it's not needed to be checked every time we should carry the translation file already as a parameter to that function the function of sync phrases should only take care of the phrases so this one is legit because it's working with the phrases update or create but those two things should be higher so we need to pass the translation and translation files as parameters to this one let's do exactly that let's do translation file cut and then we need to paste it here so we have file here as a parameter so we need to create for each file we need to pass translation and language already is here so we don't need to past language here at all to sing phrases anymore but now there are a few more changes with parameters and by the way this is me pavilas after a few days this is one of those videos that were shot in a few days time in separate pieces so even the jumper has changed as you can see so that's the next day powerful is continuing and now I have mismatching parameters here so we don't need the language here but what we do need actually inside of that function we use translation file so we need translation and translation file here those both as parameters in here so let's do exactly that translation and translation file and here translation and translation file like this and now I think we are ready to relaunch that and see how many queries we have we will launch the command yes no errors and we have two eight two queries one of those tests were done the previous day of shooting this video and the same done here for some reason it's a bit slower maybe some caching is cleared but anyway two eight two queries from the original one thousand and five hundred that's already a huge difference but we're not done yet so the main thing the main those hundreds of queries are happening here update or create for the phrases for one translation so it happens with 127 phrases for each of those it's checking if it already exists and then updates or creates meaning two queries per phrase and it seems at first glance that there's not much to optimize here that's how update or create Works in some cases you can change update or create if we look at the official variable documentation with absurd which would launch one query to the database but for that the requirement is that the key values would be primary or unique index in our case that's not the case because we have more complex situations so we cannot use absurd so how can we improve that last part and avoid those 200 queries and update only those that need actually updating let's get back a bit up and we have for each of the translations We sync phrases but what we're actually doing is sync phrase one phrase of key and value so what I think we should be doing is getting all the phrases here once in the database for that language then compare them in the collections and update or create only those that are changed and now actually I've noticed a bug that I introduced unintentionally probably didn't break anything in our case but it would break in case of more languages I override the variable of translation although it's the same name as the parameter to sing translations so we need to rename that for example to source so the main language and we pass the source here and translation would be the actual translation of the language it's good that I've noticed now look what I came up with to optimize that this was a paste of big code and I will explain it just a bit I just went offline for like half an hour to try to experiment and this is what I came up with so instead of for each of translations and launching that sync phrases I do that in line and seems like more code but it's actually less queries to the database so we get the phrases of that translation from the database once and then we get the new values I've copied those from the sync phrases here so these are new values and these are kind of the key to compare by so these are new values we search for those new values in the collection not in the database anymore and if we do find that phrase first if we don't find that phrase then we create those values create that phrase with those keys plus new values this is a combined array otherwise if at least one value is changed again we're comparing with The Collection not with the database it's all in memory it's faster then we update that phrase so this is again longer code maybe a bit harder to read and maybe it could be optimized a bit for readability but my main message here in this video is you would understand the idea of database optimization we're comparing not with the database every time but with collection in our memory which is this one and now the result of all of that if we relaunch that command remember we're not launching sync phrases at all so this function now becomes irrelevant we relaunch that with fresh yes it is successful and we have only 162 queries instead of what was 280 last time so again almost 50 percent of queries off just because we're not comparing each phrase to the database and those 100 queries actually what are real 127 queries for creating of each translation so we don't really optimize that because it's eloquent create but if I launch that without fresh for the update like this updating the phrases before our optimization it was launching hundreds of queries just to compare now if we launch import we have only 26 queries so our final optimization result in terms of number of queries from the very beginning 1500 queries if we launch fresh import it will be 162 and 0.3 of a second instead of second and more and if it's just update of existing translations it would be even faster so yeah that was my demo optimization again sorry for a bit unstructured approach because I was doing that almost in live mode and also with live happening I was running from computer to computer and kind of losing my train of thought in this video but I hope you got the main idea of steps to optimize and that's actually how it's happening in real life you try you experiment you fail you refactor you test and then you come up with solution if you want more structured way of talking about eloquent optimization I have a separate course better eloquent performance one hour long where I discussed n plus one query problems and other practical examples in shorter videos the link will be in the description below that's it for this time and see you guys in other videos

Channel: Laravel Daily

Views: 19,907

Keywords:

Id: csWx7RcNh5U

Length: 14min 49sec (889 seconds)

Published: Mon Jan 16 2023