Let the Database Do the Work: Reading

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:

* - 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.

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.

Examples

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.

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:

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

AI Assist

New to FME 2023.1, the AI Assist (Tech Preview) functionality is available in the SQL Statement Editor to help new and experienced users build out SQL Statements. Schema information from your database can also be sent to the AI service to return a result containing the correct table and column names.

How to use the AI Assist with SQL
In FME 2023.1 and greater, you can access the AI Assist through the SQL Statement Editor. It will appear as a button in the bottom left corner of the window.

Using the sample screenshot below, we will provide an overview of each of the components of the AI Assist window. 

1.  SQL Query Description
This is where you can specify your request in plain English. For example:
"Find all the countries that have a population greater than 100000"

This would return a SQL statement such as: 

SELECT * FROM “countries” WHERE “population” > 100000

Send database schema to AI
When enabled, FME scans your database schema to provide a more accurate result from the AI Assist. The resulting SQL will be tailored to the tables and columns that exist in your database, instead of providing generic naming. 

Let's use the following example request: 
"Find all the countries that have a population greater than 100000"

With Send database schema to AI enabled, the AI will try to match the SQL query with the tables and columns in your database.

The AI returns the following SQL statement:

SELECT * FROM "public"."Countries" 
WHERE "population" > 100000

Tip: The Send database schema to AI parameter can prolong the time it takes to return a SQL query from the AI Assist. This is because FME must parse through all the database table and column names and send it to the AI. The amount of tables and columns in each table will dictate how long it takes FME to scan your database schema.

2. Status
Provides an overview of the status of the AI request and whether it succeeded or not.

3. AI Results
The results from the description after it has been run through the AI data model. This will include a functional SQL query to use in the SQL editor. If Send database schema to AI is enabled, then the query may include accurate table and column names.

4. Explanation
To help users understand the AI results, each object within the SQL query is broken down with an explanation. 

The AI Assist is still in Tech Preview and is subject to change in future FME versions.
For legal information regarding the AI Assist, please see our AI Terms of Use.
If you have any feedback regarding the use of the AI Assist in the SQL Editor, please leave a comment on this article or contact us at info@safe.com.

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.