News for hackers. Stuff that matters.
Re:Medium-hard SQL interview questions by 0az on Saturday May 02, 2020 @04:20PM (#23054600)
What is wrong with DISTINCT?
Re:Medium-hard SQL interview questions by barbegal on Saturday May 02, 2020 @04:39PM (#23054724)
DISTINCT generally requires the results to be sorted which has O(n^2) worst performance so it can have a big performance hit on a query. It is best to make your database structure such that queries only return distinct data. E.g. by disallowing duplicates
Re:Medium-hard SQL interview questions by namibj on Saturday May 02, 2020 @10:11PM (#23056345)
If your sorting algorithm degrades to anywhere near O(n^2) in pathological cases, you're doing something wrong. And even if it's just a kind of timeout/operations-limit to detect pathological cases and just run an in-place mergesort instead. Tail latency/containing pathological data is quite important if there's any interactivity.
Re:Medium-hard SQL interview questions by marcosdumay on Saturday May 02, 2020 @04:36PM (#23054709)
Nearly every time, it's a symptom of bad data normalization.

But every time, it interferes badly with any kind of locking (that's DBMS dependent, of course), and imposes a high performance penalty (on every DBMS).

Re:Medium-hard SQL interview questions by philshem on Saturday May 02, 2020 @04:38PM (#23054722)
“Think before you DISTINCT”
Re:Medium-hard SQL interview questions by barrkel on Saturday May 02, 2020 @05:30PM (#23055085)
In order to determine the distinct items, the items need to be deduplicated. Generally that's done in only two ways: a hash table that skips items already seen, or a sort followed by a scan that skips over duplicates. The hash table is O(1), but the sort is easier to make parallel without sharing mutable state and has more established algorithms to use when spilling to disk.
Re:Medium-hard SQL interview questions by branko_d on Saturday May 02, 2020 @08:40PM (#23056019)
There is a third way: keep the data pre-sorted in the database (via an index).
Re:Medium-hard SQL interview questions by GlennS on Saturday May 02, 2020 @10:15PM (#23056359)
It covers up bad queries, so you may not see an underlying data duplication problem.

Often better to group explicitly so you know what's actually going on.

Re:Medium-hard SQL interview questions by matwood on Sunday May 03, 2020 @07:37AM (#23058403)
> It covers up bad queries,

Bingo. I used to work with a guy who would see duplicate results and just throw a distinct on his query. I had to keep on him to fix his queries or explain why distinct was correct in this case. My default is that distinct is almost always not the solution.

Re:Medium-hard SQL interview questions by cultofmetatron on Saturday May 02, 2020 @04:24PM (#23054630)
seriously.. I'm building out some functionality using plpgsql and have used it. This is going to be haunting my dreams
Re:Medium-hard SQL interview questions by wesd on Saturday May 02, 2020 @04:48PM (#23054785)
there is possible performance hit [1]. Also, it could mean that the data granularity has not been modeled well if there are duplicate rows.

https://sqlperformance.com/2017/01/t-sql-queries/surprises-a...