A view is a saved query that pretends it’s a table. It doesn’t actually store any data. So if you need to query 10 different tables, joining them together and filtering the results specific ways, a view would just be that saved query, so instead of "SELECT * FROM " you can do “SELECT * FROM HandyView”
Comment on I'll just be a quick 3h
hddsx@lemmy.ca 7 months agoSo my work is archaic and doesn’t even use SQL. What are views?
calabast@lemm.ee 7 months ago
Restaldt@lemm.ee 7 months ago
Predefined queries more or less
doctordevice@lemm.ee 7 months ago
Basically scripts you can run on the fly to pull calculated data. You can (mostly) treat them like tables themselves if you create them on the server.
So if you have repeat requests, you can save the view with maybe some broader parameters and then just SELECT * FROM [View_Schema.My_View] WHERE [Year] = 2023 or whatever.
It can really slow things down if your views start calling other views in since they’re not actually tables. If you’ve got a view that you find you want to be calling in a lot of other views, you can try to extract as much of it as you can that isn’t updated live into a calculated table that’s updated by a stored procedure. Then set the store procedure to run at a frequency that best captures the changes (usually daily). It can make a huge difference in runtime at the cost of storage space.
dan@upvote.au 7 months ago
They can be in some cases! There’s a type of view called an “indexed” or “materialized” view where the view data is stored on disk like a regular table. It’s automatically recomputed whenever the source tables change. Doesn’t work well for tables that are very frequently updated, though.
Having said that, if you’re doing a lot of data aggregation (especially if it’s a sproc that runs daily), you’d probably want to set up a separate OLAP database so that large analytical queries don’t slow down transactional queries. With open-source technologies, this is usually using Hive and Presto or Spark combined with Apache Airflow.