Let the Database Do the Work: Reading

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

  • FME 2017.x

Introduction

There are a number of ways to “let your database do the work”, we’ll take a look at the ways to use FME to perform a more efficient and quicker read from Oracle. By utilizing database capabilities such as views, indexes, SELECT statements, WHERE clauses; we can limit the volume of data (features and attributes) being read to only those we need in our translation.

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

 

Significance

Reading in only the data (attributes and features) you need for your workflow can improve overall performance. By restricting the features being read in from the database with a view, WHERE clause, or SELECT statement, you can streamline the data flow within your workspace thus improving its speed.

The following chart compares the translation times when accessing a smaller subset of features (19,412 features) from the Oracle database in the exercise workspace:

 

database-read-time.jpg

* - with no geometry

 

Reading in all of the data using an Oracle Spatial Reader, with a Tester to filter for COUNTRYCODE = CA takes approximately 300 seconds.

 

With a smaller number of features, the where clause, select statement, and view methods are quite comparable. Using a larger subset of features, the select statement proves to be a faster read since it’s also limiting the number of attributes being read in.

 

Method of Reading Time (seconds) to Read 313,270 Records
Where clause 25.0
Select statement 18.0
View 24.9

 

Note: The above times were recorded from a workspace with only the desired Reader enabled, Run With Full Inspection off, and any connected Inspectors disabled.

 

Exercise

The attached Let the Database Do the Work.fmw demonstrates a number of ways to efficiently read in features from a database. We’ll be using the Countries table in the Oracle database which has over 2 million features. The exercises will emphasize the importance of selectively reading in the features we need: letting the database do the work.

Ensure that you only enable one bookmark at a time, this will help verify the performance differences between each method of reading.

 

Example 1: Table with Where Clause

Notice that by using the Where clause in the Navigator, FME will limit the features read to those that satisfy the condition.

whereclause.jpg

 

Example 2: Table with Select Statement and Where Clause

You can use a SELECT statement instead of a WHERE to reduce the volume of data read by, reducing the number of features read and only reading the attributes of interest. However, if you use a SELECT statement, you are replacing FME’s default SELECT, so if you have a spatial column you need to explicitly include it in the SELECT statement, i.e.:

SELECT GEONAMEID, NAME, ASCIINAME, LATITUDE, LONGITUDE, COUNTRYCODE, TIMEZONE, GEOM from FME.COUNTRIES where COUNTRYCODE='CA' and FEATURECLASS='P'

 

An alternative way to reduce the number of attribute read is to use the Exposed Attributes option, along with a WHERE:

exposedattributes.png

 

Example 3: Reading a View

The Reader in this bookmark has been configured to read a view from the Oracle database. This view has been generated from the Countries base table with a where clause set to COUNTRYCODE = CA and FEATURECLASS = P.

You can also combine a VIEW with a WHERE which gives more flexibility on the data you read.

 

Example 4: Reading Data and Filtering or Thinning with FME

Notice the performance impact when using a where clause (COUNTRYCODE = ‘CA’) and then an AttributeFilter or a Tester to further refine the data to records that have a FEATURECLASS value of ‘P’. Between the two transformers, the AttributeFilter has the better performance.

 

Example 5: SQLCreator and SQLExecutor

Both the SQLCreator and SQLExecutor can be used to query the database. In this bookmark, we are selecting all attributes from Countries based on their COUNTRYCODE and FEATURECLASS. Although both transformers produce the same result, you can simplify your workspace using the SQLCreator to eliminate the need for a Creator/Initiator.

SQLExecutor is useful when other data that has been read is used to initiate the database query

 

Example 6: Reading with and without Indexes

Use the SQLCreators to create a composite index on COUNTRYCODE and FEATURECLASS. In the Feature Type properties, the where clause has been set to use the index created for a more efficient read. This reads in the features faster than the where clause used in example 1.

CREATE INDEX IDX_COUNTRIES ON COUNTRIES (COUNTRYCODE, FEATURECLASS);

 

Example 7: Using the FeatureReader

Use the FeatureReader to perform spatial queries. Creating an attribute or spatial index can make a big difference on the performance of your data read. Most of FME’s database readers will take advantage of a spatial index and carry out MBR intersects. You can use an initiating feature, the Shapefile in this example, to perform a spatial query to limit the incoming data on read.

 

Example 8: Using the Search Envelope

In the Navigator, you can define a Search Envelope to help limit the number of features being read in. Specify the four coordinates and the coordinate system. There is also an option to Clip to Search Envelope. This is faster than reading in all of the data and then clipping by the coordinates in the workspace.

searchenvelope.png

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.