span8
span4
让-the-database-do-the-work.fmw
There are a number of ways to "let your database do the work",我们将看看如何使用FME执行更高效和更快的Oracle读取。By utilizing database capabilities such as views,indexes,SELECT statements,凡条款;我们可以将要读取的数据量(特性和属性)限制为翻译中需要的数据量。
Although this exercise uses anOracledatabase,the concepts and methods discussed can be applied to other databases such as波斯特雷斯尔/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子句,or SELECT statement,you can streamline the data flow within your workspace thus improving its speed.
下表比较了从“练习”工作区中的Oracle数据库访问较小的功能子集(19412个功能)时的转换时间:
*-没有几何图形
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,WHERE条款,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.
阅读方法 | Time (seconds) to Read 313,270 Records |
何处条款 | 二十五 |
Select statement | 十八 |
视图 | 二十四点九 |
Note:The above times were recorded from a workspace with only the desired Reader enabled,Run With Full Inspection off,和任何相关的检查人员都是残疾人。
所附的让数据库完成这项工作。fmw演示了从数据库高效地读取特性的多种方法。我们将使用Oracle数据库中的Countries表,它有超过200万个功能。这些练习将强调有选择地阅读我们需要的特性的重要性:让数据库完成工作。
确保一次只启用一个书签,这将有助于验证每个读取方法之间的性能差异。
Example 1 - Table with Where Clause
注意,通过在导航器中使用WHERE子句,FME将把读取的特性限制为满足条件的特性。
示例2-带有SELECT语句和WHERE子句的表
You can use a SELECT statement instead of a WHERE to reduce the volume of data read by,减少读取的功能的数量,只读取感兴趣的属性。然而,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,时区,来自国家代码为'ca'且功能类为'p'的fme.countries的geom
另一种减少属性读取次数的方法是使用暴露的属性选项,along with a WHERE:
示例3-读取视图
The Reader in this bookmark has been configured to read a view from the Oracle database.此视图是从国家/地区基表生成的,其中WHERE子句设置为COUNTRYCODE=CA,FEATURECLASS=P。
您还可以将视图与where结合起来,这样可以更灵活地读取数据。
例4——读取数据并用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.
示例5-sqlCreator和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非常有用。
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.大多数FME的数据库阅读器将利用空间索引进行MBR交叉。You can use an initiating feature,本例中的shapefile,执行空间查询以限制读取时传入的数据。
示例8-使用搜索信封
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.还有一个选项可以剪辑来搜索信封。这比读取所有数据,然后根据工作区中的坐标进行剪切要快。
© 2019 亚搏在线Safe Software Inc |Legal