Effective PyCharm Transcripts
Chapter: Performance and profiling
Lecture: Optimizing the database access code
0:00 We've optimized our computer analytics where to next?
0:03 Well let's look at this search.
0:04 It's Not very fast. It did take 1.4 seconds.
0:09 You know, I think the internet is just being slow right now.
0:12 Let's try to run it again and see if what the time is going to be
0:16 like. If we look into it over here,
0:19 you'll notice there's not a lot we can do we call get on requests and then
0:24 you know, that's that's it.
0:26 We wait let's just go see if there's any sort of difference over here now.
0:31 It's 448 milliseconds. That's a huge difference.
0:34 But what are we gonna do?
0:35 There's not a whole lot else we can do.
0:38 I'll go and keep this as our baseline now.
0:40 But we've got search not a lot we can do compute analytics which we've made about
0:45 10 times faster. And now we've got get records And over here and get records
0:49 We have create connection being called once that's 12% and there's run query which is
0:55 really running read row. So Send query might be the place to focus on but
1:00 notice 889888. So read row is actually really the challenge.
1:06 Let's go and focus on this section.
1:10 They're down here. Again, we're simulating.
1:12 We're not creating a real database with huge amounts of data that will make a slow
1:16 query. But imagine we're doing a query against a database here and then because it
1:21 has a lot of data, it's somewhat slow when we're working with databases and we
1:25 have a lot of data. The thing you can do more than almost any other
1:30 thing to make it faster is to add an index.
1:33 So let's imagine there was and this one we had no index and we decided,
1:38 you know what, we're going to go and look at the query performance stuff over
1:43 in the database and realize, oh,
1:44 there's no data, there's no index on this.
1:47 That's a problem adding an index when there's tons of data can easily make a query
1:52 1000 times faster. Let's just imagine this one makes it 100 times faster.
1:57 So put a couple zeros there again,
2:00 somewhat contrived. But this is real world type of stuff.
2:03 You see a database query that's slow and you go and you optimize it by optimize
2:09 it in the database first. The other thing that's often done in this case is
2:13 if you want to run a query we might use something like Redis or an in
2:17 memory cache where we cash the results.
2:19 So we don't even go to the database again.
2:21 But in my mind it's better to make the database faster if that's at all possible
2:26 because adding a caching layer, all of a sudden you end up with cache invalidation
2:31 and staleness and all kinds of challenges.
2:33 So start by making the database faster.
2:36 That's what we're simulating by adding index that's running again,
2:40 we did it. Oh look at that.
2:41 This thing is starting to the zip.
2:43 So we can't really speed up the queries or the web request.
2:46 I'll make a quick comment before we wrap up this video on what we could do
2:51 some of the time. But in this situation we can't make it any faster.
2:54 The database seemed faster. And we already saw that this is much faster.
2:57 Let's profile it and get a real answer up over the call,
3:01 graph those side by side. See how we're doing.
3:05 Our program is still red because well,
3:07 that's the slowest thing here. It's always going to be true.
3:10 But look at how much faster we got on our get records Now,
3:14 it's 20% of the time, whereas before it was 55% goes from 1.1 second down
3:22 to just 0.26 or 265 milliseconds.
3:27 That is a huge improvement. All right,
3:30 well, I feel like we've massively optimized this app.
3:35 We've gone what was the very original one?
3:37 It was something like six seconds.
3:39 We've gone from six seconds. Stand at 1.2 and we've done that.
3:42 Being guided by what we got in this picture right here.
3:47 Make a small change. Run it in the profiler.
3:50 See if it got better if it got worse.
3:51 Don't don't keep it roll that back.
3:53 Make another change. Figure out what's slow now in this new context.
3:57 Optimize that and keep going and keep going.
4:00 My final comment on this search here.
4:02 What we're doing is we're going over and passing a search text profile.
4:07 profiling over to talk python and asking for the answer and it's got to go through
4:11 the internet all the ping Times and what not to the east coast from the west
4:15 coast of the U. S.
4:16 That is at least 100 milliseconds.
4:18 Ping time plus the server as to respond.
4:20 And we have to process and print out the results.
4:23 Okay, So we can't really do anything about that.
4:26 I mean we could not print out the results but we kind of want to see
4:29 them. We could not do the query but we need the answer.
4:32 Right? So on one hand it feels like,
4:34 well we can't do a whole lot but we could say if we're gonna if this
4:38 was more interactive, if it was longer running,
4:41 we could actually go up here and say at Funk tools and add an
4:45 'lru_cache( )' to this as well.
4:47 At least that way, if we ever search for the same text twice it's going
4:51 to be twice as fast. The second time we ask for the search,
4:55 we're going to assume that the data set over there hasn't changed does sometimes,
5:00 but it's not that frequent when assuming hasn't changed for the lifetime of this app running
5:05 and we're just going to give back the same answer,
5:07 we can add this, it'll make zero difference in terms of performance because we're calling
5:10 it only once but if we were calling it multiple times with potentially the same input
5:14 we have a much better response there.
5:18 Alright, final time, let's go and run our app.
5:19 See how it works. Boom still works much,
5:22 much faster. Pretty awesome, right?