Hi I am writing millions of line features to an various feature classes in an ESRI SDE Geodatabase 10.0. Are there any best practices for improving the performance in writing.
I am writing to a non versioned db, which is being used constantly.
I have increased the transaction to 10000. And Set Transaction type to null.
Is there a way to set the writer in load-only mode for the GEODATABASE_SDE?
Here's some info on that:
https://community.safe.com/s/article/writers-and-performance
and also:
https://community.safe.com/s/article/improving-performance-when-working-with-esri-geoda
You asked about dropping and rebuilding the index? That would definitely help, but unfortunalty there is no easy way to do this in FME without a bit of python. https://pro.arcgis.com/en/pro-app/tool-reference/data-management/remove-spatial-index.htm and https://pro.arcgis.com/en/pro-app/tool-reference/data-management/add-spatial-index.htm
Someone might have a sample to share with you.
The other thing to check it permissions, I have heard that if permissions are not high enough for SED FME might try and start and edit session which is much slower. Check that you have proper permissions.
If it is non-versioned it becomes relatively straightforward to instead Write using the native DB Client, like the SQL Server Spatial Writer (depending on Spatial Data Type being used in the backend). Similarly dropping and re-building the indices can be executed with the relevant SQL commands like DROP INDEX, CREATE INDEX etc. but that involves compromises if you are executing that in a live production environment and is DB dependent.
An alternative way I've found faster for insertions and updates is to send the features in via ArcPy da (Data Access) InsertCursors or UpdateCursors respectively. They are a pain to code up in Python, but it tends to outperform the SDE interface (and related FME Writers) by several orders of magnitude.
If it is non-versioned it becomes relatively straightforward to instead Write using the native DB Client, like the SQL Server Spatial Writer (depending on Spatial Data Type being used in the backend). Similarly dropping and re-building the indices can be executed with the relevant SQL commands like DROP INDEX, CREATE INDEX etc. but that involves compromises if you are executing that in a live production environment and is DB dependent.
An alternative way I've found faster for insertions and updates is to send the features in via ArcPy da (Data Access) InsertCursors or UpdateCursors respectively. They are a pain to code up in Python, but it tends to outperform the SDE interface (and related FME Writers) by several orders of magnitude.
This is really interesting - thanks for sharing.