Question

Resources on writing features to databases

  • 4 October 2018
  • 3 replies
  • 1 view
K
Badge

Hi FMEer's!

Can someone kindly point me to tutorials/webinars of where the workbench is writing particularly large amounts of data to spatial and non-spatial databases? I have many models that write to Oracle databases, SQL Server, ArcSDE etc. However, I find that the moment we start writing 100's of thousands of records it is quite slow. It's generally quicker to import a table into one of the databases and do the truncate and load through a database management software.

Some of the methods I use in FME are:

 

- Change detection and use FME_DB_OPERATION = Insert or Delete (Not sure really how to use the Update)

- Truncate table and insert (Use this when the table is massive and change detection is just not feasible)

- Write to ESRI file geodatabase first and then copy to ArcSDE

Any help is much appreciated, I just want to make my models more efficient!

Kind regards,

Kam

3 replies

T
Rank
Badge +6

Hello @kam,

Some resources covering the topics of databases can be found at the following links:

 

 

SQL Server: https://knowledge.safe.com/articles/32420/tutorial-getting-started-with-microsoft-sql-server.html

 

Updating Databases: https://knowledge.safe.com/articles/34351/updating-databases.html

 

Mastering Spatial Databases(Oracle): https://knowledge.safe.com/articles/44492/mastering-spatial-databases.html

 

Extracting differences from an ArcSDE Geodatabase: https://knowledge.safe.com/articles/30729/tutorial-extracting-differences-from-an-arcsde-geo.html

 

 

Those are just some of the articles currently posted on the Knowledge Centre. You may also find additional help from existing questions that have been asked and answered by the public. If you have a specific question as well, you can post it. FME users are very helpful in providing suggestions and tips on how you might be able to accomplish a specific workflow!

 

 

I hope that helps!

ravenkopelman
Rank
Badge
In general I'd expect pure insert mode to be much faster than update/deletes, but one concrete suggestion is to make sure you have an index on whatever column(s) you are keying your updates and deletes on.

 

 

mark2atsafe
Rank
Userlevel 4
Badge +25
Hi @kam Coincidentally I'm just starting on a short article about making database updates. So keep an eye out as it'll be online in the next few days.

 

Reply


Community Stats

28,724
Posts
109,518
Replies
36,563
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.