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

141–150 of 168 posts

Re: SQL Tips and Tricks

#141

Post by knighthack »

password4321 wrote: post #138
WuxiFingerHold wrote: post #137

I'm not a fan of "just in case" development. Not when it comes to interfaces and also not regarding this `where 1=1` placeholder. Do things when you need them. Not if you think you might need them someday in the futures. Also, production code is not the place to keep dev helpers around. Do what you want in dev time, but for prod code readability and clear intent is much more important.

Do you fully qualify all table + column name references? I've found it often increases readability by at least an order of magnitude but quickly becomes very verbose and incredibly painfully tedious to write.

With autocomplete (like with Jetbrain's Datagrip) I found that verbose table names aren't that much of a problem; they in fact really help readability.

Re: SQL Tips and Tricks

#142

Post by password4321 »

knighthack wrote: post #141
password4321 wrote: post #138

Earlier quoted context omitted.

Do you fully qualify all table + column name references? I've found it often increases readability by at least an order of magnitude but quickly becomes very verbose and incredibly painfully tedious to write.

With autocomplete (like with Jetbrain's Datagrip) I found that verbose table names aren't that much of a problem; they in fact really help readability.

Maybe fully qualifying all entity references is table stakes these days, I'll have to give Datagrip a spin.

I already complained about autocomplete in response to another comment asking to put FROM first; maybe existing tooling is enough to make my life easier.

Re: SQL Tips and Tricks

#143

Post by password4321 »

hot_gril wrote: post #46
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 in…

It would be cool to see something automating this conversion (and other similar performance workarounds) available as a first-class feature in a SQL IDE.

Re: SQL Tips and Tricks

#144

Post by galkk »

Idk, I feel it's missing really useful convenience stuff that exists here and there...

Examples, from the top of my head:

1. JOIN USING, for databases that support it. In some databases you can replace

    FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c2 = t2.c2 ...
with

    FROM t1 JOIN t2 USING (c1, c2)
much shorther and cleaner

2. Ability to exclude columns in select *

DuckDB:

    SELECT * EXCLUDE (c1)
Spanner

    SELECT * EXCEPT (c1)

Re: SQL Tips and Tricks

#145

Post by the_gorilla »

password4321 wrote: post #133
hans_castorp wrote: post #70

Earlier quoted context omitted.

> A lot of databases support trailing commas in select clauses. Which ones? Postgres, Oracle, SQL Server, MySQL, MariaDB and SQLite do not allow that.

Just append a random constant at the end of every SELECT column list instead, 42 to the rescue! (I kid, I kid.) I can't tell yet whether my experiment starting an all-OR WHERE clause with 0=1 so each OR could start the next line would go over like a lead balloon here too or not. One thing I've actually found useful especially in SQL is always including a single-line comment in front of the closing of every multi-line…

SQL has so little room for expression or opinionated formatting, so it's funny to people bikeshed over comma placement. I'm kind of jealous that they have time to think about whether left comma or right comma offends them greatly.

Re: SQL Tips and Tricks

#146

Post by password4321 »

swarnie wrote: post #35
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.

Alternatively, write a mess of SQL like a three year old child that just discovered MSPaint then push the "beautifier" button and knock off for an early lunch.

Where am I supposed to park my bike if the shed is gone?!

Closer to more seriously: which "beautifier" button is best? Is there a free one that is close to industry standard?

Re: SQL Tips and Tricks

#147

Post by password4321 »

regexman1 wrote: post #43
silveraxe93 wrote: post #40

Earlier quoted context omitted.

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…

> testing something and want to quickly comment a column out

This is a pretty solid reason for use in temporary queries so no doubt this approach will be around for a long time.

Re: SQL Tips and Tricks

#148

Post by password4321 »

Semaphor wrote: post #14

Regarding "Comment your code!": At least for MSSQL, it’s often recommended not to use -- for comments but instead /**/, because many features like the query store save queries without line breaks, so if you get the query from there, you need to manually fix everything instead of simply using your IDEs formatter.

Are you able to cast as XML? I use that for OBJECT_DEFINITION, eg.

  select name,cast((select OBJECT_DEFINITION(object_id) for xml path('')) as xml) from sys.procedures
This can be easier to straighten out since it preserves the newlines though other XML characters get mangled like > to >. One other option is VARBINARY plus something to un-hex it.

Re: SQL Tips and Tricks

#149

Post by password4321 »

layoric wrote: post #128
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…

Agreed. Learn how to use EXPLAIN and interpret using whatever tools you prefer. Also monitor your queries. Something I did in a previous start up was install PgHero, and man did that tool help me optimise and prioritise performance.

PgHero looks great (props for linking to related projects!), though somehow not discussed here much previously.

https://github.com/ankane/pghero

https://news.ycombinator.com/item?id=41299148#41300220 (mentioned among the author's other helpful tools and Ruby gems)

> helpful to identify slow queries in production, remove duplicate indexes, see missing indexes, keep an eye on table size, etc

--

I haven't recently put in the effort to find a copy of SQL Sentry from back when the full-featured edition was briefly free but even the "always free" version was helpful working with MSSQL query plans.

https://www.solarwinds.com/free-tools/plan-explorer

(NOTE: Not sure how free-but-pushy it is these days, but years ago it wasn't bad.)

Re: SQL Tips and Tricks

#150

Post by hot_gril »

password4321 wrote: post #143
hot_gril wrote: post #46

Earlier quoted context omitted.

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 in…

It would be cool to see something automating this conversion (and other similar performance workarounds) available as a first-class feature in a SQL IDE.

I ended up building jank Python-based tooling around it to sorta automate it. You select your key and it decides the ranges for you.

Wonder if it'd be useful at all for live applications or just data processing. For the former, would need to somehow execute all reads at the same MVCC version even though they're separate connections.

Post reply on HN