Skip to main content
  • EN

Hi FME Community

 

We have a dynamic FME workbench which iterates through 89 datasets in an Oracle Database and inserts all records into datasets in a SQL Database. Some of the datasets are large (over 4 million records) and the whole process for truncate & load can take over 24 hours to run. We are looking for ways to improve the speed of this process.

 

We are currently using a feature reader to get the Oracle features, then using a few transformers to clean up the data (i.e. in case there are multiple geometries), then using a feature writer to insert the records into the SQL database.

 

How can we speed up and/or optimize this process? Is there a way to use a SQL Executor to improve the process?

 

image

I think the GeometryFilter doesn't support bulk mode, so the workspace may be reading from oracle in bulk, but unable to write to SQL in bulk, causing it to process features one-by-one. Since you have a Deaggregator, you might be able to use Tester to only pass where @GeometryType() in fme_area,fme_none (if geometries are aggregates, then GeometryType() for a multipolygon would be fme_aggregate). Also check you have Bulk Insert enabled on SQL, since triggers etc on the SQL tables could be slowing the insert.

 

It also may be possible to shift processing work like geometry filtering to the database? either as a where clause on Oracle with something like GET_GTYPE(), or run a SQL statement after write.

@kaz​ Perhaps don't read all the data in one single task. You should be able to use the same workspace but read each of the 89 datasets as a separate job. That's a great automation for FME Server. But if you don't have FME Server you could create a 'parent' workspace that uses WorkspaceRunner to fire of a separate job for each dataset

@kaz​ Perhaps don't read all the data in one single task. You should be able to use the same workspace but read each of the 89 datasets as a separate job. That's a great automation for FME Server. But if you don't have FME Server you could create a 'parent' workspace that uses WorkspaceRunner to fire of a separate job for each dataset

Is there an example workbench for the migration of oracle spatial to sql spatial?

Hello ​@natalie_campos.

Thank you for tuning into this thread! We do have an article that may provide insight to do such a conversion: Converting from Oracle as a first step. Let me know if this helps.

Thanks!

Community Stats

31,951
Posts
121,336
Replies
39,562
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.