4 ms·

I'm not allergic to SQL Server. I didn't study the example code; I was just curious about why DISTINCT was a problem. TL;DR: for some query planners, DISTINCT

I'm not allergic to SQL Server. I didn't study the example code; I was just curious about why DISTINCT was a problem. TL;DR: for some query planners, DISTINCT requires a table sort, which requires a full table scan, which can be avoided by using a construct (GROUP BY) that gives the planner hints. But I assumed that was dependent on the query planner; Oracle SQL, as I recall, has/had specific constructs for giving hints to the query planner.

dspillett 3y ago
> But I assumed that was dependent on the query planner; Problems that can be “fixed” with DISTINCT but really shouldn't, an accidental cross-join being one I've seen many times, be are common to many (all?) SQL implementations and I don't think they are treat much better by any of them (they are generally mistakes, so prioritising optimising for them is not a good use of the query planner engineering team's time). > Oracle SQL, as I recall, has/had specific constructs for giving hints to the query planner. SQL Server has index hints much like Oracles, along with locking hints (which I think Oracle has similarly), and so forth, which can tweak the query planners behaviour. They are best avoided if other options are available because: • they often only work well for a given data pattern, so you are just pushing the problem into the future when your data size has grown noticeably (in this context they are valuable as a temporary workaround, just make sure you treat them as such and work to remove the need) • they potentially block optimisations that future versions of the query planner/engine can perform by forcing it a different way around • sometimes they make explicit reference to objects (indexes usually), tightly coupling their names to things that others may not expect making accidental breaking changes more likely Postgres doesn't have the same sort of index hints IIRC, but does have options that constrain the query planner in similarly to try make it prefer a particular path and/or avoid another.
- denton-scratch 3y ago
  Yeah, thanks. I get all that; avoid if possible any proprietary languange constructs.Try to tune the query and the schema so that no sane optimizer would do the wrong thing. But the last time I tangled with Oracle, I was working for a bank; schema changes took months to approve, and the DBAs were GHODZ. And there was no chance of the bank changing suppliers anytime soon. And anyway, EVERYTHING was proprietary. They had their own proprietary stored-procedure language, and it wasn't dispensable - I believe people bought Oracle because of their stored procedure setup. To be clear, I'm not in any sense advocating hinted SQL!

dspillett 3y ago

> But I assumed that was dependent on the query planner; Problems that can be “fixed” with DISTINCT but really shouldn't, an accidental cross-join being one I've seen many times, be are common to many (all?) SQL implementations and I don't think they are treat much better by any of them (they are generally mistakes, so prioritising optimising for them is not a good use of the query planner engineering team's time). > Oracle SQL, as I recall, has/had specific constructs for giving hints to the query planner. SQL Server has index hints much like Oracles, along with locking hints (which I think Oracle has similarly), and so forth, which can tweak the query planners behaviour. They are best avoided if other options are available because: • they often only work well for a given data pattern, so you are just pushing the problem into the future when your data size has grown noticeably (in this context they are valuable as a temporary workaround, just make sure you treat them as such and work to remove the need) • they potentially block optimisations that future versions of the query planner/engine can perform by forcing it a different way around • sometimes they make explicit reference to objects (indexes usually), tightly coupling their names to things that others may not expect making accidental breaking changes more likely Postgres doesn't have the same sort of index hints IIRC, but does have options that constrain the query planner in similarly to try make it prefer a particular path and/or avoid another.

denton-scratch 3y ago
Yeah, thanks. I get all that; avoid if possible any proprietary languange constructs.Try to tune the query and the schema so that no sane optimizer would do the wrong thing. But the last time I tangled with Oracle, I was working for a bank; schema changes took months to approve, and the DBAs were GHODZ. And there was no chance of the bank changing suppliers anytime soon. And anyway, EVERYTHING was proprietary. They had their own proprietary stored-procedure language, and it wasn't dispensable - I believe people bought Oracle because of their stored procedure setup. To be clear, I'm not in any sense advocating hinted SQL!