ArcSDE Writer is very slow, taking 1 hour to write approx 60000 records. How do I improve the speed?

UPDATE will always take the longest. I have try several ways but it really depends on the dataset.

1) Majority of the time, I just truncate records and rewrite the whole table if it's not too much features. (less than a million, but updates are 30k-40k features) But it will mess with the GLOBALID, so be careful if you use this to link with something else .

2) Instead of Update you could add extra logic for it to do DELETE and INSERT that UPDATE records as new too (I find it improve in performance), But it will mess with the GLOBALID, so be careful if you use this to link with something else .

3) Check option on BULK insert in the writer you can pick how many feature you wants it to write before it commit to the database. I forgot what they recommend, there should be in the Help section of the feature writer thou.

There are a lot of useful tips and tricks in this article https://community.safe.com/s/article/improving-performance-when-working-with-esri-geoda

Thanks for your reply @panda​ 

1. It's not feasible as I need to UPDATE some records, not the whole table.
2. I have opted for using fme_db_operation as the Feature Operation. Will see if that improves the performance
3. I could not find any option for BULK INSERT. The writer I am using is 'Esri Geodatabase (ArcSDE Geodb) Writer'

Thanks, @joepk​ .

I had already gone through the article before posting my question here. Most of the suggestions there are already implemented in my workspace (such as increasing transaction interval, transaction type set as TRANSACTIONS, Simplify Geometry and Validate Features set as NO).

I have now setup the Feature operation as 'fme_db_operation' instead of UPDATE. Will see if that improves the performance

Is it versioned? If not, the SQLExecutor might be faster. Deleting records from my SDE on Azure is way faster using a SQLExecutor than a (Feature)Writer. But it is not versioned. Also my FME versions are a bit older than the actual ones, so this might be better now.

Sorry about Bulk Insert, It's actually Features per transaction under Advance Parameter of the writer.

Also, I forgot to ask if your Transaction Type is set to Transaction or Version Edit Session. Transaction will always be the fastest, but it will not work if you dealing with topology, network, or version table is turn on.

If it is a Version Edit Session, you could try to set Features Per Transaction at a higher number it may help a little bit.

If you could also provide a log file, I can help you look through it and try to see what else could culprit.

Transaction type is set as 'Transactions'. I have attached the snippet of the log file showing that the writer is taking about 55 mins to write about 63,000 records. Features Per Transaction was set as 1000. Should I increase that to 10,000? Also, I have changed the operation type to fme_db_operation from UPDATE but haven't run the workspace yet. Will these changes make any impact?

I havn't use fme_db_operation much so I can't tell you, i'm sorry.

But you could try set it to 10000 and check if time to run is reduced, do be mindful that increasing this number will cause the computer to use higher system resources, if it's costing too much, it could end up making your workspace fail as well. Try to find the most optimal number for this is the key. Below is article i found on improving performance from Safe.

Improving Writer Performance

Increase transaction interval

You can speed up translations involving all writers by increasing the interval between committing transactions. Committing transactions is an expensive operation, and therefore it is recommended that you make the transaction interval as big as possible. In speed tests performed at Safe Software, changing the transaction interval from 500 to 1000 resulted in a specific translation being 2.5% faster. Changing the transaction interval to 5000 resulted in the same translation running 5.5% faster. Turning transactions OFF resulted in an improvement of either 12% or 19%. The performance advantages of changing the transaction interval or of turning transactions off will differ between various datasets.

Chunk Sizes and Transaction Intervals

Data written to a database is held in two places. FME holds the data until it has a set amount of data (the Chunk Size). Then it passes the data to the database. The database holds the data until it has a set amount of data (the Transaction Interval). Then it commits the data.

A higher chunk size (also known as Features per Bulk Write) means that FME holds more data, but makes fewer requests to the database. Each request to the database comes at a performance cost, so is best avoided.

A higher transaction interval means that the database holds more data, but has to carry out fewer commit actions. Committing transactions is an expensive operation that it is best to avoid.

At their extreme low ends, smaller chunk sizes and transaction intervals mean you could be passing records to the database and committing them one at a time! This would be extremely costly.

But higher values can use more system resources and run the risk that a failed record means an entire set of data uploads need to be rolled back.

fme_db_operation will allow you to use the same writer to update, insert or delete features, depending on the value of the fme_db_operation attribute. It does not have a lot of impact on performance if the total amount of UPDATE features is the same.

You can try nielsgerrits' aproach if you want to keep your workflow within FME. When I was facing the same issue (4 hours to update 200k features) I skipped FME writers and updated my features through in-house Python/SQL tools.