A humble request to folks making benchmark or other graphs - please understand that thin coloured lines are not easy to visually parse .. even for folks like me who aren't totally colour blind but have partial red-green colour blindness. At least, the lines can be made thicker so it is easier to make out the colours. Even better, label the lines with an arrow and what they represent.
12 requests per second: A realistic look at Python web frameworks
31–40 of 239 posts
Re: 12 requests per second: A realistic look at Python web frameworks
#32Earlier quoted context omitted.
> (usually people writing dumb code without realizing it's expensive) Some years ago, one morning I gave a co-worker a recommendation on how to improve a loop that was unnecessarily hitting database through the Django ORM. He committed the fix that afternoon. Barely an hour later I accidentally reintroduced the exact same slowdown in the exact same loop when adding a different piece of data to it. Soooo yeah, ORMs ca…
Something about select_related? Please do share.
It wasn't anything that fancy, it's just the solution was something you usually try not to do so it just wasn't coming to mind for him. The data being looped over came from solr, and some of the fields were primary keys used in lookup tables in the database, for getting translated text. Instead of doing the lookup inline, load the entire table into a python dict before the loop (And like I said above, usually you don't just select out the entire contents of a table and handle it in the application, so I reflexively did the wrong thing as well, because of how easy it was to do with Django's ORM.
Re: 12 requests per second: A realistic look at Python web frameworks
#33Earlier quoted context omitted.
Something about select_related? Please do share.
My guess is accessing a related field within a loop causing a database request per iteration, e.g. ``` [book.author.name for book in Book.objects.all()] ```
That’d be my guess: prefetch_related is great but you need to guard it with something like an assertNumQueries test to avoid accidental regressions.
Re: 12 requests per second: A realistic look at Python web frameworks
#34Don't forget that you're paying a huge price using the sqlalchemy orm - https://docs.sqlalchemy.org/en/13/faq/performance.html If I know an endpoint is going to be hit hard, I forgo trying to use the ORM (except to maybe get the table name from the model obj so some soul can trace it's usage here in the future) and directly do an engine.execute( ). Makes a huge difference. Next optimization I do is create stored proc…
You can also use SQLAlchemy Core, which is an intermediate between the full-blown ORM and running actual strings of SQL. I've had a great experience with Core - I can easily have it output essentially the exact SQL I'd write by hand, but I get many benefits (like the ability to compose queries) that are nicer than dealing with raw SQL.
Definitely agree, I'm happy to write raw SQL, but SQLAlchemy Core is even better than that because of composability.
Re: 12 requests per second: A realistic look at Python web frameworks
#35My experience doing perf optimizations in real world systems with many many people writing code to the same app is a lot of inefficiencies happen due to over fetching data, inefficiencies caused by naively using the ORM without understanding the underlying cost of the query, and lack of actual profiling to find where the actual bottlenecks are (usually people writing dumb code without realizing it's expensive). Sure,…
I increasingly lean towards plain SQL over ORMs. It requires greater familiarity with SQL but I prefer that over greater familiarity with ORM-specific syntax that doesn’t translate across frameworks or languages. In addition, you can prototype new queries and profile existing queries in the database and copy-paste directly into your code.
I favor a hybrid approach: use the Django ORM to define models, do migrations, auto generate the admin, etc. but don’t be shy about using the extension points (extra, raw, cursors) to put in an optimized query for a hotspot. You can get pretty far using the ORM but it’s really valuable to be able to be comfortable dropping down for things like reports or bulk processing.
Re: 12 requests per second: A realistic look at Python web frameworks
#36Earlier quoted context omitted.
Something about select_related? Please do share.
My guess is accessing a related field within a loop causing a database request per iteration, e.g. ``` [book.author.name for book in Book.objects.all()] ```
Funnily enough, I recently optimized some code along these lines.
The way I sped it up was to call `.values()` on the query, which serializes the data into a dict and prevented me from accidentally making subsueqent calls.
PS: Indent by 4 spaces for code formatting.
Re: 12 requests per second: A realistic look at Python web frameworks
#37Earlier quoted context omitted.
My guess is accessing a related field within a loop causing a database request per iteration, e.g. ``` [book.author.name for book in Book.objects.all()] ```
Funnily enough, I recently optimized some code along these lines. The way I sped it up was to call `.values()` on the query, which serializes the data into a dict and prevented me from accidentally making subsueqent calls. PS: Indent by 4 spaces for code formatting.
s/ident/indent/
Re: 12 requests per second: A realistic look at Python web frameworks
#38Don't forget that you're paying a huge price using the sqlalchemy orm - https://docs.sqlalchemy.org/en/13/faq/performance.html If I know an endpoint is going to be hit hard, I forgo trying to use the ORM (except to maybe get the table name from the model obj so some soul can trace it's usage here in the future) and directly do an engine.execute( ). Makes a huge difference. Next optimization I do is create stored proc…
I'll happily forget that because it's such a small microscopic price that it's moot. You're way better off optimizing the actual query being made, which SQLAlchemy is great at because it doesn't hide the SQL from you. Don't use engine.execute(), use SQLAlchemy Core if your endpoint is getting hammered.
Re: 12 requests per second: A realistic look at Python web frameworks
#39My experience doing perf optimizations in real world systems with many many people writing code to the same app is a lot of inefficiencies happen due to over fetching data, inefficiencies caused by naively using the ORM without understanding the underlying cost of the query, and lack of actual profiling to find where the actual bottlenecks are (usually people writing dumb code without realizing it's expensive). Sure,…
There is lots of truth to this. Some ORMs like Django perform joins in very unsuspecting ways.
A simple example is, say, foreign keys. Trying to access the foreign key of an object by doing `book.user.id` does an additional query for the user table to get the ID. It's less known that the id is immediately available by just doing `book.user_id` instead.
I've spent time optimising things like text searches down from 2000+ queries to about ~4, and one of the more noticeable things to me isn't actually the number of joins, rather the SELECT's that take place. Many of these ORMs do a SELECT * unless you explicitly tell them to otherwise, and when dealing with large-ish datasets or on models that have large text fields this translates into significant time taken to serialise these attributes. So you can optimise the query and still have it take a long time until you realise that limiting the initial `SELECT` parameter is probably more efficient than limiting the number of joins.
Re: 12 requests per second: A realistic look at Python web frameworks
#40My experience doing perf optimizations in real world systems with many many people writing code to the same app is a lot of inefficiencies happen due to over fetching data, inefficiencies caused by naively using the ORM without understanding the underlying cost of the query, and lack of actual profiling to find where the actual bottlenecks are (usually people writing dumb code without realizing it's expensive). Sure,…
> (usually people writing dumb code without realizing it's expensive) Some years ago, one morning I gave a co-worker a recommendation on how to improve a loop that was unnecessarily hitting database through the Django ORM. He committed the fix that afternoon. Barely an hour later I accidentally reintroduced the exact same slowdown in the exact same loop when adding a different piece of data to it. Soooo yeah, ORMs ca…
That's a nice benefit of using async ORMs (not yet available in django), the db calls are explicit!