Skip to main content
Question

Table truncating takes ages

  • October 10, 2016
  • 8 replies
  • 241 views
M

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

8 replies

itay
Supporter
Forum|alt.badge.img+17
  • itaySupporter
  • Supporter
  • October 10, 2016

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

M
itay wrote:

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.
itay
Supporter
Forum|alt.badge.img+17
  • itaySupporter
  • Supporter
  • October 10, 2016
maverick_mark wrote:

 

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

 

 

itay
Supporter
Forum|alt.badge.img+17
  • itaySupporter
  • Supporter
  • October 10, 2016
maverick_mark wrote:

 

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

 

 

M
itay wrote:

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.

 

 

mark2atsafe
Safer
Forum|alt.badge.img+45
  • mark2atsafeSafer
  • Safer
  • October 11, 2016

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.

M
mark2atsafe wrote:

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!

 

 

M
itay wrote:

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


Helpful Members This Week

Community Stats

31,468
Posts
119,284
Replies
39,083
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.