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 have an effect on translation performance. Keys and constraints can cause translations to fail and impact processes overall. 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 a base table or multiple tables within the database. Views are virtual and created at the runtime of the SQL statement. This returns the latest data since it is pulling the features from existing tables.

Materialized views are saved using disk space: these need to be refreshed to reflect any changes in the master table(s). In general, materialized views have faster reads since the view is not refreshed at run. 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 and will not affect 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 geometry (i.e.- point, line, area) of interest.

You need to run this workspace on a machine that can edit an Oracle database to be able 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.

Making use of Oracle Sequences when working with Oracle Tables

Writing to an Oracle table with Foreign Keys