How and When to use Views

Owned by Kim Stimpson
Last updated: 29 Sept, 2022

Overview

This article tries to describe the scenarios when you could use a View as well as highlighting possible performance issues with using Views over Tables.

When could you use a View

There are various reasons when you would create a View over a Table.

  1. Views can simplify a complex table structure
  2. Views are acceptable when you want to restrict users to a particular subset of data.
  3. You can add/remove or concatenate fields easily in a view without modifying your underlying schema.
  4. Views can model complex joins easily.

Drawbacks

  1. You lose information about relations (primary keys, foreign keys)
  2. A string LIKE comparison in a WHERE clause e.g. LIKE '%land%' is a lot slower than an integer clause. 
  3. It's not obvious whether you will be able to insert/update a view, because the view hides its underlying joins from you.
  4. If you are using the FTP Replicator to replicate the state of a system between two locations, the LOCAL and the REMOTE then database Views are NOT replicated. If you decide to use Views then you will need to manually recreate these on the External Server.


iShare Performance

Scenario: You have a Layer in iShare which is based on a View and the underlying Table from which the View was created has e.g. millions of records when the View only filters a much smaller subset.

Result: This would be extremely inefficient. It would mean that iShare could potentially take a long time to load the Layer based on this View.

Solution: In Studio, create a Workflow Job and then either a Stored Procedure Task using the at_wkf_executecmd Function (from v6.0.0 this is called wkf_executecmd under the -Workflow- Function filter) or a Spatial Data Transformation Task, to create a new Table containing the same data subset as the View. This Workflow Job could then be scheduled to run as often as required to keep the data current. Then change your Layer definition to use the Table instead of the View.

What is a Materialized View

Not currently supported in iShare as iShare uses PostgreSQL 9.0 - will be supported in iShare V6.0.0 which uses PostgreSQL 11.

A materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function. In PostgreSQL, version 9.3 and newer natively support materialized views but they are not auto-refreshed. They are populated only at time of creation (unless WITH NO DATA is used). It may be refreshed later manually using REFRESH MATERIALIZED VIEW. From version 9.4, the refresh may be concurrent with selects on the materialized view if CONCURRENTLY is used.