4 ms·

Pretty easy to see how, just look at the Python equivalent in Django. You're so far abstracted away from what is actually going on that it's no wonder no one un

Pretty easy to see how, just look at the Python equivalent in Django. You're so far abstracted away from what is actually going on that it's no wonder no one understands it. There is a lot of gotchas, that most probably never investigate, particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs(they aren't).

nsomaru 7y ago
There’s tools to debug the lowest hanging fruit. You can go a long way with an ORM.
andybak 7y ago
> particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs (they aren't). No need to check the SQL logs directly. First thing I learned about optimising Django was to check django-debug-toolbar to see how many queries were being generated per page. This is fairly common knowledge. However. I don't often bother because SQL calls aren't the most common bottleneck. It's nearly always a better use of my time to look at page weight or javascript blockage.
- bshipp 7y ago
  django-debug-toolbar is excellent, and I use it on my django sites as well. In addition, the documentation does a pretty good job of highlighting some of the common gotchas. Unlike your environment, my page/js weight is very low but I'm querying against a few hundred million records joined across many tables. Even using materialized views to eliminate the impact of joins in postgres, it's required a fairly delicate touch to make the delay for page loads tolerable. In that respect I would likely redo the project in flask and sqlalchemy, if only because then I wouldn't have to remember the syntax nuances of two separate ORMs. They're similar, but not identical, and it's infuriating at times. Plus I'm very comfortable dipping down into raw SQL in sqlalchemy, and it hasn't been as intuitive for me with Django.
- overcast 7y ago
  When you have millions or billions of rows, SQL calls absolutely become a huge bottleneck. I deal with this all the time from shortsighted developers at the office in other ORM environments. Most recent being a SELECT...NOT IN('a','b','c'), causing an INDEX SCAN on 200 million rows, and then complaining it takes 6 minutes to run. Look at Django's select_related. It's one of those if you don't understand what's happening under the hood, then you're probably querying way more than you should be.
  - andybak 7y ago
    > When you have millions or billions of rows, SQL calls absolutely become a huge bottleneck. Not necessarily. It's the specifics of the queries not the number of rows. Which is why you measure before you optimize.
  - bshipp 7y ago
    select_related caught me when I first started using Django. It's a sneaky one because the queries worked fine when I first wrote the program. Then I started populating the database and, over the course of months, the queries got slower and slower and slower. Eventually I was forced to pop open the hood and horrified to find this spaghetti bowl of nested, duplicate queries that took a fair bit of work to simplify and optimize. I was not so lucky as to have a DBA I could dump my problems on and was forced to learn that lesson the hard way.
    - overcast 7y ago
      PRECISELY my point. Everything is easy to setup, and works amazing, when nothing is actually in the database.
    - aldoushuxley001 7y ago
      What was it about select_related that slowed your queries? What'd you do to fix them? Did you have to abandon select_related, or just tweak its parameters? I'm just building out a Django app now and using select_related, or rather prefetch_related, for retrieving tags (m2m relationship). Seems to work well so far, but I'm I'm sure I'll run into a similar thing of having to optimize all these queries soon.

nsomaru 7y ago

There’s tools to debug the lowest hanging fruit. You can go a long way with an ORM.

andybak 7y ago

> particularly multiple calls to the database for very simple join operations that aren't made apparent in the ORM unless you're watching SQL logs (they aren't). No need to check the SQL logs directly. First thing I learned about optimising Django was to check django-debug-toolbar to see how many queries were being generated per page. This is fairly common knowledge. However. I don't often bother because SQL calls aren't the most common bottleneck. It's nearly always a better use of my time to look at page weight or javascript blockage.

bshipp 7y ago
django-debug-toolbar is excellent, and I use it on my django sites as well. In addition, the documentation does a pretty good job of highlighting some of the common gotchas. Unlike your environment, my page/js weight is very low but I'm querying against a few hundred million records joined across many tables. Even using materialized views to eliminate the impact of joins in postgres, it's required a fairly delicate touch to make the delay for page loads tolerable. In that respect I would likely redo the project in flask and sqlalchemy, if only because then I wouldn't have to remember the syntax nuances of two separate ORMs. They're similar, but not identical, and it's infuriating at times. Plus I'm very comfortable dipping down into raw SQL in sqlalchemy, and it hasn't been as intuitive for me with Django.
overcast 7y ago
When you have millions or billions of rows, SQL calls absolutely become a huge bottleneck. I deal with this all the time from shortsighted developers at the office in other ORM environments. Most recent being a SELECT...NOT IN('a','b','c'), causing an INDEX SCAN on 200 million rows, and then complaining it takes 6 minutes to run. Look at Django's select_related. It's one of those if you don't understand what's happening under the hood, then you're probably querying way more than you should be.
- andybak 7y ago
  > When you have millions or billions of rows, SQL calls absolutely become a huge bottleneck. Not necessarily. It's the specifics of the queries not the number of rows. Which is why you measure before you optimize.
- bshipp 7y ago
  select_related caught me when I first started using Django. It's a sneaky one because the queries worked fine when I first wrote the program. Then I started populating the database and, over the course of months, the queries got slower and slower and slower. Eventually I was forced to pop open the hood and horrified to find this spaghetti bowl of nested, duplicate queries that took a fair bit of work to simplify and optimize. I was not so lucky as to have a DBA I could dump my problems on and was forced to learn that lesson the hard way.
  - overcast 7y ago
    PRECISELY my point. Everything is easy to setup, and works amazing, when nothing is actually in the database.
  - aldoushuxley001 7y ago
    What was it about select_related that slowed your queries? What'd you do to fix them? Did you have to abandon select_related, or just tweak its parameters? I'm just building out a Django app now and using select_related, or rather prefetch_related, for retrieving tags (m2m relationship). Seems to work well so far, but I'm I'm sure I'll run into a similar thing of having to optimize all these queries soon.