Jonathan Reinink - Eloquent Performance Patterns

I would say that I'm not gonna go fast but I am because as you know these are shorter talks today so I'm gonna move right along but I'll make all the code available for this talk available on github for you to view afterwards so I'm gonna talk to you to you all about eloquent performance patterns which is just basically database performance patterns in laravel so anytime you're building a modern web application performance is obviously a really really important piece of the project right you need your web app or your website to be fast otherwise customers are gonna get frustrated or you're gonna have to spend more money than you want on hosting so anytime you're building any sort of app this is the performance has to be sort of top of mind right from the get-go and one particular area that I've seen people make mistakes over the years is in how they use their database or I should say how they don't use their database I see a lot of people not intentionally but sort of accidentally doing too much work in PHP in laravel even just the the collection object in laravel you know we all use the collections objects it's it's it's a wonderful wonderful tool where you can iterate over a collection of objects and do filters and maps and all different things like that and we've gotten good at doing that but I worry sometimes that that's come of the cost that we are doing more of this work in PHP and maybe not as much of this work in the database layer and the database is actually really good at this stuff and that can come at some pretty serious performance penalties if you're not careful so I wanted to go through a bunch of the examples today or a few examples of kind of how this can happen but instead of going through kind of with a bit of theory I thought I would just hack on this little demo app that I created so it's called Engler's online so this is a so I've actually I just flew in from the cottage this morning this is the week my family is always at the cottage which is this is also my first layer con ever so it's yeah finally so I flew in this morning and I'm flying out tonight which kind of kind of seemed like a good idea three months ago but yeah but anyway so fishing my mind because I've been out fishing and I thought you know for this demo app I'll create this a little app called anglers online which is just a pretend social media platform for sports fishermen so and what we'll do is we're gonna basically just work through and add a few different requirements basically pretend like we're building this app and and we have to kind of add some new features and then we'll use kind of that as sort of some steps to illustrate some problems that you might run into from a performance perspective and got how I would tackle that and we'll we'll start simpler at first and then we'll get into some more complicated ones all right so basically before I jump into the code I'm just going to show you real quick so this is all the pages right now we have a name the club so every single fisherman in this social media website belongs to a fishing club we're currently logged in as Lloyd Montgomery and he belongs to the northern pikes fishing club and I'll just show you the database here as well and hopefully I'll try to zoom in on a bunch of this stuff as I go here so we can all see it think I can zoom in yeah all right so the I'm not sure I can on the left hand side here though so it'll just have to trust me when I read out these table names so the users table is a pretty standard layer ball default Migration users table the only addition is the club ID which is a foreign key to the clubs table I've seeded it with four four different clubs there's a buddy's table which is just a pivot table that connects two different users who are buddies so user ID and a buddy ID and then the more interesting table is a trips table so the become the main feature of this particular app is that fishermen can post when they go on fishing trips and they can say oh I went on this date and I went to this lake and you know who knows maybe they could share how many fish they caught or what buddies they went with or whatever but this table is interesting because it has 250,000 records in it so if a quarter of a million records it took my old 2015 laptop awhile to see that but I wanted a decent amount of data in here to sort of illustrate the performance problems that you can run into there's 250,000 because there's a thousand users 250 trips Soper fishermen they fish a lot apparently and and so that adds up to a quarter million okay let's dig in so I'm not gonna hand type at all because I'm really not that great at typing so it's gonna be a lot of copy and paste and then I kind of explained it as I go so the first requirement here is what we want to do is we want to update this application to only show fishermen who belong to the club that you're a part of or who are a buddy of yours so for instance here if I jump onto page eight in the pagination here I can see that Bob here is a buddy of mine and I go to page ten I can see that Zeb is a buddy so I proceeded six buddies for lloyd and he's obviously part of the northern pikes Club which has a bunch of users as well so we want to somehow reduce this set from 100 pages to only those users that they're allowed to see so what you might be inclined to do here is to create a PHP artisan make you might be inclined to make a policy if it gets make policy user policy and then in there let me just pull open the user policy and then I'm gonna just take two seconds here make this all a little larger okay so now this is just a standard layer bell users policy kind of fall on the standard layer belt Convention and I created this view method so basically just a check if one user which would be our currently authenticated user Lloyd if you can see the other users so we have this policy now and now we want to actually implement the policy in our controller so just so you know the controller in this particular demo app is just the web PHP route file but let's go in here and say okay we want to drop that in but in order to run this policy we can't run that in the database right and the reason why we can't run that in the database is because the policy exists in PHP right we just made it so we have to instead get all the users and then filter through them in the collection and return only the ones that the user can see so if we rerun this here it'll work it works perfectly we got the users from the nor Pike's Club and we can see the buddies there and we got 26 pages here but what we've done here is we've introduced some performance issues so let's take a look at a few different metrics I have the laravel debug bar install which is really useful for finding these issues so one thing here we can see is we were at five queries five database queries that's fine nothing really to be concerned about there the main query here the users query took 19 milliseconds but previously and I probably should have mentioned this we are at about 4 megabytes of memory and now we've jumped up over 7 megabytes of memory previously we're running about 40 to 50 milliseconds and now we're over 300 milliseconds and maybe most interesting at all here I've added a custom metric to the layer vault debug bar call hydrated models I find this a really helpful metric to look at when analyzed and performance issues because what can happen is you can run a database query that runs really fast like this query ran really fast 17 milliseconds whatever it was but what you don't realize is behind the scenes all that data is now being downloaded from your database whatever your database is and it's being loaded into memory and PHP and not only is it being loaded into memory in PHP laravel now needs to go through every single one of those records and convert it into an eloquent model and if you're not careful and you have too many relationships all kind of like coming together you can end up generating a lot of these models and every one of those even though it takes a fraction of a second it adds up so in this case we're now hydrating over a thousand models which is way too much so this is a pretty simple example there's a relatively easy solution to this problem is to put this to move this work to the database layer so the way I'll often do this is I'll create a scope for this which a scope is just a way of encapsulating some query builder logic essentially and put it into a model so I'll create a visible to scope so I'll go over to my users model here jump right to the bottom and I'll paste this in and and let's look at this scope here for a second it's very similar to our user policy so our user policy said check the clubs if the clubs are the same then they're allowed to see each other or if they're buddies then they're allowed to see each other right so we basically need to reproduce this stuff that we're doing in PHP in that in that scope so we look here we can say yeah all right so if the current user is in the same club or that user is one of the youth the current users buddies so you can see here that I'll create this visible to scope where you can pass through any user so we'll be using it with the currently authenticated user but you could actually use a scope for anything any other user that you'd want to check this against alright so let's update our controller here jump in here and we'll get rid of this stuff here we'll just say ok now we want to load all the users order them by name only the ones that are visible to our currently our current user and if we reload that now it loads really really quickly we have six database queries with our main database query running just over to two milliseconds so you can see we're selecting start from users which and this is just pretty standard eloquent stuff where club equals three or the ID equals one of the Buddy IDs you can see over here we're running in around 50 milliseconds 40 to 50 milliseconds our memory uses is around 4 megabytes and we're only hydrating 19 models so that's great so that's that's the solution to that particular problem next problem very similar one but a little bit of a different one what if we wanted to sort the write all these rows so right now we're seeing the they're just ordered by name right but what if we wanted to see the buddy's first so like what if we wanted Bob and and and ZEB here and the other four buddies all listed first how would we do that in the database and this is gonna be pretty tricky because that you remember like we had that pivot table right the pivot table has this information in it so there's not really like an obvious way to sort this in the database right off the bat so I've actually created a couple query builder helpers over the years one of them is called an order by sub which is order by sub query so I'm just going to copy and paste this in to our user model and I'll explain it briefly so order by sub so I have a package that has this available in it and the source codes also right in the app service provider in here like order by sub is literally five lines of code or so that's it and the other one we're gonna be looking at is ad sub select in a moment so I'm not gonna go into the details of how these particular in particular work me and Taylor have already been talking about merging these into Larry Belk or at some point so they're just readily available but for now so just know that that's where that function comes from so we're gonna order by sub and all we're gonna do is say select raw true so we're just all we care about is it's almost like a where exists query so we just want to select raw true from buddy's where the buddy ID matches and where the user ID matches the current user right and we're going to limit this to one although there would only be one record because we only sub query you can only return one record back and I'll show you the sub query that it generates in a second and this works for ordering because true is sorted first so if it's not true it's gonna be null and true we'll sort ascending first before in null okay let's update the update our controller here for a second as well and we need to make sure we run that here before the order by name because we want to order by buddy's first right so that worked super quick once again it literally had no impact on the performance of the page the memory usage is still low page is still loading around 50 milliseconds still loading just 21 models here and the actual query here is running around 4 milliseconds so let's take a quick look at this in postal code let's actually just run this query here to see I'm not sure this is actually gonna help here because you may not be able to see it not much better but what we're doing here is if you look carefully here this is what's being generated eloquent is saying order by and then this is the actual sub query that's being run so that sub query runs it goes off it figures something out in this case it's just true or null and then it just returns that value and then it's ascends sorts ascended from there so that's what a sub query looks like and I'll give you a better example in a moment as well how about exactly works ok so that's where we're clipping right along here so that's good so requirement number 3 so now what we want to do and actually I'll just copy this for a sec we want to go over to our template and now what we want to do is we actually want to add the last trip date to this page kay we want to add the last trip so remember that I said the trips table has like a quarter of a million records in it right so we need to somehow for each one of the ten users on this particular page we want to find the last trip that they went on and just show the date so let's just start with kind of like the naive way that you might go about this so you might say all right you might go in here and do something like this for this user so this is gonna be this column right here for this user give me their trips so standard laravel relationship right so the user model has a trips relationship right there and sort that in eloquent or sorry not in eloquent we're gonna sort this because this is gonna return a collection right so we're back in the collection land here we're gonna sort that by the date that they went we're gonna grab the first one and then we're going to just use this handy Carbon differ humans helper and just reload this and sure enough we get the last date that they went so that's good right away here though we see that we have an M plus one problem but that's that's something that's easy to fix because we know that we didn't eat your load trips so we're just gonna go to our controller here and eat your load trips reload that again and now we're back down to seven database queries that's good but if you look here this is not actually great because what's actually happening now is we're loading every single trip for every single one of the ten users on this page which is amounting to a lot more memory and a lot more eloquent models being hydrated you can see that we're at over two and a half thousand models being hydrated here just to get this silly last trip value and the page is now loading it like 300 milliseconds and if we were to go in here and say like maybe we're not showing ten per page we're so not showing 100 per page if we reload this again now it's like really crawling because we're now just like that we're not and this is how easy this can happen we're now up over 225 thousand eloquent models being hydrated just to display this page just because we're just working with kind of standard eloquent relationships and not really thinking about what's happening behind the scenes so one way you might think to solve this I'll just drop this back I'm gonna also change this get rid of this eager load here one way you might think to do this it's like okay well I can't load all those records because that's obviously a major performance issue what I'm gonna do is I'm just going to run in basically forced N+ 1 query so as you may know with eloquent if you don't call this if you just call this as a function a relationship as a as a function you get an actual instance of the relationship object back which is and it's basically the query builder which you can then run a query against so we're gonna say user trips except this time we're gonna say get me the latest one and this is running in the database now but only get me the first one and then we're gonna display it so let's reload that and that works and it actually runs way faster you can see our pages now load in around 60 60 milliseconds so not terrible and the models are low so that's good and the memory is low but we've introduced an NM plus one problem right because because we've done it this way we have no choice but to run that over and over and over every single time so this is kind of an interesting point though because I think sometimes we always assume that n plus 1 issues are the worst but they're actually not always the worst they're ass there are some situations that just run in a few extra queries makes a lot more sense than trying to keep the queries to an absolute minimum and then do that work in PHP just because you you know we just saw here we the performance issues show themselves a lot like we really ran into performance issues more by doing that work in PHP than we did by literally just running sixteen database queries on the page but there's actually a better way to do this and that's again with sub queries I'm a big big fan of sub queries and I think they're kind of this little like underused feature of eloquent that can really become super useful for situations like this where you need some piece of extra relational data and there's not an easy way to get at it kind of just using like standard laravel relationships and I'm gonna do this by creating another scope and I'm gonna put the scope on the user model so I'll just paste it in and we'll talk about it so this scope is called with last trip date and I'm using this other macro that I've created called add sub select so the other sub the other one I said order by sub just means that whatever it's just gonna order by whatever result comes back from that sub query this is saying like I know we actually want to select a value whatever value comes back from this query and then alias it to the last trip at column and then all we're doing here is we're saying give me the went at date from the trips column for this user ordered by the date that they went because we want the latest one and then limit it to one okay so let's just update our controller here I'll put this down here reload this and oh and we're still at sixteen database queries because I need to do a couple more things here I need to update the template so I'm gonna update the template and I'm also gonna update I'm gonna add a cast because I want it so by default in my trip column here I have this went at date time so that's how I was able to use that or that Carbon helper that the differ humans or whatever it's cold so I'm gonna need to add that for this custom sub query column that we're creating so I'm just gonna go to the user model jump to the top here and I'm gonna paste that in here and then I'm gonna update the view as well which is right here okay and then let's look at what we're doing here we're just saying user last trip at so this is what's interesting like by using sub queries eloquent actually has no idea that it's a that this column is coming back from a sub query it just thinks it's another column on that table so we can add casts for it we can do mutaters we can do whatever we want with that data just like it were a normal column on that table and then we called it for humans on it because I made it a carbon instance so now we reload this page we're back in business and things are all like super good on all fronts we have only six database queries we're running around 40 to 50 milliseconds here and there's only 21 models being hydrated so let's look at this query that's being generated for two seconds and the copy and paste it's starting to get a little bit longer here but like a note here it's still only taking eight milliseconds to run this even on my own old laptop here so what's happening here is it's running basically it's getting the users right and it's getting all the user columns barea the very end here which you can probably hardly read unless you're in the front row here but this last column over here is the last trip at so that's where this sub-queries so we're saying when we run this query up here we're saying give me a users thought star so give me all the users data and then select went at from trips for that user order by went at descending limit one and then alias that column two last trip at so that's like this is pretty straightforward SQL stuff but maybe not something that you've seen as much in eloquent now eloquent uses sub queries sometimes but a lot of that's kind of it's abstracted away but to write your own sub query like this isn't really possible without this macro and it's it's super super powerful so let's say though what if we wanted to add a bit more of information about the last trip it's like okay that's great we we know that Bob went five days ago but I like what lake did he go to I'd love to see where he went well one simple way to do this would just be like okay let's go off and add another scope and this time for that extra called now paste this in here and this scope is literally identical to the previous cope and always except instead of returning the date we're returning the lake and so if we update our we have to then call this scope right because scopes need to be you gotta tell it when to run so I'm just gonna say with last trip date and with track list so BA with last trip lake and and then we'll update our template okay so I'm just gonna remove this whole column here and we'll just replace it with this one so now what we're doing is saying Kate display the last trip at and then also the lake here reload this page and there we go we have all the lakes being loaded now as well so again we're running one database query to get all this information we're just using a sub query to get this extra information about this particular trip and it's still running at like this query is running in under 10 milliseconds and the page is still loading superfast we have no memory issues or anything like that so you might be thinking at this point it's like okay well what now if I wanted to make this like what if I wanted to make this like a link where I could go to that actual trip page and maybe view some more information about maybe the fish that they caught that day or what buddies he went with or whatever how would we do that do we need to now add another column just to get that information do we have to go here and duplicate this scope again and and add it for just the ID for example so that we can generate a URL and you can do that but this is not typically how I did I'm kind of building up to help you understand that how this works but what what I would actually do in this situation is though I would create a dynamic relationship so let me just walk you through this first pretend just pretend for an instance here that we actually have on the users table users dot last underscore trip underscore ID as an actual foreign key so that would be how you would make this relationship it belongs to where the last trip is last underscore trip underscore ID that's how you would make that as just a normal layer of our relationship but that obviously doesn't exist on the user's table so what we want to do is we actually want to use this standard layer of our relationship along with one of our sub queries to get that column dynamically so let me show you how you do this I'll just get rid of that one I'll say with last trip get rid of date here and I'll say select last trip ID and then select the ID instead of the date and then I do this little thing here where I just say with last trip so I don't have to I don't have to eager load I can just I don't have to call with manually it'll just be assumed when I'm using the scope that it's gonna either load the last trip because that's the only time you would use it all right so let's go back now and update our controller and let's get rid of these two calls and let's just say with last trip nothing else and then let's do a little more updating here we can go into our template then we can get rid of all this stuff and we'll just replace this whole column now with some code that looks a lot more just like a standard layer ball relationship so user last trip ID for the URL the date for the date and the lake right and then what we can also do is we can actually go back into our user model and we can go back up to the cast here and we can get rid of this this date time cast here because we're not using that anymore so we don't the last trip out it's now gone all right now if I haven't broken anything here this should work and sure enough it does so now you can see here that I have a link so you can you probably can't see it but it's showing in the bottom left there that's trip number 1555 and it still got the the date and it's got the lake but you'll notice that we went up one database query and that's because now our sub query is only getting the ID but it's eager loading all the trips as a subsequent query anytime you can avoid caching there that's a win and I think I think using sub queries like this and using these techniques gives you a whole like bunch of tools that allows you to avoid caching until it's absolutely needed and sometimes caching is as simple as this like to cache this particular value is not even that hard but you can run into situations it wouldn't in like real-time apps where caching can be a total nightmare so this is why this is gonna be such a helpful tool so yeah that's really all I had I hope that all makes sense I would recommend using the layer bail debug bar if you don't right now definitely use that to get a sense of the metrics of your app and how how well each one of these things are performing I always look at queries how much memory I'm using how long the page is taking the load and how many models I'm hydrating like those are really really important metrics to keep in mind and just be conscious every time you break out of the query builder and you start pulling pulling that data into a layer bail collection and you start working on it be aware of how much that could grow because you might be building some new app and you maybe even have a whole bunch of seed data maybe you have a thousand records of seed data which feels like a lot and your app feels superfast while you're building it and then you deploy that thing to production and then you start having real user data come on and maybe even at first it feels ok but then in a few months you go from thousands of records to 10,000 the records to hundreds of thousands of records and maybe millions of records and that's when these issues are really gonna start to show themselves because PHP is okay when you're dealing with even tens of thousands of Records but if you start getting into more than that you're really gonna start noticing some performance issues and it's not even because your database queries are running slow that's not even the issue it's just you're downloading you're just taking too much memory from your database and you're pulling all that stuff or too much data from the database and pulling that all in a memory and PHP okay so that's all I had the one thing I did want to share briefly if this was interesting to you at all I'm gonna do the classic announce a project of mine I am releasing a course all about this stuff called eloquent performance patterns it's of course I've wanted to do for quite some time which basically digs into all this database stuff a lot more deeply kind of in a screencast video course sort of format this is something that I hope to put out this year if all goes well this will certainly put the pressure on me to do that so if you are at all interested in learning more about database performance and some of the things I've just talked about be sure to check that out it's a training ta which is my last name slash eloquent course you can sign up there and I'll be sure to tweet it in a little bit walk in a little while as well so you can if you want to find me there it's just twitter.com slash Renick for me that's all I have thanks everyone really appreciate it [Applause]

Channel: StreamACon Streaming Conferences

Views: 21,847

Keywords: PHP, Laravel, Laracon, Eloquent, Performance

Id: IBUXXErAtuk

Length: 27min 48sec (1668 seconds)

Published: Mon Aug 19 2019

Reddit Comments

[u/MrMaverick82]

Boy, that was an interesting talk!

[u/Aurabolt]

This is exactly relevant to my issue I just commented here! https://www.reddit.com/r/laravel/comments/crmn78/z/ext5dxb

Thanks