SQL Deep Dive: Looking into Views

Greetings, Illuminate geeks!

In another post, I explained how to delve into back-end Custom Report data to see how Illuminate builds SQL queries about the things you care about. In this post, I’ll describe another trove of expert-made SQL: views. Views are queries that are saved as permanent objects in a relational database. Views can be used in SQL SELECT queries just as you would use native tables.

A good example is public.term_info. Some rather important tables anchor a thing in time by affiliating it with a scheduling term: sections and grading_periods, for example. There’s also the old-style school enrollment list in public.student_term_aff (which is now itself a view that keeps many an old query non-broken even after the table was replaced with public.student_session_aff). To get the site name for a section using native tables you have to

JOIN public.terms USING (term_id) JOIN public.sessions USING (session_id) JOIN public.sites USING (site_id)

The term_info view combines essential information from public.terms, public.sessions, and public.sites so that you can just

JOIN public.term_info USING (term_id)

SchemaSpy provides checkboxes on schema pages that will show or hide tables or views in the list. But it won’t show the SQL statement that constitutes the view. For that, you can browse the object tree in pgAdmin or a similar database administration utility and look at the properties of the view. Or you can query the data from the database. Most relational database systems have tables, views and/or functions that provide metadata about the structure of the database, and PostgreSQL is no exception. This query will return all the views in your Illuminate database:

SELECT *
FROM
pg_views PGV
ORDER BY
  CASE WHEN PGV.SchemaName LIKE 'pg_%' OR PGV.SchemaName LIKE 'information_schema' THEN 1 ELSE 0 END
, PGV.SchemaName
, PGV.ViewOwner
, PGV.ViewName

Why not run that against your database and see for yourself how public.student_common_demographics calculates whether the SPED flag should be displayed for a given student? Or check out the interrelationships between the views in the public schema whose names start with student_grades, and how they combine data from public.student_grades, public.outer_student_grades, and rosters (and related tables) into a consistent list of grades for transcript pages, printed transcripts, and grades for Custom Reporting. Or check out the differences in data selection logic between public.section_dates versus public.section_dates2.

Using Views in Your Queries

While it can certainly be convenient to use views in your own queries, in some cases an equivalent query using the individual native tables can execute faster than one that joins a view to other data. In my experience, the difference in performance is usually negligible. Nevertheless, if you’re encountering performance problems with a query that joins a view, consider whether it’s worth your while to redesign it to use native tables instead.

If you’re building process-critical SQL queries that invoke views, be advised that the permissions on views can break. It’s rare, but occasionally I find that my ODBC account loses access permission that it had to one or more views. I think what’s happening is that the system updates a view using a script that performs the same update on scores of databases, and that script is unaware of the database-specific user accounts that afford us our ODBC access. So presumably the view gets deleted and recreated with vanilla permissions. In such a case, the help desk is characteristically prompt about fixing the omission, but your SQL query may be broken until the database permissions are restored. You can still see the definition of the view, so in a pinch you can replace the name of the view where it’s invoked in your query with a sub-select that encapsulates the view’s definition under whatever alias it goes by in your query.

Casey Mirch
Leadership Public Schools