SQL: One of the most valuable skills

Re:SQL: One of the most valuable skills by garyclarke27 on Wednesday February 13, 2019 @04:18AM (#19151642)

Views - Create a general top level view, then build more specific views on views using more filters, to go down, ie more granular - then just join these with yet more views to combine, or aggregate to go back up. How is that not composable? If you hit performance issues, they are easily solved by using a few materialized views. Also CTEs and User defined Functions (I use pure SQL functions but in Postgres you can easily use Python or Javascript instead)

[ Parent | on HN ]

Re:SQL: One of the most valuable skills by magicalhippo on Wednesday February 13, 2019 @04:33AM (#19151707)

At least in the DB we're using, Sybase SQLAnywhere, materialized views comes with a hefty price tag. They must be dropped and recreated every time you touch any of the base tables, like adding a column, which in turn requires any indexes on the materialized views to be recreated.

For a few of our customers, that meant that a 15 minute DB change (adding a column) turned into a several hour DB change (rebuilding materialized view).

If they're refreshed immediately, they also incur a penalty on the underlying tables, since any data changes essentially runs a trigger to see if the materialized view needs changes as well.

In our case we had one such immediate refresh materialized view which caused such a performance degradation of an underlying table that we had to find an alternate solution.

[ Parent | on HN ]

Re:SQL: One of the most valuable skills by ryanbrunner on Wednesday February 13, 2019 @09:01AM (#19153104)

Materialized views for sure add some complexity and overhead, but I think that's true of any caching mechanism. They should probably be one of the last tools you reach for when trying to optimize queries, but in certain critical situations they can be super effective.

[ Parent | on HN ]

Re:SQL: One of the most valuable skills by jbverschoor on Wednesday February 13, 2019 @10:46AM (#19153877)

What's the difference between that and a non sql approach?

Writing a program that makes a temporary table somewhere, and if there's a mistake you have to start all over again?

[ Parent | on HN ]

Re:SQL: One of the most valuable skills by magicalhippo on Wednesday February 13, 2019 @06:16PM (#19158102)

Well, for immediate refresh materialized views, how do you keep your non-sql solution up to date?

For manual refresh, I agree, either non-sql or just plain temp tables is a decent alternative. In our case the views do a lot of joins and subqueries, so we've mostly used temp tables.

[ Parent | on HN ]