Can I use some other transformer instead of DatabaseDeleter? How about using a SQL Executor?

When you say purged, you mean the table is emptied? If so, have you tried setting the Table Handling on the writer to Truncate Table ?

I would love to. I just wanna make sure that I can TRUNCATE a spatial table without any issues. And if that's possible, I am not sure why the guy before me used a DatabaseDeleter instead of just truncating the table.

Absolutely! I use it all the time.

Ok. Will give it a try and update here. Thanks!

I just tried selecting the 'Truncate Existing' option but there is no improvement in the speed. It is still taking processing 1280 records at a time which leads to it taking 15-20 mins to truncate the whole table.

Can you post your workbench, or maybe a screenshot of your workbench so I can better understand what you are doing? Normally when I use Truncate Existing, I'm not also deleting records...but rather inserting. Using both delete records and truncate seems redundant to me.

Looking back to your original post, you said subsequent workspaces write into the database. It may be a better solution to truncate the tables in those workspaces using a Startup Python script to call the arcpy Truncate Table command. The startup python script will run before any processing is performed in the workspace.

This is the workspace.

One other thought, is your transaction type set to Transactions?

Yes, it is.

So just as a test, can you disable your reader, feed a Creator directly into your writer, then set your writer to Feature Operation to Insert and your Table Handling to Truncate Existing? This will tell us if the actual truncating is taking long, or if it's still going through each of the features and deleting based on GlobalID.

I just tried as you suggested. It completed within 2.8 seconds and didn't delete any records.

There was this warning in the Translation Log:

2022-10-12 07:29:13| 1.5| 0.0|WARN |Esri Geodatabase Writer: An error occurred while trying to truncate table/feature class `DBO.Address'. The error number from ArcObjects is: '-2022-10-12 07:29:13| 1.5| 0.0|WARN |Esri Geodatabase Writer: An error occurred while trying to truncate table/feature class `DBO.Address'. The error number from ArcObjects is: '-2022-10-12 07:29:13| 1.5| 0.0|WARN |Esri Geodatabase Writer: An error occurred while trying to truncate table/feature class `DBO.Address'. The error number from ArcObjects is: '-2022-10-12 07:29:13| 1.5| 0.0|WARN |Esri Geodatabase Writer: An error occurred while trying to truncate table/feature class `DBO.Address'. The error number from ArcObjects is: '-2147216072'. The error message from ArcObjects is: {Underlying DBMS error [[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot TRUNCATE TABLE 'GISStaging.DBO.ADDRESS' because it is being referenced by object 'vFilterAddress'.]}. The error will be ignored

Can you try setting your transaction type back to edit session, if you haven't already? Sorry I don't have a direct answer. Just throwing some thoughts out.

I am trying to use SQLExecutor with the following statement:

DELETE FROM dbo.[ADDRESS]

But, I am getting the following error:

Microsoft SQL Server Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `DELETE FROM dbo.[ADDRESS]'. Provider error `(-2147217871) Query timeout expired'

What is wrong with the query?

@ravibansal​ You are using Feature Operation: Delete. I don't think this will cause FME to truncate the table. Use Truncate when you load the new data with Insert. The Delete is slow as each record is deleted one at a time (faster if the match column is indexed) . I would suggest you do not need this Delete workspace. Just use Truncate when you start to load the STAGINGADDRESS feature class.

To me it looks like the database won't let you truncate the table because of foreign key constraints in "vFilterAddress".

If you're unable to improve the performances in FME, you may want to involve your DBA to see if they have some tips that are specific to your database schema.

Truncate is an operation which wipes a table, resets the primary key, so it's always faster than deleting row-by-row to clear a table. It doesn't sound like your process needs this to be a separate workspace as well - the subsequent workspaces could be set to insert new records to the staging table as well as to truncate it before write, and you'd get the same result.

The only reason for the previous setup would be if each agency's records needed to be deleted from the staging table separately. You can achieve that by passing a single feature into a writer, carrying a where clause which matches every address from an agency, with the writer set to delete. So one feature triggers the delete of thousands. Deleting based on ObjectID is very safe, but also very slow.