WEBVTT NOTE Created by CaptionSync from Automatic Sync Technologies www.automaticsync.com 00:00:01.016 --> 00:00:05.656 align:middle We made a huge leap forward by telling Doctrine to make COUNT queries 00:00:05.656 --> 00:00:07.636 align:middle to count the comments for each BigFootSighting... 00:00:07.936 --> 00:00:12.316 align:middle instead of querying for all the comments just to count them. 00:00:12.716 --> 00:00:14.286 align:middle That's a big win. 00:00:14.816 --> 00:00:15.976 align:middle Could we go further... 00:00:16.036 --> 00:00:20.536 align:middle and make a smarter query that can grab all this data at once? 00:00:21.106 --> 00:00:24.686 align:middle That is the classic solution to the N+1 problem: 00:00:25.376 --> 00:00:28.936 align:middle need the data for some Bigfoot sightings and their comments? 00:00:29.496 --> 00:00:33.096 align:middle Add a JOIN and get all the data at once! 00:00:33.756 --> 00:00:35.056 align:middle Let's give that a try! 00:00:36.146 --> 00:00:37.756 align:middle The controller for this page lives 00:00:37.756 --> 00:00:42.246 align:middle at src/Controller/MainController.php - it's the homepage() method. 00:00:43.496 --> 00:00:45.986 align:middle To help make the query, this uses a function 00:00:45.986 --> 00:00:53.326 align:middle in src/Repository/BigfootSightingRepository.php - this findLatestQueryBuilder(). 00:00:54.276 --> 00:00:55.336 align:middle This method ... 00:00:55.336 --> 00:00:56.616 align:middle if you did some digging ... 00:00:56.916 --> 00:00:59.816 align:middle creates the query that returns these results. 00:01:00.176 --> 00:01:05.736 align:middle And... it's fairly simple: it grabs all the records from the big_foot_sighting table, 00:01:06.036 --> 00:01:10.826 align:middle orders them by createdAt and sets a max result - a LIMIT. 00:01:11.446 --> 00:01:17.636 align:middle To also get the comment data, add leftJoin() on big_foot_sighting.comments 00:01:17.856 --> 00:01:20.866 align:middle and alias that joined table as comments. 00:01:21.956 --> 00:01:29.966 align:middle Then use addSelect('comments') to not only join, but also select all the fields from comment. 00:01:30.676 --> 00:01:32.246 align:middle Let's... see what happens! 00:01:32.776 --> 00:01:43.226 align:middle To be safe, clear the cache: And warm it up: Now, move over, refresh and profile! 00:01:46.986 --> 00:01:59.846 align:middle I'll call this one: [Recording] Homepage with join: https://bit.ly/sf-bf-join. 00:01:59.846 --> 00:02:00.216 align:middle Go check it out! 00:02:00.216 --> 00:02:00.726 align:middle Woh! This... 00:02:00.726 --> 00:02:01.026 align:middle looks weird... 00:02:01.256 --> 00:02:02.396 align:middle it looks worse! 00:02:03.016 --> 00:02:07.116 align:middle Let's do a compare from the EXTRA_LAZY profile to the new one: 00:02:10.616 --> 00:02:12.546 align:middle https://bit.ly/sf-bf-join-compare. 00:02:12.546 --> 00:02:16.886 align:middle Wow... this is much, much worse: CPU is way up, I/O... 00:02:16.966 --> 00:02:23.636 align:middle it's up in every category, especially network: the amount of data that went over the network. 00:02:24.166 --> 00:02:26.876 align:middle We did make less queries - victory! 00:02:27.316 --> 00:02:29.756 align:middle - but they took 8 milliseconds longer. 00:02:30.276 --> 00:02:33.296 align:middle We're now returning way more data than before. 00:02:33.846 --> 00:02:35.956 align:middle So this was a bad change. 00:02:36.276 --> 00:02:39.786 align:middle It seems obvious now - but in a different situation 00:02:39.866 --> 00:02:42.866 align:middle where you might be doing different things with the data, 00:02:43.276 --> 00:02:46.256 align:middle this same solution could have been the right one! 00:02:46.716 --> 00:02:50.126 align:middle Let's remove the join and rely on the EXTRA_LAZY solution. 00:02:50.846 --> 00:02:55.886 align:middle Yes, this will mean that we will once again have 27 queries. 00:02:56.286 --> 00:02:59.606 align:middle If you don't like that, there is another solution: 00:03:00.366 --> 00:03:07.196 align:middle you could make the JOIN query smarter - it would look like this: The key is that instead 00:03:07.196 --> 00:03:09.516 align:middle of selecting all the comment data... 00:03:09.656 --> 00:03:10.966 align:middle which we don't need... 00:03:11.276 --> 00:03:13.606 align:middle this selects only the count. 00:03:13.846 --> 00:03:17.866 align:middle It gets the exact data we need, in one query. 00:03:17.866 --> 00:03:22.206 align:middle From a performance standpoint, it's probably the perfect solution. 00:03:22.576 --> 00:03:26.366 align:middle But... it has a downside: complexity. 00:03:27.106 --> 00:03:34.636 align:middle Instead of returning an array of BigFootSighting objects, this will return an array of... 00:03:34.636 --> 00:03:39.236 align:middle arrays... where each has a 0 key that is the BigFootSighting object 00:03:39.516 --> 00:03:41.986 align:middle and a 1 key with the count. 00:03:42.756 --> 00:03:43.356 align:middle It's just... 00:03:43.626 --> 00:03:45.346 align:middle a bit weird to deal with. 00:03:45.446 --> 00:03:51.306 align:middle For example, the template would need to be updated to take this into account: And... 00:03:51.396 --> 00:03:54.516 align:middle because of the pagination that this app is using... 00:03:54.806 --> 00:03:58.086 align:middle the new query would actually produce an error. 00:03:58.446 --> 00:04:00.296 align:middle So let's keep things how they are now. 00:04:00.886 --> 00:04:04.516 align:middle If the extra queries ever become a real problem on production, 00:04:04.616 --> 00:04:07.806 align:middle then we can think about spending time improving this. 00:04:08.416 --> 00:04:11.716 align:middle Sometimes profiling is about knowing what not to fix... 00:04:11.866 --> 00:04:14.066 align:middle because it may not be worth it. 00:04:15.016 --> 00:04:20.856 align:middle Next, if you were surprised that we didn't see any evidence of the network request 00:04:20.856 --> 00:04:25.876 align:middle that the homepage is making to render the SymfonyCasts repository info from GitHub, 00:04:26.196 --> 00:04:29.726 align:middle that's because the homepage is more complex than it might seem. 00:04:30.346 --> 00:04:36.256 align:middle Let's use a cool "Profile all" feature to see all requests that the homepage makes.