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

51–60 of 168 posts

Re: SQL Tips and Tricks

#51

Post by GrumpyNl »

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.

When you comment the first statement, that doesnt have the ",", it will break and you still have to remove the "," from the second line, so your comment is not valid.

Re: SQL Tips and Tricks

#53

Post by [deleted] »

Semaphor wrote: post #17
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. That very much depends on your data.

[deleted]

Re: SQL Tips and Tricks

#54

Post by hinkley »

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.

You can’t control the growth rate of your tables, you can only estimate it. When we design for reliability we want to remove single cause failures and make a best effort to reduce dual cause failures. We definitely don’t want two failures from a single cause.

What reason might the lack of indexes suddenly become a critical issue? And what other things might you be scrambling to deal with at the same time? Tables might fill quickly when a favorable review comes in, or some world even results in churn in your system.

Just make the damned index. You Are Going to Need It. And what’s the harm in making it?

Re: SQL Tips and Tricks

#55

Post by hinkley »

chipdart wrote: post #41
silveraxe93 wrote: post #13

The "readability" section has 3 examples. The first 2 are literally sacrificing readability so it's easier to write, and the last has an unreadable abomination that indenting is really not doing much.

> The first 2 are literally sacrificing readability so it's easier to write, (...) The leading comma format brings benefits beyond readability. For example, in version control systems the single-argument-per-line-with-leading-comma format turns any change to those arguments as a one-line diff. I think developers spend as much time looking at commit historyas they do to the actual source code.

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.

Re: SQL Tips and Tricks

#56

Post by hinkley »

silveraxe93 wrote: post #13

The "readability" section has 3 examples. The first 2 are literally sacrificing readability so it's easier to write, and the last has an unreadable abomination that indenting is really not doing much.

Who splits column per line in the SELECT block and still leave 150 character wide lines? This is a fucked up definition of legibility. I can’t even get started on the commas.

NOBODY CHECKS LONG LINES IN CODE REVIEWS. That was the biggest problem with AngularJS. People mishandling merges and breaking everything because the eyes start to glaze over at column 90. I’ve been on more than half a dozen teams with CRs and it’s always the same. I’m exquisitely aware of this and try not to do it, and I still fuck it up half as often as the next person.

Split your shit up. Especially when trying to set an example for others.

Re: SQL Tips and Tricks

#58

Post by Semaphor »

hinkley wrote: post #54
Semaphor wrote: post #44

Earlier quoted context omitted.

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.

You can’t control the growth rate of your tables, you can only estimate it. When we design for reliability we want to remove single cause failures and make a best effort to reduce dual cause failures. We definitely don’t want two failures from a single cause. What reason might the lack of indexes suddenly become a critical issue? And what other things might you be scrambling to deal with at the same time? Tables migh…

Depending on your application, you can very accurately estimate it. And in a case I had yesterday, it involved stringy numbers because of a third party system, so I could indeed add a computed persisted column that converts our number to a VARCHAR, add a 9th index with a lot of fields on that computed column and then save… almost nothing compared to just scanning 6k rows.

Re: SQL Tips and Tricks

#59

Post by chipdart »

hinkley wrote: post #55
chipdart wrote: post #41

Earlier quoted context omitted.

> The first 2 are literally sacrificing readability so it's easier to write, (...) The leading comma format brings benefits beyond readability. For example, in version control systems the single-argument-per-line-with-leading-comma format turns any change to those arguments as a one-line diff. I think developers spend as much time looking at commit historyas they do to the actual source code.

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 single line. I don't know where you got that idea from.

Re: SQL Tips and Tricks

#60

Post by code_biologist »

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 clause usually dictates index use.

The reason for this wasn't immediately apparent to me. For those who were curious, this blog post walks through it step by step: https://www.brentozar.com/archive/2015/06/indexing-for-group...

Post reply on HN