5 ms·

Let's say you have 14 derived facts (eg aggregations, computations involving joined tables, etc) about a SalesPerson. Do create 14 single-column views? Or have

Let's say you have 14 derived facts (eg aggregations, computations involving joined tables, etc) about a SalesPerson. Do create 14 single-column views? Or have one view with 14 columns? Either way the composability is terrible. If you go with 14 views, the syntax is awful and not very resistant to refactoring. If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization).

pdntspa 4y ago
You can SELECT just the columns you want with one view, and IIRC that will exclude other columns from being retrieved unless they are the source of a derived value.
wswope 4y ago
> If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization). This is outdated; most modern DBs treat views as they would CTEs. If you don’t reference a defined field downstream, it doesn’t need to get calculated.
- default-kramer 4y ago
  It's more about the fact that a join can change the shape of the result set. Even if the columns being projected aren't surfaced, the DB still has to process the joins to make sure the result set has the correct shape. For example, a human might know that a join will always be one:one or one:zero-or-one, but the DB has no choice but to make sure. Perhaps using subqueries instead of joins would work, but that gets ugly too. (Or maybe my knowledge is outdated and the optimizers have gotten way better than they were 3-4 years ago.)
  - jdmichal 4y ago
    At least PostgreSQL's query optimizer can and will drop `LEFT JOIN` clauses if the data is not actually being used. It can't do that for `INNER JOIN` because it must verify that a matching row exists.
    - cm2187 4y ago
      For LEFT join it would also need to know the combination of columns you are joining by are unique in the right table, which will be the case in many scenarios (joining on a primary key) but not in the general case.
      - trimethylpurine 4y ago
        I'll add that MSSQL at least since 2019 will automatically modify the execution plan to avoid this by the second time the query is executed.
      - jdmichal 4y ago
        That's an excellent point. I hadn't thought of it because the joins where I witnessed this were all on primary keys, as $DIETY intended. (That's a joke.)
      - trimethylpurine 4y ago
        With most engines, this can be optimized with indexing (or indexed views) very easily to the extent it would be negligible.
zasdffaa 4y ago
As others have said, any decent optimiser will prune out values that aren't SELECT'ed. I don't think it's that complex either - find the unused columns, remove them from subviews, recurse all the way down. Try it on MSSQL/Postgres and you can see it being done.
fny 4y ago
The bigger problem is views are tightly coupled to the underlying data.
- saltcured 4y ago
  Right, there's a big difference between views and the kind of generic abstraction that I think the original poster seeks. You do need something like macros or generated SQL to build a library of algorithms you can apply to data. Imagine wanting something as simple as "find_percentile_value(table, column, percentile)". There is no portable and standard way to write this parametric abstraction and then reuse it on any column source you need to interrogate later.
  - gerdesj 4y ago
    Please give a simple example of what your second para means. Perhaps find_% of all values in the row vs the referenced column, in the referenced table. Now, ... reuse. Surely you write the result to another table and then refer to that.
    - saltcured 4y ago
      The detail of the function isn't that important, but I was thinking of something like the numpy percentile function as an example of a simple functional abstraction. You give it an array representing the population of values and a desired percentile (0..100), and it returns the value corresponding to that desired percentile. Calling it for percentile 50 would return the median value. Other options might select alternative methods for tie-breaking or interpolation between population samples. Note, this was just meant as an example where a view is not helpful. We don't always desire particular calculated data to be named for reuse. Rather, we want a reusable method we can apply to any data. In my earlier post, I tried to address the topic of macros or generated SQL, and so suggested it take a table and column name as input to expand a query idiom for a set of values stored in a table. But, that's not how I'd really want to do it. The rest of this comment will veer into a more elaborate perspective on real functional libraries, rather than mere macros... Today, with dialect-specific mechanisms, I can try to define window functions or ordered-set aggregate functions. It would be possible to solve the percentile problem this way. In fact, many SQL dialects have built-in functions that can help with percentiles, since this is recognized as such a basic statistical concept. But, I only chose percentile as an accessible yet concrete example. These built-in solutions are not the answer to my question. My question is how can user programmers extend the system with their own abstractions. Writing aggregate and window functions requires a big mental shift for the programmer. You usually need to define a state variable and provide a set of input/output/accumulator functions and register all that as an aggregate function. This is because of the way this calling convention is meant to be integrated into query plans in a streaming fashion. It's a bit like forcing a programmer to learn some async or co-routine calling convention without offering a simpler synchronous option as a starting point. It's an obstacle. The application of window or ordered-set aggregate functions also require a lot of verbose syntax to wire up columns or expressions to function inputs and to configure the windows and ordering modes. There isn't really any support to abstract these details away behind the named function, such that the naive user could just call it without knowing how it works internally to solve the whole problem. In other words, having a functional abstraction which can encapsulate data-preconditioning behind an easier interface. Finally, this approach only addresses the case of reducing a set to a scalar or assigning new scalars for every row in a set. To match the generality of the numpy API would require functions that take sets as input and produce arbitrarily different sets as output. It also needs the host language to allow low-syntax manipulation of intermediate data when composing functions. So, for full generality, a data transform function might take one set as input and produce another set as output. We would also want things like: set-based function interface typing and function overloading; functions that can setup their own desired traversals of input data including sorting and filtering; functions that can allocate (potentially) large intermediate data using sets or other common data structure idioms; and complementary call-site syntax to easily compose functions with little syntactic boilerplate but also with low marginal syntax overhead to customize and restructure data passed between functions.

pdntspa 4y ago

You can SELECT just the columns you want with one view, and IIRC that will exclude other columns from being retrieved unless they are the source of a derived value.

wswope 4y ago

> If you go with one view, you will eventually end up paying a performance cost for computations you actually aren't using in certain places (barring incredible and maybe impossible advancements in query optimization). This is outdated; most modern DBs treat views as they would CTEs. If you don’t reference a defined field downstream, it doesn’t need to get calculated.

default-kramer 4y ago
It's more about the fact that a join can change the shape of the result set. Even if the columns being projected aren't surfaced, the DB still has to process the joins to make sure the result set has the correct shape. For example, a human might know that a join will always be one:one or one:zero-or-one, but the DB has no choice but to make sure. Perhaps using subqueries instead of joins would work, but that gets ugly too. (Or maybe my knowledge is outdated and the optimizers have gotten way better than they were 3-4 years ago.)
- jdmichal 4y ago
  At least PostgreSQL's query optimizer can and will drop `LEFT JOIN` clauses if the data is not actually being used. It can't do that for `INNER JOIN` because it must verify that a matching row exists.
  - cm2187 4y ago
    For LEFT join it would also need to know the combination of columns you are joining by are unique in the right table, which will be the case in many scenarios (joining on a primary key) but not in the general case.
    - trimethylpurine 4y ago
      I'll add that MSSQL at least since 2019 will automatically modify the execution plan to avoid this by the second time the query is executed.
    - jdmichal 4y ago
      That's an excellent point. I hadn't thought of it because the joins where I witnessed this were all on primary keys, as $DIETY intended. (That's a joke.)
    - trimethylpurine 4y ago
      With most engines, this can be optimized with indexing (or indexed views) very easily to the extent it would be negligible.

zasdffaa 4y ago

As others have said, any decent optimiser will prune out values that aren't SELECT'ed. I don't think it's that complex either - find the unused columns, remove them from subviews, recurse all the way down. Try it on MSSQL/Postgres and you can see it being done.

fny 4y ago

The bigger problem is views are tightly coupled to the underlying data.

saltcured 4y ago
Right, there's a big difference between views and the kind of generic abstraction that I think the original poster seeks. You do need something like macros or generated SQL to build a library of algorithms you can apply to data. Imagine wanting something as simple as "find_percentile_value(table, column, percentile)". There is no portable and standard way to write this parametric abstraction and then reuse it on any column source you need to interrogate later.
- gerdesj 4y ago
  Please give a simple example of what your second para means. Perhaps find_% of all values in the row vs the referenced column, in the referenced table. Now, ... reuse. Surely you write the result to another table and then refer to that.
  - saltcured 4y ago
    The detail of the function isn't that important, but I was thinking of something like the numpy percentile function as an example of a simple functional abstraction. You give it an array representing the population of values and a desired percentile (0..100), and it returns the value corresponding to that desired percentile. Calling it for percentile 50 would return the median value. Other options might select alternative methods for tie-breaking or interpolation between population samples. Note, this was just meant as an example where a view is not helpful. We don't always desire particular calculated data to be named for reuse. Rather, we want a reusable method we can apply to any data. In my earlier post, I tried to address the topic of macros or generated SQL, and so suggested it take a table and column name as input to expand a query idiom for a set of values stored in a table. But, that's not how I'd really want to do it. The rest of this comment will veer into a more elaborate perspective on real functional libraries, rather than mere macros... Today, with dialect-specific mechanisms, I can try to define window functions or ordered-set aggregate functions. It would be possible to solve the percentile problem this way. In fact, many SQL dialects have built-in functions that can help with percentiles, since this is recognized as such a basic statistical concept. But, I only chose percentile as an accessible yet concrete example. These built-in solutions are not the answer to my question. My question is how can user programmers extend the system with their own abstractions. Writing aggregate and window functions requires a big mental shift for the programmer. You usually need to define a state variable and provide a set of input/output/accumulator functions and register all that as an aggregate function. This is because of the way this calling convention is meant to be integrated into query plans in a streaming fashion. It's a bit like forcing a programmer to learn some async or co-routine calling convention without offering a simpler synchronous option as a starting point. It's an obstacle. The application of window or ordered-set aggregate functions also require a lot of verbose syntax to wire up columns or expressions to function inputs and to configure the windows and ordering modes. There isn't really any support to abstract these details away behind the named function, such that the naive user could just call it without knowing how it works internally to solve the whole problem. In other words, having a functional abstraction which can encapsulate data-preconditioning behind an easier interface. Finally, this approach only addresses the case of reducing a set to a scalar or assigning new scalars for every row in a set. To match the generality of the numpy API would require functions that take sets as input and produce arbitrarily different sets as output. It also needs the host language to allow low-syntax manipulation of intermediate data when composing functions. So, for full generality, a data transform function might take one set as input and produce another set as output. We would also want things like: set-based function interface typing and function overloading; functions that can setup their own desired traversals of input data including sorting and filtering; functions that can allocate (potentially) large intermediate data using sets or other common data structure idioms; and complementary call-site syntax to easily compose functions with little syntactic boilerplate but also with low marginal syntax overhead to customize and restructure data passed between functions.