10 ms·

SQL Design Patterns (2010)

151 points by mci 1y ago

jslaby 1y ago
Of course, trying out the first example doesn't work on SQL Server..
- datadrivenangel 1y ago
  "We use Oracle syntax and write <column expr> <alias> instead of ANSI SQL <column expr> AS <alias>. Ditto for table expressions" Footnote on page 3.
  - jslaby 1y ago
    T-SQL can handle that alias expr just fine, but the seqNum returned is 4,8,12,16,20 instead of the 1,2,3... I tried on MySQL and it works fine. I'm not sure how SQL Server is handling those cartesian joins differently
potatoproduct 1y ago
Not ashamed to admit that I never really thought about the distinct operator 'being redundant' as its essentially just a group by.
- morkalork 1y ago
  distinct has always felt like a query smell to me. Too many junior analysts abusing it because they don't know the schema well and are over-joining entities
  - dspillett 1y ago
    DISTINCT is often a smell at the head (or middle) of a complex query as you are throwing away processed information, sometimes a lot of it, late in the game. Much better to filter it out earlier and not process it further, where possible. Filtering earlier, as well as reducing waste processing time (and probably memory use), increases the chance of the query planner being able to use an index for the filter which could greatly decrease the IO cost of your query.
  - ryanjshaw 1y ago
    Sometimes the number of joins is fine but they don’t understand the data properly and should be spending more time understanding why multiple rows are being returned when they expect one (eg they need to filter on an additional field). I wish SQL had a strict mode syntax that forces you to use something like `select one` (like LINQ’s Single()) or `select many` to catch these kinds of bugs.
- stevage 1y ago
  Huh, I have always just thought of it as a syntactic shortcut.
- paulddraper 1y ago
  SELECT DISTINCT is often a code smell. (Not always.) If you see it, there’s a 70% chance it got slapped on to fix an issue that should have been solved a different way. SELECT DISTINCT ON is different, and useful.
  - aspaviento 1y ago
    I had a teacher who had specific rules for exams when we wrote SQL statements: - For a question worth 2 points, if you use the word "DISTINCT" when it wasn't needed, you lose 0.5 points. - If you don't use "DISTINCT" when it was necessary, you lose all 2 points.
    - 9dev 1y ago
      Oh yes, introducing a little game theory anxiety into exam questions sounds like a wonderful little torturing tool!
alphazard 1y ago
I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL. You can always use Google to look up the SQL, or now ChatGPT to generate it for you. SQL is a not-that-great language and it intentionally hides what's going on. It is also different enough between databases that you need to pay attention. So learning to design/think in terms of SQL is probably not worth doing. The set of data structures that you use to model and index a dataset is worth understanding, and designing in that space is a skill worth learning.
- yakshaving_jgt 1y ago
  For posterity, how would you recommend the average working programmer should go about doing that?
  - ryanjshaw 1y ago
    Read code from other projects
  - alphazard 1y ago
    An intro data structures course is worth watching if you haven't taken one. There are plenty of them on YouTube. Try to follow along with a language that has an explicit pointer type. Go is a good choice. Java and Python are worse choices (for this particular thing) IMO. Assuming you are familiar with trees and hashmaps, you have all the important building blocks. You can imagine a database as a bunch of trees, hashmaps and occasionally other stuff, protected by a lock. First you acquire the lock, then you update some of the data structures, and maybe that requires you to update some of the other data structures (like indexes) for consistency. Then you release the lock. By default, most data will live in a BTree with an integer primary key, and that integer is taken from a counter that you increment for new inserts. Indexes will be BTrees where the key is stuff you want to query on, and the value is the primary key in the main table. Using just those data structures you should be able to plan for any query or insert pattern. It helps to figure this out yourself in a programming language for a few practice cases, so you know you can do it. Eventually it will be easy to figure out what tables and indexes you need in your head. In the real world, this stuff is jotted down in design docs, often as SQL or even just bullets. That's really all you need, and that's where I recommend getting out of the rabbit hole. Query planners are pretty good. You can usually just write SQL and if you did the work to understand what the tables and indexes should be, the planner will figure out how to use them to make the query fast.
deleted 1y ago
[deleted]
dspillett 1y ago
This is rather old and there are better ways of doing most of these things now. For instance the counting example would usually be much more efficient performed using the ROW_NUMBER() window function instead of a Cartesian product. When you can remove a cross product from your process it is almost always beneficial to do so. That will often involve introducing a CTE⁰¹ which might put off some beginners²³, but it shouldn't as this sort of example is pretty simple (you aren't worrying about any recursive case). ---- [0] Because you want the ordinal of the row in the input table/view, not your output. [1] You could also use a sub-query, in most cases a good query planner will see the equivalence and do the same thing for either. The CTE option is easier to read and maintain IMO. [2] In databases, like sports, CTEs can result in headaches! [3] Or veterans of postgres, where until a few years ago CTEs were an optimisation gate, blocking predicate push-down and making some filtered queries a lot more expensive (though often no more so than the naive Cartesian product method).
phartenfeller 1y ago
SQL is beautiful in its own way. Definitely not easy to master but beautiful in how much business logic you can implement in not many lines of code. And with SQL macros becoming a thing it is now easily possible to store patterns as reausable functions with parameters.
deleted 1y ago
[deleted]

151 points by mci 1y ago

jslaby 1y ago

Of course, trying out the first example doesn't work on SQL Server..

datadrivenangel 1y ago
"We use Oracle syntax and write <column expr> <alias> instead of ANSI SQL <column expr> AS <alias>. Ditto for table expressions" Footnote on page 3.
- jslaby 1y ago
  T-SQL can handle that alias expr just fine, but the seqNum returned is 4,8,12,16,20 instead of the 1,2,3... I tried on MySQL and it works fine. I'm not sure how SQL Server is handling those cartesian joins differently

potatoproduct 1y ago

Not ashamed to admit that I never really thought about the distinct operator 'being redundant' as its essentially just a group by.

morkalork 1y ago
distinct has always felt like a query smell to me. Too many junior analysts abusing it because they don't know the schema well and are over-joining entities
- dspillett 1y ago
  DISTINCT is often a smell at the head (or middle) of a complex query as you are throwing away processed information, sometimes a lot of it, late in the game. Much better to filter it out earlier and not process it further, where possible. Filtering earlier, as well as reducing waste processing time (and probably memory use), increases the chance of the query planner being able to use an index for the filter which could greatly decrease the IO cost of your query.
- ryanjshaw 1y ago
  Sometimes the number of joins is fine but they don’t understand the data properly and should be spending more time understanding why multiple rows are being returned when they expect one (eg they need to filter on an additional field). I wish SQL had a strict mode syntax that forces you to use something like `select one` (like LINQ’s Single()) or `select many` to catch these kinds of bugs.
stevage 1y ago
Huh, I have always just thought of it as a syntactic shortcut.
paulddraper 1y ago
SELECT DISTINCT is often a code smell. (Not always.) If you see it, there’s a 70% chance it got slapped on to fix an issue that should have been solved a different way. SELECT DISTINCT ON is different, and useful.
- aspaviento 1y ago
  I had a teacher who had specific rules for exams when we wrote SQL statements: - For a question worth 2 points, if you use the word "DISTINCT" when it wasn't needed, you lose 0.5 points. - If you don't use "DISTINCT" when it was necessary, you lose all 2 points.
  - 9dev 1y ago
    Oh yes, introducing a little game theory anxiety into exam questions sounds like a wonderful little torturing tool!

alphazard 1y ago

I always tell people to worry about the data structures that you want the database to maintain for you, and not worry about the SQL. You can always use Google to look up the SQL, or now ChatGPT to generate it for you. SQL is a not-that-great language and it intentionally hides what's going on. It is also different enough between databases that you need to pay attention. So learning to design/think in terms of SQL is probably not worth doing. The set of data structures that you use to model and index a dataset is worth understanding, and designing in that space is a skill worth learning.

yakshaving_jgt 1y ago
For posterity, how would you recommend the average working programmer should go about doing that?
- ryanjshaw 1y ago
  Read code from other projects
- alphazard 1y ago
  An intro data structures course is worth watching if you haven't taken one. There are plenty of them on YouTube. Try to follow along with a language that has an explicit pointer type. Go is a good choice. Java and Python are worse choices (for this particular thing) IMO. Assuming you are familiar with trees and hashmaps, you have all the important building blocks. You can imagine a database as a bunch of trees, hashmaps and occasionally other stuff, protected by a lock. First you acquire the lock, then you update some of the data structures, and maybe that requires you to update some of the other data structures (like indexes) for consistency. Then you release the lock. By default, most data will live in a BTree with an integer primary key, and that integer is taken from a counter that you increment for new inserts. Indexes will be BTrees where the key is stuff you want to query on, and the value is the primary key in the main table. Using just those data structures you should be able to plan for any query or insert pattern. It helps to figure this out yourself in a programming language for a few practice cases, so you know you can do it. Eventually it will be easy to figure out what tables and indexes you need in your head. In the real world, this stuff is jotted down in design docs, often as SQL or even just bullets. That's really all you need, and that's where I recommend getting out of the rabbit hole. Query planners are pretty good. You can usually just write SQL and if you did the work to understand what the tables and indexes should be, the planner will figure out how to use them to make the query fast.

deleted 1y ago

[deleted]

dspillett 1y ago

This is rather old and there are better ways of doing most of these things now. For instance the counting example would usually be much more efficient performed using the ROW_NUMBER() window function instead of a Cartesian product. When you can remove a cross product from your process it is almost always beneficial to do so. That will often involve introducing a CTE⁰¹ which might put off some beginners²³, but it shouldn't as this sort of example is pretty simple (you aren't worrying about any recursive case). ---- [0] Because you want the ordinal of the row in the input table/view, not your output. [1] You could also use a sub-query, in most cases a good query planner will see the equivalence and do the same thing for either. The CTE option is easier to read and maintain IMO. [2] In databases, like sports, CTEs can result in headaches! [3] Or veterans of postgres, where until a few years ago CTEs were an optimisation gate, blocking predicate push-down and making some filtered queries a lot more expensive (though often no more so than the naive Cartesian product method).

phartenfeller 1y ago

SQL is beautiful in its own way. Definitely not easy to master but beautiful in how much business logic you can implement in not many lines of code. And with SQL macros becoming a thing it is now easily possible to store patterns as reausable functions with parameters.

deleted 1y ago

[deleted]