4 ms·

>Seems a textbook case of using a view for sharing query logic. Views are just terrible outside of data analyst style work. Unmaintainable, restricted to SQL s

>Seems a textbook case of using a view for sharing query logic. Views are just terrible outside of data analyst style work. Unmaintainable, restricted to SQL structures, require absurd hacks or custom dlls, not properly source controlled, difficult to perform performance analysis on, hard to update. You can't combine views easily, you can't cache results, you don't get static type checking.

GordonS 7y ago
I don't like to say "you're doing it wrong", but TBH it sounds like you are. > restricted to SQL structures So is your ORM; it just adds a layer of abstraction > require absurd hacks or custom dlls Literally no idea what you're doing that views require anything out of the ordinary, or indeed "custom DLLs" > not properly source controlled Eh? You can store your view DDL files in source control just fine. I mean, they're just SQL files; why couldn't you? > difficult to perform performance analysis on I don't see how ORMs offer any advantage here - if anything, SQL has the edge here, since your typical SQL GUI will draw pretty graphs to help visualise EXPLAIN, and some (e.g. SSMS) will even suggest missing indexes > hard to update It's just SQL, and for views typically rather basic SQL at that. I use ORMs on 99% of the projects I work on, sometimes heavyweights like Entity Framework Core or Marten (which I love, BTW), and sometimes more lightweight like Dapper - but I know when to use the right tool for the job, and sometimes that's SQL views. I just don't get any of your arguments against them.
- strokirk 7y ago
  What SQL GUIs do you use that draws graphs for EXPLAIN statements? The ones I've used for OSX (PopSQL, Sequel Pro, TablePlus) hasn't had that feature. Haven't used MySQL Workbench in a few years, so not sure about that one.
  - sigstoat 7y ago
    pgadmin? https://www.pgadmin.org/screenshots/#9 https://www.pgadmin.org/screenshots/#9
  - GordonS 7y ago
    I mentioned SQL Server Management Studio (SSMS), which is a great GUI for SQL Server. There is also pgAdmin (both 3.x and 4.x have this feature), and it's also available in Azure Data Studio (for SQL Server; it's coming soon for the Postgres version).
dragonwriter 7y ago
> Unmaintainable, Views are not "unmaintainable". > restricted to SQL structures, I'm not sure how that's a problem, since SQL is a fully-general data language. > require absurd hacks or custom dlls, Views require neither hacks nor "custom DLLs" in any RDBMS I am aware of. Can you provide a concrete example? > not properly source controlled, There's no reason DB schemas (including, but not limited to, view definitions) can't be source controlled. > You can't combine views easily Yes you can, whether by "combine" you mean as joined source relations, or as criteria that need to be combined to slice and dice the same source data. That's, like, playing right to the strength of SQL and RDBMS systems. > you can't cache results, Well, not generally incrementally or in something like a recently-used cache; a number of RDBMS's do support materialized views, though.
GFischer 7y ago
At least for MS SQL, code is fully source controllable, I have it on Git and we have all automated deploys and niceties. Views can be a problem from a performance side, Oracle has materialized views but MS SQL lags behind (I think Postgre has them too).

GordonS 7y ago

I don't like to say "you're doing it wrong", but TBH it sounds like you are. > restricted to SQL structures So is your ORM; it just adds a layer of abstraction > require absurd hacks or custom dlls Literally no idea what you're doing that views require anything out of the ordinary, or indeed "custom DLLs" > not properly source controlled Eh? You can store your view DDL files in source control just fine. I mean, they're just SQL files; why couldn't you? > difficult to perform performance analysis on I don't see how ORMs offer any advantage here - if anything, SQL has the edge here, since your typical SQL GUI will draw pretty graphs to help visualise EXPLAIN, and some (e.g. SSMS) will even suggest missing indexes > hard to update It's just SQL, and for views typically rather basic SQL at that. I use ORMs on 99% of the projects I work on, sometimes heavyweights like Entity Framework Core or Marten (which I love, BTW), and sometimes more lightweight like Dapper - but I know when to use the right tool for the job, and sometimes that's SQL views. I just don't get any of your arguments against them.

strokirk 7y ago
What SQL GUIs do you use that draws graphs for EXPLAIN statements? The ones I've used for OSX (PopSQL, Sequel Pro, TablePlus) hasn't had that feature. Haven't used MySQL Workbench in a few years, so not sure about that one.
- sigstoat 7y ago
  pgadmin? https://www.pgadmin.org/screenshots/#9 https://www.pgadmin.org/screenshots/#9
- GordonS 7y ago
  I mentioned SQL Server Management Studio (SSMS), which is a great GUI for SQL Server. There is also pgAdmin (both 3.x and 4.x have this feature), and it's also available in Azure Data Studio (for SQL Server; it's coming soon for the Postgres version).

dragonwriter 7y ago

> Unmaintainable, Views are not "unmaintainable". > restricted to SQL structures, I'm not sure how that's a problem, since SQL is a fully-general data language. > require absurd hacks or custom dlls, Views require neither hacks nor "custom DLLs" in any RDBMS I am aware of. Can you provide a concrete example? > not properly source controlled, There's no reason DB schemas (including, but not limited to, view definitions) can't be source controlled. > You can't combine views easily Yes you can, whether by "combine" you mean as joined source relations, or as criteria that need to be combined to slice and dice the same source data. That's, like, playing right to the strength of SQL and RDBMS systems. > you can't cache results, Well, not generally incrementally or in something like a recently-used cache; a number of RDBMS's do support materialized views, though.

GFischer 7y ago

At least for MS SQL, code is fully source controllable, I have it on Git and we have all automated deploys and niceties. Views can be a problem from a performance side, Oracle has materialized views but MS SQL lags behind (I think Postgre has them too).