Question

Migrate Data from Oracle to SQL - How can we speed it up?

K
Badge

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

2 replies

ctredinnick
Rank
Userlevel 3
Badge +16

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.

M
Badge +2

@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

Reply


Community Stats

28,667
Posts
109,350
Replies
36,535
Members
Terms & ConditionsCookie settings
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.