Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQL Tips and Tricks

github.com

Post reply on HN
Print view View original

61–70 of 168 posts

Re: SQL Tips and Tricks

#61

Post by hinkley »

chipdart wrote: post #59
hinkley wrote: post #55

Earlier quoted context omitted.

If you’re still using a diff tool that can’t do sub-line diffs it’s time to join the 20’s. I haven’t been forced to use one of those in over ten years.

> If you’re still using a diff tool that can’t do sub-line diffs it’s time to join the 20’s. I think you failed to understand what I wrote. Leading comma ensures one line diffs, but trailing comma forces two-line diffs when you add a trailing argument. With trailing comma, you need to touch the last line to add a comma, and then add the new argument in the line below. We are not discussing bundling all arguments in a…

What’s the value in doing this unless it makes the diff clearer?

It only makes the diff clearer if you don’t have single character highlighting in your diff tool. Which most have now. Have had for a decade.

Also it’s not going to be a single line anyway. You add a line to the query and one to the caller. At a minimum. So you’re really arguing for three versus four. Which is false economy.

Re: SQL Tips and Tricks

#62

Post by chipdart »

hinkley wrote: post #61
chipdart wrote: post #59

Earlier quoted context omitted.

> If you’re still using a diff tool that can’t do sub-line diffs it’s time to join the 20’s. I think you failed to understand what I wrote. Leading comma ensures one line diffs, but trailing comma forces two-line diffs when you add a trailing argument. With trailing comma, you need to touch the last line to add a comma, and then add the new argument in the line below. We are not discussing bundling all arguments in a…

What’s the value in doing this unless it makes the diff clearer? It only makes the diff clearer if you don’t have single character highlighting in your diff tool. Which most have now. Have had for a decade. Also it’s not going to be a single line anyway. You add a line to the query and one to the caller. At a minimum. So you’re really arguing for three versus four. Which is false economy.

> What’s the value in doing this unless it makes the diff clearer?

Because it makes the diff clearer.

Are you even reading the posts you're replying to?

Re: SQL Tips and Tricks

#63

Post by magicalhippo »

code_biologist wrote: post #60
magicalhippo wrote: post #11

I'll add some of mine: Learn your DB server. Check the query plans often. You might get surprised. Tweak and recheck. Usually EXISTS is faster than IN. Beware that NOT EXISTS behaves differently than EXCEPT in regards to NULL values. Instead of joining tables and using distinct or similar to filter rows, consiser using subquery "columns", ie in SELECT list. This can be much faster even if you're pulling 10+ values fr…

Instead of joining tables and using distinct or similar to filter rows, consiser using subquery "columns", ie in SELECT list. What does this mean? Running SELECT column1, ( SELECT column2, column3, ... FROM table_b WHERE table_a.id = table_b.a_id ) FROM table_a Results in "subquery must return only one column" as I expected. You mean returning the multiple columns as a record / composite type? Keep in mind GROUP BY c…

Sorry, was on mobile so hadn't patience to type examples.

    SELECT
      column1,
      (
        SELECT column2
        FROM table_b
        WHERE table_a.id = table_b.a_id
      ) as b_column2,
      (
        SELECT column3
        FROM table_b
        WHERE table_a.id = table_b.a_id
      ) as b_column3
    FROM table_a
It might look like a lot more work, but in my experience it's usually a lot faster. YMMV but check it.

Re: SQL Tips and Tricks

#64

Post by magicalhippo »

Semaphor wrote: post #44
magicalhippo wrote: post #18

Earlier quoted context omitted.

I should have noted that I was talking about application workloads. I don't have much experience with analytics workloads. If you have something else in mind, do feel free to elaborate.

Relevant for applications as well, when a table only has a few thousand entries, a scan is not the end of the world and not even an outage in waiting. I agree with you that one should seek when possible as part of normal query optimization, but depending on your data, it could also just easily be something you can live with forever.

As usual, there are well-qualified exceptions. If you are very certain the table scan can't hurt, sure. But in my experience, an index wouldn't hurt any in those cases.

Re: SQL Tips and Tricks

#65

Post by hans_castorp »

magicalhippo wrote: post #11

I'll add some of mine: Learn your DB server. Check the query plans often. You might get surprised. Tweak and recheck. Usually EXISTS is faster than IN. Beware that NOT EXISTS behaves differently than EXCEPT in regards to NULL values. Instead of joining tables and using distinct or similar to filter rows, consiser using subquery "columns", ie in SELECT list. This can be much faster even if you're pulling 10+ values fr…

> Any query that's not a one-off should not perform any table scans. A table scan today can mean an outage tomorrow

I disagree.

There are queries where a table scan is the most efficient access strategy. These are typically analytical/aggregation queries that usually query the whole table. And sometimes getting only 50% of all rows is better done using a table scan as well.

I also don't see how a (read only) "table scan" could leave to an outage. It won't block concurrent access. The only drawback is that it results in a higher I/O load - but if the server can't handle that, it would assume it's massively undersized.

Re: SQL Tips and Tricks

#66

Post by disgruntledphd2 »

silveraxe93 wrote: post #21
willvarfar wrote: post #15

Earlier quoted context omitted.

A lot of databases support trailing commas in select clauses. Which is just as well. I want to scratch my eyes out every time I see someone formatting with comma starting the lines. It's the kind of foolish consistency that is a big part of performative engineering.

> I want to scratch my eyes out every time I see someone formatting with comma starting the lines Right!? I _physically_ recoil every time I see that. I think that's the clearest example of normalisation of deviance [1] I know. Seems like anyone that enters the industry straight from data instead of moving from a more (software) engineering background gets used to this. And the arguments in favour are always so weak!…

I'm a data person and despite seeing this for years, still despise that approach to commas. Seriously, it's not that hard to comment out the damn comma.

Re: SQL Tips and Tricks

#67

Post by higeorge13 »

yen223 wrote: post #29
sgarland wrote: post #7

Not shown: stop using SELECT *. You almost certainly do not need the entire width of the table, and by doing so, you add more data to filter and transmit, and also prevent semijoins, which are awesome.

There are broadly two kinds of people who write SQL: analysts, and developers For developers, yeah. SELECT * has pitfalls, and you should almost always specify your columns or use a query builder that does that for you. For analysts though, life is short and sometimes you really don't want to type all the columns out. SELECT * is fine.

Analysts usually query data warehouses, which are columnar, so * is a query/warehouse killer. Everybody should just select the columns they need.

Re: SQL Tips and Tricks

#68

Post by magicalhippo »

hans_castorp wrote: post #65
magicalhippo wrote: post #11

I'll add some of mine: Learn your DB server. Check the query plans often. You might get surprised. Tweak and recheck. Usually EXISTS is faster than IN. Beware that NOT EXISTS behaves differently than EXCEPT in regards to NULL values. Instead of joining tables and using distinct or similar to filter rows, consiser using subquery "columns", ie in SELECT list. This can be much faster even if you're pulling 10+ values fr…

> Any query that's not a one-off should not perform any table scans. A table scan today can mean an outage tomorrow I disagree. There are queries where a table scan is the most efficient access strategy. These are typically analytical/aggregation queries that usually query the whole table. And sometimes getting only 50% of all rows is better done using a table scan as well. I also don't see how a (read only) "table s…

I mentioned in a different reply that I did not have analytic queries in mind. I don't work with that so forgot to specify.

Outage might "just" mean slow enough that customer can't get their work done in time. For the customer it's the same.

Re: SQL Tips and Tricks

#70

Post by hans_castorp »

willvarfar wrote: post #15
the_gorilla wrote: post #6

Leading comma is nice in SELECT statements because you can comment toggle individual lines. Indenting your code to make it more readable is basically what anyone with room temperature IQ does automatically. A lot of these other tips look like they're designed to deal with SQL design flaws, like how handling nulls isn't well defined in the spec so it's up to each implementation to do whatever it wants.

A lot of databases support trailing commas in select clauses. Which is just as well. I want to scratch my eyes out every time I see someone formatting with comma starting the lines. It's the kind of foolish consistency that is a big part of performative engineering.

> A lot of databases support trailing commas in select clauses.

Which ones?

Postgres, Oracle, SQL Server, MySQL, MariaDB and SQLite do not allow that.

Post reply on HN