Optimize Laravel Queries & Reduce Memory Usage for Faster Performance | Laravel Query Optimization

hey everyone I have this code is actually showing some statistics to the admin user for this site currently this code is actually using 113 query over 37,000 model hydration to process this code it Tak 78 MB of space and almost 12 seconds now what I improved from this piece of code to achieve the same result everything by six queries 46 model hydration 2 megabyte and within one and a half second it's a huge Improvement right if you are interested to know more how do I write this bagy code to the performance code hey my name is to Islam previously I showed you how can I improve n plus1 issue today I will show you how to optimize the memory users and processing time with the different type of code let's jump into it I have this code actually it's a admin section where admin is able to view the statistics of a application that how many total messages has been sent messages to admin users how many messages sent considering as a potential user and how many messages are considering flat after that it's showing the conversation between every users for example these two users what is the conversation how many total messages has been exchanged between them and what was the last date for this conversation uh for sending message and what was the last message pretty interesting and if I go down here it's it's coming from 1,000 conversation and it's showing only 50 based on pagination look at that stats it's actually 113 over queries 37,000 over model hydration total 78 mgab memory uses for processing this query it's and total taking almost 12 seconds for this processing it is not really a performant code so let's see what we can improve from here if I go into the code editor let's open the route I can see that this is the method actually rendering our Pages here if we inspect the code look at here the messages is actually getting all the messages with some eager loading then grouping by the conversation and showing based on page nation and Counting how many messages sent to the admin user and then joining with DV with message with conversation pting uh putting some uh condition on it and then getting and count hm this is a memory issue so it's actually dumping everything on the memory first and then counting which might not necessary okay look at that potential user messages is actually joining these two tables and then putting some condition on it selecting something again memory issue flat messages joining these two tables it seems these two queries are actually same and here total messages is actually using another query putting everything on the memory and count so I can see these three actually I can combine into one query because it's all coming from the message except this one where it's actually joining with the conversation because it's putting a we condition on the conversation table so what I can do in the last episode I showed you how can you use case when put condition then right so let's use this one actually I created another route it's called refactor this dedicated method and it's actually not doing anything just rendering a blade file so let's open this one first it's actually loading a simple blade view with some HTML syntax no query no model 1 megab and 34 millisecond for processing cool first of all as I mentioned that we can combine these three queries together all of them are coming from the message first what I need to do I need to join with this two t uh join this two table for the flag messages right so let's join it first I join these two tables right then what I need to do let's come with this one first Total messages how can I do that I can use a select right so I will use DB I need to count how many messages is because there's no condition right so what I will do I just put ID on it and then say as total messages this is the first condition which means I'm good to go with this second one here I'm trying to count the flat messages and putting condition on message is fled right this is our key again I will say case when is flaged is actually one then consider is a true otherwise zero and I end my query with this condition I need to count how many record I get it from my database query so I just put it sum whatever I'm getting from here I just put S Flex messages we're good to go we covered this one potential messages I need to consider how many potential messages I need to put a condition is potential user on the conversation let's go there I do exact same I just use instead of is flat is potential now is potential is where is that on the message table I think it's on conversation table look at this is potential is on the conversation table I need to prepend is is potential basically on conversation table and then let's change it now we need to use the fres to run this query we're good to go so we need to pass this data to the view I need to go go to the refactor and use this variables so let's say now with any luck if everything goes smoothly we should get some good results look at that we are getting total flag message total potential messages and total messages now if we try to compare side by side whether we get the right information or not it's identical right so which means we are achieving these three things by one query look at that can query number is one one model and 2 megabyte and over one second let's check for the next stage what else we can do for admin users if I go into current implementation what is doing here for admin users messages is actually coming from the user joining with messages and conversation and then putting some sort of condition on conversation all right some condition select and Group by let's see what we can do here we might not able to do it here so I would love to create another uh query for this let's call it message to admin accounts okay what is actually doing is trying to get some combination from conversation and messages right so I would love to call it conversation model then what I'm actually uh expecting conversation should have a u relationship with message if I check in to the conversation model it has two relationship with the same table from the conversation user one user two right so which means I can utilize the wire has let's try to do it wire has user one and then I want to put a logic or condition on it right user secondly I will duplicate this exact same thing we are good to go I need to count how many total messages to admin now I need to print it out into the refractor blade if everything goes smoothly I should get me message two huh undef variable user one H I made some mistake let's see where did I do mistake ah it should be two that's my fault yeah we are getting some result let's double confirm that we are getting the same result yeah 756 756 perfect now we are good to go with stat statistics four statistics we are getting with two queries and it's around one seconds right now let's jump into this section how can we improve it's coming from here messages let's copy this one what exactly trying to achieve it's trying to get messages with this this this and grouping by H since it's a grouping byy I would do it's a little bit opposite way maybe conversations I'll put it conversation and I will try to get it from conversation model they are trying to use user one user two all right I will use user one and then and then page un it I would go step by step instead of dumping everything together right I just comment it out I I need this one for reference in future that now I need to pass this one to view in the vade I need to render it over if I refresh at least I should see some sort of record and definitely this is not what I'm expecting we need real data right if I look into their implementation what exactly they are doing all messages in the loop all right they need the conversation and then user one full name we are in the conversation so we can do just copy this one what we can do user one I just delete it because we are on conversation already okay let's try for the second one next we need to count how many messages for these two user H I think we might have a relationship between conversation and messages let's go to the conversation if I go there and there's no relationship so a conversation could have multiple messages right there good to go now I have a from conversation I can able to see all the messages right so what I can do I will prefer to put it with count now this count instead of counting here like this Bo just conversation messages I think it's called count right okay now I'm getting some counting with them perfect now what's next I need to show the last message date and what is the last message let's see how they implement this one H wow they write some query on the blade H I can think a little bit different way let's say I prefer to use subquery how can I do this subquery add select and then I need to count actually what is happening here for example I want to say the last message date now in the subquery I try to get the last message date for this part conversation how can I do this I need to go to the message model okay and select what I need to select actually I need to select a specific column that actually tell me the date of the last message it's called created at I need to confirm this conversation ID and the message conversation ID are actually same how can I do this I can do wire you can use wire column right column this wire column basically Compares with two tables column all right and finally with this condition I need to get the latest record latest and I would say take so by the way in the subquery you always need to take only one record if it is multiple record the subquery won't work all right now how can I actually able to access this one this is our access name exactly like the table column so if I go to the refractor and I want to show the message date if everything goes smoothly if I refresh I should see at is something hm um let's see ah okay I think it's a typo okay I should see something all right I can see the date now perfect with the six queries 46 model hydration and everything seems good now the one one problem I realize that this is a string we need to convert it to carvon in order to get the exact formatt date like this way there are two ways to achieve this one one we can actually convert it it can carbon by Carbon on the blade like this it's really not a good approach I really don't like it alternative approach on fly we can change this string to the date how can we do this if we go to the conversation table and if we apply the cast here it doesn't work because this column is not available in the database so how can we do this in the larel there's a option you can do it with cast now if everything goes smoothly I should able to format this one as a carbon for example I can easily do it copy it here let's try to refresh if everything goes smoothly I should get the exact same result formattable perfect now what else this is the last message right how can I do this I think this one extra column that's unnecessary for us we can apply the same subquery to get the last message let's try that's it now I can copy this one last message we are good to go with any luck if I refresh I should get the last message here perfect it's quite long if you want to trunet it you can use a larable helper method I think I want to put it l 50 I should get the exact same result perfect now one thing is missing that is pagination if I refresh 1,000 result 15 record and with the latest date whatever 50 record are showing here with all the pagination data now look at that if I compare side by side we are getting exact same results we are getting exact same amount of data with huge Improvement now if you want to improve more we can do it better using uh the indexing on the database site definitely we can do it by but to make this video short and simple I want to stop here if you like this video I request you to subscribe my channel and share this video to the social networking platform so that your friend and co-workers have a chance to look into it hopefully you enjoy this video see you bye

Channel: Laravel School

Views: 4,674

Keywords: laravelSubQuery, laravelMemoryOptimize, LaravelOptimization, QueryOptimization, LaravelPerformance, LaravelTutorial, WebDevelopment, PerformanceTuning, Laravel, PHP, OptimizationTips

Id: -W9Syinl__0

Length: 15min 16sec (916 seconds)

Published: Sun Oct 29 2023