Skip to main content
  • EN

I have a number of workbenches that update a
SDE. When it gets to Truncating Table it seems to take ages (roughly as long as the write process for each table). In the text from the log below,
you can see it took over an hour to truncate the table. Truncating a table should
be almost instantaneous. No? Does anyone have any idea why it would take such a long time?. I’m running FME Desktop 2016.1. In the previous version of FME (2013) this wasn't an issue.

Regards,

Mark

2016-10-05 09:55:36| 5.9| 0.0|INFORM|ArcSDE release: '10.0'. Underlying
database: 'SQL Server'

2016-10-05 09:55:37| 6.0| 0.1|INFORM|The 'HAS_Z_VALUES' keyword is set
to 'AUTO_DETECT'. With the exception of MultiPatch feature classes, the
dimensionality of new feature classes will be based on the first feature
written to the feature class. (Features with no geometry are considered 2D)

2016-10-05 09:55:37| 6.0| 0.0|INFORM|A default z-value of '0' will be
used for all 3D features where z-values are not provided

2016-10-05 09:55:37| 6.0| 0.0|INFORM|Geodatabase Writer: Not
simplifying geometries being written to the Geodatabase

2016-10-05 09:55:37| 6.0| 0.0|INFORM|Writer mode is 'INSERT'

2016-10-05 09:55:37| 6.0| 0.0|INFORM|Geodatabase Writer: Truncating
table/feature class `Property'

2016-10-05 10:59:17| 113.4|107.4|INFORM|Geodatabase Writer: The field
'OBJECTID' in table 'Property' will not be updated since it is not editable

2016-10-05 10:59:17| 113.4|
0.0|INFORM|Starting an edit session

2016-10-05 10:59:19| 113.6|
0.2|INFORM|Geodatabase Writer: Truncating table/feature class
`Property_All'

2016-10-05 12:17:34| 248.8|135.2|INFORM|Geodatabase Writer: The field
'OBJECTID' in table 'Property_All' will not be updated since it is not editable

Hi, try to set it to a different type of session, might help.

Hi, try to set it to a different type of session, might help.

 

Thanks for the response.

 

 

My current session is set to "Non-Versioned Edit Session". What else would I need to do if I set it to a "Versioned Edit Session" and would I need to be aware of anything if it was set to "Transactions". These options didn't exist when I was writing to a Legacy SDE.

 

Thanks for the response.

 

 

My current session is set to "Non-Versioned Edit Session". What else would I need to do if I set it to a "Versioned Edit Session" and would I need to be aware of anything if it was set to "Transactions". These options didn't exist when I was writing to a Legacy SDE.
This link might shed some more light on the issue:

 

https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/geodatabase/GEODATABASE_SDE_writer.htm

 

 

 

Thanks for the response.

 

 

My current session is set to "Non-Versioned Edit Session". What else would I need to do if I set it to a "Versioned Edit Session" and would I need to be aware of anything if it was set to "Transactions". These options didn't exist when I was writing to a Legacy SDE.
Possibly using the tabel owner to truncate might also be of help:

 

https://knowledge.safe.com/articles/494/why-is-fme-slow-to-truncate-my-sdegeodatabase-tabl.html

 

 

Hi, try to set it to a different type of session, might help.

Thanks for your help. I'll look into the table owner route as detailed in the second link.

 

 

The log file is very interesting, especially this part:

2016-10-05 09:55:37| 6.0| 0.0|INFORM|Geodatabase Writer: Truncating table/feature class `Property'

2016-10-05 10:59:17| 113.4|107.4|INFORM|Geodatabase Writer: The field
'OBJECTID' in table 'Property' will not be updated since it is not
editable

Notice that it took from 9:55:37 to 10:59:17 (3,820 seconds) but FME says it only used 107.4 seconds. From that we can infer that the extra time is on the database side. FME issues the truncate call and then waits an hour for a response.

One suggestion is to drop any indexes on the data before truncating. If you drop/create the table then it is usually quicker because that removes the indexing. So if you need to truncate only, drop the indexes first.

The log file is very interesting, especially this part:

2016-10-05 09:55:37| 6.0| 0.0|INFORM|Geodatabase Writer: Truncating table/feature class `Property'

2016-10-05 10:59:17| 113.4|107.4|INFORM|Geodatabase Writer: The field
'OBJECTID' in table 'Property' will not be updated since it is not
editable

Notice that it took from 9:55:37 to 10:59:17 (3,820 seconds) but FME says it only used 107.4 seconds. From that we can infer that the extra time is on the database side. FME issues the truncate call and then waits an hour for a response.

One suggestion is to drop any indexes on the data before truncating. If you drop/create the table then it is usually quicker because that removes the indexing. So if you need to truncate only, drop the indexes first.

Thanks for your advice. I did some extra reading last night and some others have had the same issue. As mentioned in the first response to my question, I changed the session type from a "Non-versioned Edit session" to "Transaction" and the truncate seems to be instant. Happy days!! I'm currently running a test on a much larger dataset (330K) to see how these setting affect the time it takes to run.

 

The data that is being updated is used in a map service and therefore locked so the process fails if i select "drop table". Another issue with dropping the table is it loses all the attributed permissions, Not ideal.

 

Thanks again for your help and suggestions!

 

 

Hi, try to set it to a different type of session, might help.

By changing the session from "Non-versioned Edit Session" to "Transaction" the truncate time is no longer an issue. Issue resolved. Thanks for the help.

 

 

Reply


Community Stats

30,126
Posts
114,342
Replies
37,757
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.