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

41–50 of 168 posts

Re: SQL Tips and Tricks

#41

Post by chipdart »

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.

Re: SQL Tips and Tricks

#42

Post by halayli »

regexman1 wrote: post #30
AtNightWeCode wrote: post #16

Never use WHERE 1=1. It is both a security risk and a performance risk to run dynamic ad-hoc queries.

I'll add this as a caveat. I'm an analyst so my SQL isn't really exposed to anyone other than myself and so I wasn't aware of this, thanks for flagging.

A random person claims adding 1=1 is a security risk and you are going to add it as caveat without verifying if the claim is true nor knowing why? That's how misinformation spreads around.

OP doesn't know what they are talking about because adding 1=1 is not a security risk. 1=1 is related to sql injections where a malicious attacker injects 'OR 1=1' into the end of the where clause to disable the where clause completely. OP probably saw '1=1' and threw that into the comment.

Re: SQL Tips and Tricks

#43

Post by regexman1 »

silveraxe93 wrote: post #40
regexman1 wrote: post #33

Earlier quoted context omitted.

That's a totally valid point haha.

I'll try to give some constructive criticism instead of a drive by pot shot. I'm sorry, it's just that the leading commas make my eyes bleed and I really hope the industry moves away from it. On point 3: What I do is use CTEs to create intermediate columns (with good names) and then a final one creating the final column. It's way more readable. ```sql with intermediate as ( select DATEDIFF(DAY, timeslot_date, CURRENT…

I appreciate the feedback, no offence taken. I'm an analyst so I often find the leading comma useful when I'm testing something and want to quickly comment a column out but I take your point.

And I agree, I should have used CTEs for this query, I was just trying to save lines of code which had the unintended consequence of quite an ugly query. However I did want to use it as an example of indentation being useful to make it slightly easier to read. Although perhaps I'm the only one who thinks so.

I greatly appreciate the constructive criticism.

Re: SQL Tips and Tricks

#44

Post by Semaphor »

magicalhippo wrote: post #18
Semaphor wrote: post #17

Earlier quoted context omitted.

> 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.

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.

Re: SQL Tips and Tricks

#46

Post by hot_gril »

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…

The most useful thing is learning your DBMS. There's no escaping the performance and isolation quirks of each one, and there are different bonus features in each.

One interesting thing I found about Postgres that's probably true of others too, often you can manually shard INSERT (SELECT ...) operations to speed them up linearly with the number of CPU cores, even when you have like 10 joins. EXPLAIN first, find the innermost or outermost join, and kick off a separate parallel query operating on each range of rows (id >= start AND id < end). For weird reasons, I relied on this a lot for one job 6 years ago. Postgres has added parallelism in versions 10+, but it's still not this advanced afaik.

Re: SQL Tips and Tricks

#48

Post by regexman1 »

halayli wrote: post #42
regexman1 wrote: post #30

Earlier quoted context omitted.

I'll add this as a caveat. I'm an analyst so my SQL isn't really exposed to anyone other than myself and so I wasn't aware of this, thanks for flagging.

A random person claims adding 1=1 is a security risk and you are going to add it as caveat without verifying if the claim is true nor knowing why? That's how misinformation spreads around. OP doesn't know what they are talking about because adding 1=1 is not a security risk. 1=1 is related to sql injections where a malicious attacker injects 'OR 1=1' into the end of the where clause to disable the where clause comple…

Fair point!

Re: SQL Tips and Tricks

#49

Post by arkh »

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…

> Just make sure the subqueries return at most one row.

The JSON functions most RDBMS offer are awesome for that. One subquery to get a JSON if you have multiple results for the field then you only have to decode it on the app side.

Post reply on HN