5 ms·

Keep your matching logic simple. With SQL, matching is (by far) the most computationally expensive bit of most queries. Your goal should be to use only sargabl

Keep your matching logic simple. With SQL, matching is (by far) the most computationally expensive bit of most queries. Your goal should be to use only sargable[1] operators, and eliminate any functions, in all your WHERE clauses and JOINs. Complicated matching criteria will force the engine to make tough trade-off decisions, which can be affected by out-of-date table statistics, lousy indexes, and a million other things, and will often force the engine to rule-out more optimized algorithms and fall back to the cruder, primitive algorithms. [1] For most queries, "OR" is not usually a sargable operator: https://en.wikipedia.org/wiki/Sargable https://en.wikipedia.org/wiki/Sargable

setr 6y ago
Shouldn’t OR operations in an otherwise sargable[0] query just constitute merging two sargable queries? I don’t see why OR should affect the property [0] new term for me, and wow do I not like it
- kroolik 6y ago
  This depends on the rdbms engine. Even if it might make sense, the planned could go with nested loops because of statistics and cost estimation. In the article, the devil is in how the second explodes in size. We are suddenly cross-joining employees with customers. Left join of stores, employees and markouts gives you more than 2 rows. It gives you all the employees assigned to the store. Regardless only 2 have markouts. Next, you join full customers table with it. And keep on multiplying the size by further left joining. Result size might quickly exceed the memory limits for efficient joins, and the rdbms might have to resort to poor nested loops.
dragonwriter 6y ago
> Your goal should be to use only sargable[1] operators, and eliminate any functions, in all your WHERE clauses and JOINs. Or just use indexes properly; it's fairly common for modern databases to support indexes on functions of column values; using an indexed (deterministic) function in WHERE/JOIN criteria isn’t problematic. The Wikipedia article you link addresses valid concerns, but the specifics it suggests don't seem accurate for the features of modern RDBMSs.
- haimez 6y ago
  Really seems like you don’t know what sargable means (and didn’t bother to find out if this is your reply).
  - fastball 6y ago
    I think it might be the other way around.
  - epr 6y ago
    No, its pretty clear from reading his entire response that he understands exactly what sargable means. "just use indexes properly" is pretty ambiguous, but the rest of his comment provides context. It doesn't make much sense to me to be indexing on fn(x) for predicates unless an alternative sargable predicate is too complex or not possible, but that doesn't mean that it's never the best solution.
    - candu 6y ago
      > unless an alternative sargable predicate is too complex or not possible... As you note, functional indexes can sometimes be the best solution - some common examples: when working with different geospatial projections in PostGIS; when building a full-text index using `to_tsvector`. For anyone who works with relational databases that support this, definitely a good tool to have in your toolkit!
- jbverschoor 6y ago
  Also common for ancient RDBMSes
slt2021 6y ago
the problem with author slow query is joining 7 tables (forcing SQL engine to compute cartesian of 7 tables), this is the primary source of slow down. the faster query with UNION is joining 4 tables and unions with 5 tables, so the faster query is two orders of magnitude faster, because the SQL engine has to compute cartesian product on 5 instead of 7 tables
- Albert_Camus 6y ago
  Author here. Between this and your other response, where you expound on the same point, I think you're being far too hand wavy about what causes performance issues. The number of joins alone doesn't have much to do with the performance characteristics of any query. What's more important for performance of queries on larger data sets than the number of joins is that there are indexes and that the query is written in a way that can utilize indexes to avoid multiplicative slowdowns. The reason the UNION query is fast is because the query on either side effectively utilizes the indexes so that the database engine can limit the number of rows immediately, rather than filter them out after multiplying them all together. I can expand this schema to have a UNION query with two 10-table joins and it would still perform better than the 7 table query. I think someone new to SQL is likely to read your statement and think "okay joins are slow so I guess I should avoid joins". This is not true and this belief that joins are slow leads people down the path that ends at "SQL just doesn't scale" and "let's build a complicated Redis-backed caching layer". SQL performance is a complex topic. The point of our post was to illustrate that a UNION query can simplify how your join your tables and allow you to write constituent queries that have better performance characteristics. Morphing this into "the number of joins is smaller so the performance is better" is just incorrect.
  - slt2021 6y ago
    Sorry, if I was hand-wavy. I was trying to give other people a simple framework that I use myself (Big O calculated as a number of rows in each table). ALthough I dont know how many rows you have in each table, Big O frameworks still works, because cartesian of tables is being dominated by two huge different datasets (customers's orders being joined to employees' orders). The Union simply calcualtes them separately, rather than doing cartesian of two completely different datasets that represent different entities. Well written predicates and indexes can help, as well as poorly written predicates make it worse. So there is balance. This is not a shortcut or a silver bullet, it is a trade-off being made. More indexes->faster selects and slower updates/inserts. One bad index=>failed insert and possible losing customer data (happened to me once) I agree with you that "SQL performance is a complex topic." and one should definitely study query Execution Plan to understand the bottlenecks and make optimization decisions

setr 6y ago

Shouldn’t OR operations in an otherwise sargable[0] query just constitute merging two sargable queries? I don’t see why OR should affect the property [0] new term for me, and wow do I not like it

kroolik 6y ago
This depends on the rdbms engine. Even if it might make sense, the planned could go with nested loops because of statistics and cost estimation. In the article, the devil is in how the second explodes in size. We are suddenly cross-joining employees with customers. Left join of stores, employees and markouts gives you more than 2 rows. It gives you all the employees assigned to the store. Regardless only 2 have markouts. Next, you join full customers table with it. And keep on multiplying the size by further left joining. Result size might quickly exceed the memory limits for efficient joins, and the rdbms might have to resort to poor nested loops.

dragonwriter 6y ago

> Your goal should be to use only sargable[1] operators, and eliminate any functions, in all your WHERE clauses and JOINs. Or just use indexes properly; it's fairly common for modern databases to support indexes on functions of column values; using an indexed (deterministic) function in WHERE/JOIN criteria isn’t problematic. The Wikipedia article you link addresses valid concerns, but the specifics it suggests don't seem accurate for the features of modern RDBMSs.

haimez 6y ago
Really seems like you don’t know what sargable means (and didn’t bother to find out if this is your reply).
- fastball 6y ago
  I think it might be the other way around.
- epr 6y ago
  No, its pretty clear from reading his entire response that he understands exactly what sargable means. "just use indexes properly" is pretty ambiguous, but the rest of his comment provides context. It doesn't make much sense to me to be indexing on fn(x) for predicates unless an alternative sargable predicate is too complex or not possible, but that doesn't mean that it's never the best solution.
  - candu 6y ago
    > unless an alternative sargable predicate is too complex or not possible... As you note, functional indexes can sometimes be the best solution - some common examples: when working with different geospatial projections in PostGIS; when building a full-text index using `to_tsvector`. For anyone who works with relational databases that support this, definitely a good tool to have in your toolkit!
jbverschoor 6y ago
Also common for ancient RDBMSes

slt2021 6y ago

the problem with author slow query is joining 7 tables (forcing SQL engine to compute cartesian of 7 tables), this is the primary source of slow down. the faster query with UNION is joining 4 tables and unions with 5 tables, so the faster query is two orders of magnitude faster, because the SQL engine has to compute cartesian product on 5 instead of 7 tables

Albert_Camus 6y ago
Author here. Between this and your other response, where you expound on the same point, I think you're being far too hand wavy about what causes performance issues. The number of joins alone doesn't have much to do with the performance characteristics of any query. What's more important for performance of queries on larger data sets than the number of joins is that there are indexes and that the query is written in a way that can utilize indexes to avoid multiplicative slowdowns. The reason the UNION query is fast is because the query on either side effectively utilizes the indexes so that the database engine can limit the number of rows immediately, rather than filter them out after multiplying them all together. I can expand this schema to have a UNION query with two 10-table joins and it would still perform better than the 7 table query. I think someone new to SQL is likely to read your statement and think "okay joins are slow so I guess I should avoid joins". This is not true and this belief that joins are slow leads people down the path that ends at "SQL just doesn't scale" and "let's build a complicated Redis-backed caching layer". SQL performance is a complex topic. The point of our post was to illustrate that a UNION query can simplify how your join your tables and allow you to write constituent queries that have better performance characteristics. Morphing this into "the number of joins is smaller so the performance is better" is just incorrect.
- slt2021 6y ago
  Sorry, if I was hand-wavy. I was trying to give other people a simple framework that I use myself (Big O calculated as a number of rows in each table). ALthough I dont know how many rows you have in each table, Big O frameworks still works, because cartesian of tables is being dominated by two huge different datasets (customers's orders being joined to employees' orders). The Union simply calcualtes them separately, rather than doing cartesian of two completely different datasets that represent different entities. Well written predicates and indexes can help, as well as poorly written predicates make it worse. So there is balance. This is not a shortcut or a silver bullet, it is a trade-off being made. More indexes->faster selects and slower updates/inserts. One bad index=>failed insert and possible losing customer data (happened to me once) I agree with you that "SQL performance is a complex topic." and one should definitely study query Execution Plan to understand the bottlenecks and make optimization decisions