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

11–20 of 168 posts

Re: SQL Tips and Tricks

#11

Post by magicalhippo »

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 from the same table, even if your database server supports lateral joins. Just make sure the subqueries return at most one row.

Any query that's not a one-off should not perform any table scans. A table scan today can mean an outage tomorrow. Add indexes. Keep in mind GROUP BY clause usually dictates index use.

If you need to filter on expressions, say where a substring is equal something, you can add a computed column and index on that. Alternatively some db's support indexing expressions directly.

Often using UNION ALL can be much faster than using OR, even for non-trivial queries and/or multiple OR clauses.

edit: You can JOIN subqueries. This can be useful to force the filtering order if the DB isn't being clever about the order.

Re: SQL Tips and Tricks

#12

Post by Semaphor »

youdeet wrote: post #9

One more point in the "Anti Join". Use EXISTS instead of IN and LEFT JOIN if you only want to check existence of a row in another large table / subquery based on the conditions. EXISTS returns true as soon as it has found a hit. In case of LEFT JOIN and IN engine collects all results before evaluating.

Yeah, I was a bit confused there. In all my testing, (NOT) EXISTS was generating either a better plan or the same one as (LEFT) JOIN/(NOT) IN. In addition, it’s also clearer what the intent is.

Re: SQL Tips and Tricks

#13

Post by silveraxe93 »

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.

Re: SQL Tips and Tricks

#14

Post by Semaphor »

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.

Re: SQL Tips and Tricks

#15

Post by willvarfar »

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.

Re: SQL Tips and Tricks

#17

Post by Semaphor »

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.

Re: SQL Tips and Tricks

#18

Post by magicalhippo »

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.

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.

Post reply on HN