Tutorial: Let the Database Do the Work

Introduction

Letting the database do the work can drastically improve translation performance when working with databases in FME. This tutorial will look at both reading and writing with databases using FME. The first article focus will be on efficient and quicker reads by minimizing the number of features being read into FME from the database. Reducing the number of features can drastically reduce translation times and improve performance.

Writing to the database can also impact translation performance. Keys and constraints can cause translations to fail and negatively impact overall processes. Writing smartly to your database with FME can improve performance by reducing table loading failures.

Although this tutorial uses an Oracle database, the concepts and methods discussed can be applied to other databases such as PostgreSQL/PostGIS and SQL Server.

Database Terminology

Views

Database views are representations derived from one or more base tables within a database. Views are virtual and created at runtime of the SQL statement. This returns the latest data, as it pulls features from existing tables.

Materialized views consume disk space; therefore, they need to be refreshed to reflect any changes in the master table(s). In general, materialized views have faster reads since they are not refreshed at runtime. Both Oracle and PostgreSQL have materialized views. SQL Server has indexed views.

Index, Composite Index, and Spatial Index

An index can provide faster data access when created on one or more columns in a table. Indices can be dropped or added without affecting the table.

A composite index is created on multiple columns within a table. Composite indexes can be created based on the columns that are most frequently used in querying.

Spatial indexes are created on tables with a spatial column. Spatial Indexes can be used to find objects that interact with the geometry (i.e., point, line, area) of interest.

You need to run this workspace on a machine that can edit an Oracle database to follow along with the 'Let the Database Do the Work' and 'Working with Foreign Keys' exercises.

Articles

Let the Database do the Work: Reading

Compares the performance of reading in records using a variety of methods or transformers: select statements, views, SQLExecutor, indexes, etc.

Working with Foreign Keys: Writing Database Tables

Highlights how to best write to the database when there are constraints on the table, focusing on foreign keys.

Database Tips & Tricks In FME

Common tips and tricks when working with databases in FME.

Writing to an Oracle table with Foreign Keys