4 ms·

The biggest database performance problem that I have seen time and time again over the 20+ years I've been using sql databases now is caused by people not under

The biggest database performance problem that I have seen time and time again over the 20+ years I've been using sql databases now is caused by people not understanding that databases are relational (ie designed to perform efficiently over tables, not individual rows) and so running code that essentially causes hundreds/thousands of individual queries where they could just do one big query. Any time you are doing a select query in a loop you should sit down and take a long hard look at your code. Almost always Do big expensive slow query to get everything I need in one hit for each row of result do something with it is going to be much faster than for each thing in some collection in my program get the database row that corresponds to just that one thing do something with it Round trip io, query parse time, cache effects etc are just going to totally destroy the performance of the second one, and optimising the db or programming language perf is going to do almost nothing to improve things if you're doing it this way. Lots of ORM type abstraction layers make it hard sometimes to see that's what you're doing, so people blame db performance but they're actually doing an insanely inefficient algorithm. My biggest win ever from this was taking an 8 hour operation down to 15s just by moving some sql out of a loop as per the above.

derefr 4y ago
On the other hand, weird things can happen at scale inside an RDBMS. For example: window queries, where you're trying to extract `row_number() = 1` of each window-partition. (Or "DISTINCT ON" queries, which are basically syntax sugar for this.) These can be extremely slow, because in many RDBMSes† they generate a query plan that reads+materializes all the rows of each window-partition, in order to then just discard all-but-the-first row-tuple of each window-partition. (In other words, the filtering-out by rank doesn't get pushed back through the query plan to turn the whole-index scan into an index-prefix scan.) The (surprising) solution to this over-materialization, is to use a recursive CTE, using the previous query's matched key as the inductive case's filter condition, to cause index-prefix matching to happen "one query step at a time." Which, if you think about it, is just "causing hundreds/thousands of individual queries"... just with all of those queries happening within a single tx, statement, query plan, table lock, and (hopefully!) page cursor. † Some RDBMSes do know how to push this window-node attribute back into the fetch node; the feature is called "index skip scans" or "loose index scans." (Before anyone asks: Postgres isn't one of them... yet. https://wiki.postgresql.org/wiki/Loose_indexscan https://wiki.postgresql.org/wiki/Loose_indexscan)
- Widdershin 4y ago
  This is good to know about, used a similar window function in Postgres recently. Will double check the performance of that query.
rob74 4y ago
Sounds obvious... of course, sometimes ORM and other abstraction layers conspire to hide that you are doing queries in a loop.
- dopylitty 4y ago
  Years ago I was part of an effort to migrate a compute footprint from one data center to another that was across town. When the DB VMs were migrated suddenly we had complaints about certain ETL jobs going from minutes to days. I opened up a packet capture and took a look (thankfully at the time the queries weren’t encrypted). What I saw was that this big name ETL tool was loading data one row at a time using one query per row. This apparently was ok when the DB and app server were in the same building but adding a few additional milliseconds to each query totally screwed the jobs. Thankfully there was a parameter that could be set in the DB driver to fix the issue and we just had to convince the ETL tool vendor to set that.
boxed 4y ago
Django has pretty great features for handling exactly this. Unfortunately it doesn't by default warn you if you're missing this optimization. I've had to write that warning system myself into iommi, but with that on it's super nice.
- eru 4y ago
  Well, Django has an ORM that makes this kind of feature necessary in the first place? ORM's are trying to hide the relational nature of your db, don't they?
  - gwillz 4y ago
    Well no, it's in the name - Object Relational Mapping.
  - noirscape 4y ago
    Not really... At least, not a good ORM. The actual idea behind a model based ORM system is to basically solve two problems. The first is SQL is old and its methodology doesn't directly map onto modern programming paradigms. This one is just apparent by seeing what you get back from raw queries; tables. Want to do something with the table? Go and iterate over the rows individually. Want to get something from a foreign table? Go and do a JOIN before you even start iterating. Want to use binary json properly (the one SQL can actually index so it's not slow as molasses)? Enjoy a serialization syntax that makes you want to rip your eyes out once you get into the fine details. And that's without delving into how every "flavor" of SQL is subtly different with its own idiosyncrasies compared to everyone else. SQLite is not MySQL is not SQL server is not Postgres. Just about the only thing actually shared between them is the extremely basic CRUD tasks (and only if your definition doesn't include any complex WHERE operations). Anything to do with configuring the database is implementation specific in syntax. ORMs broadly solve this by letting you use OOP paradigms instead; you designate a model that inherits from some base class with fields that themselves are typed to something in the database (including foreign key fields, so the relational status of your database is preserved). After that you can use traditional OOP methods and queries to read and write to the database. Most ORMs will also offer you some form to speed up any query slowdown that might occur due to unnecessary joins by letting you express them beforehand in a syntax that actually makes programmatic sense instead of trying to awkwardly map things. The other thing ORMs solve is that by default you uh... don't check in your database scheme into a VCS. Some projects just do an SQL dump and expect you to import that. ORMs are basically a solution to that, in that they are a reference for how your database is expected to look like to be functional. The really fancy ORMs also typically introduce (or support) a migration system so you can also track and easily update your database when your models get changed (which is well, necessary due to the disconnect between the database and the application), but that's not everywhere (SQLAlchemy for example doesn't support it). To be clear, ActiveRecord is kinda garbage. It does have a migration system but rather than doing model agnostic queries it just references the original models, which leads to problems when setting up the database in development mode since you can't just run all migrations. Instead rake gives you the option to create a database dump of a working environment and have that be the new "canonical" version of the database. For that reason alone it's a mess. It's also incredibly slow, has (as the post mentions) lots of footguns that you need to be careful with and in some cases cannot actually be worked around easily. ActiveRecord is arguably what killed Ruby for most webapps and I can say that pretty confidently.
pjmlp 4y ago
On top of it, disregarding stored procedures and waste network traffic with data that is going to be thrown away.

derefr 4y ago

On the other hand, weird things can happen at scale inside an RDBMS. For example: window queries, where you're trying to extract `row_number() = 1` of each window-partition. (Or "DISTINCT ON" queries, which are basically syntax sugar for this.) These can be extremely slow, because in many RDBMSes† they generate a query plan that reads+materializes all the rows of each window-partition, in order to then just discard all-but-the-first row-tuple of each window-partition. (In other words, the filtering-out by rank doesn't get pushed back through the query plan to turn the whole-index scan into an index-prefix scan.) The (surprising) solution to this over-materialization, is to use a recursive CTE, using the previous query's matched key as the inductive case's filter condition, to cause index-prefix matching to happen "one query step at a time." Which, if you think about it, is just "causing hundreds/thousands of individual queries"... just with all of those queries happening within a single tx, statement, query plan, table lock, and (hopefully!) page cursor. † Some RDBMSes do know how to push this window-node attribute back into the fetch node; the feature is called "index skip scans" or "loose index scans." (Before anyone asks: Postgres isn't one of them... yet. https://wiki.postgresql.org/wiki/Loose_indexscan https://wiki.postgresql.org/wiki/Loose_indexscan)

Widdershin 4y ago
This is good to know about, used a similar window function in Postgres recently. Will double check the performance of that query.

rob74 4y ago

Sounds obvious... of course, sometimes ORM and other abstraction layers conspire to hide that you are doing queries in a loop.

dopylitty 4y ago
Years ago I was part of an effort to migrate a compute footprint from one data center to another that was across town. When the DB VMs were migrated suddenly we had complaints about certain ETL jobs going from minutes to days. I opened up a packet capture and took a look (thankfully at the time the queries weren’t encrypted). What I saw was that this big name ETL tool was loading data one row at a time using one query per row. This apparently was ok when the DB and app server were in the same building but adding a few additional milliseconds to each query totally screwed the jobs. Thankfully there was a parameter that could be set in the DB driver to fix the issue and we just had to convince the ETL tool vendor to set that.

boxed 4y ago

Django has pretty great features for handling exactly this. Unfortunately it doesn't by default warn you if you're missing this optimization. I've had to write that warning system myself into iommi, but with that on it's super nice.

eru 4y ago
Well, Django has an ORM that makes this kind of feature necessary in the first place? ORM's are trying to hide the relational nature of your db, don't they?
- gwillz 4y ago
  Well no, it's in the name - Object Relational Mapping.
- noirscape 4y ago
  Not really... At least, not a good ORM. The actual idea behind a model based ORM system is to basically solve two problems. The first is SQL is old and its methodology doesn't directly map onto modern programming paradigms. This one is just apparent by seeing what you get back from raw queries; tables. Want to do something with the table? Go and iterate over the rows individually. Want to get something from a foreign table? Go and do a JOIN before you even start iterating. Want to use binary json properly (the one SQL can actually index so it's not slow as molasses)? Enjoy a serialization syntax that makes you want to rip your eyes out once you get into the fine details. And that's without delving into how every "flavor" of SQL is subtly different with its own idiosyncrasies compared to everyone else. SQLite is not MySQL is not SQL server is not Postgres. Just about the only thing actually shared between them is the extremely basic CRUD tasks (and only if your definition doesn't include any complex WHERE operations). Anything to do with configuring the database is implementation specific in syntax. ORMs broadly solve this by letting you use OOP paradigms instead; you designate a model that inherits from some base class with fields that themselves are typed to something in the database (including foreign key fields, so the relational status of your database is preserved). After that you can use traditional OOP methods and queries to read and write to the database. Most ORMs will also offer you some form to speed up any query slowdown that might occur due to unnecessary joins by letting you express them beforehand in a syntax that actually makes programmatic sense instead of trying to awkwardly map things. The other thing ORMs solve is that by default you uh... don't check in your database scheme into a VCS. Some projects just do an SQL dump and expect you to import that. ORMs are basically a solution to that, in that they are a reference for how your database is expected to look like to be functional. The really fancy ORMs also typically introduce (or support) a migration system so you can also track and easily update your database when your models get changed (which is well, necessary due to the disconnect between the database and the application), but that's not everywhere (SQLAlchemy for example doesn't support it). To be clear, ActiveRecord is kinda garbage. It does have a migration system but rather than doing model agnostic queries it just references the original models, which leads to problems when setting up the database in development mode since you can't just run all migrations. Instead rake gives you the option to create a database dump of a working environment and have that be the new "canonical" version of the database. For that reason alone it's a mess. It's also incredibly slow, has (as the post mentions) lots of footguns that you need to be careful with and in some cases cannot actually be worked around easily. ActiveRecord is arguably what killed Ruby for most webapps and I can say that pretty confidently.

pjmlp 4y ago

On top of it, disregarding stored procedures and waste network traffic with data that is going to be thrown away.