span8
span4
span8
span4
Let-the-Database-Do-the-Work.fmw
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 anOracledatabase,the concepts and methods discussed can be applied to other databases such asPostgreSQL/PostGIS,andSQL Server.
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.
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.
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,例如:
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:
例3 -阅读一个视图
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 theSQLCreator and SQLExecutorcan 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.
Working with Foreign Keys: Writing Database Tables
Performing spatial queries on database tables using the FeatureReader
Working with Alternatives or Versions in Smallworld
How do you connect to an Oracle Database with OS authentication (Windows)
Writing custom data types to an Oracle table
Writing to Database Tables that contain Multiple Geometry Columns
Tutorial: Let the Database Do the Work
© 2019 亚搏在线Safe Software Inc |Legal