SQL Server update table to replace empty string with null taking forever
Hello.
I'm using FME 2017.0.1.1 (17291) 64-bit on Windows Server 2008 connecting to SQL Server 2012 on Windows Server 2012.
I'm reading an SQL Server table of 3.7 million records with about 80 fields and writing back to it using fme_db_operation to update about 70 of those fields after using NullAttributeMapper to replace empty strings with null. It's been going for 16 hours and has not even done 1 million records.
Is this the sort of timings I should be expecting or is there something I can do to improve things?
Features per transaction on the Writer is 500.
First of all, verify that there's an index in the database on the update key field used on the writer. This will have a huge impact on a large table.
On the other hand this sounds like a typical example of where FME with its reader/writer paradigm might not be the best tool. The problem is that FME will have to extract all the 3.7 million records, process them and then write them back into the database, which will be slow no matter what.
If you only need to convert empty string fields to null, it will be a LOT faster to do it inside the database with some SQL, either using a SQLExecutor in FME or from e.g. MS SQL Studio:
update my_table_name set my_field_name = null where my_field_name = ''
Or something like that.
There is an index on the update key field.
I would have done it with SQL in Management Studio but there are 70 fields to update so an FME Workspace is a bit quicker to build (for me anyway). However it looks like overall the time spent constructing the SQL query (e.g. in Excel then copying and pasting out) might be quicker than an FME Workspace.
Interesting to compare the strengths of the SQLExecutor and Writer for different tasks. See:
https://knowledge.safe.com/questions/48463/sqlexecutor-how-to-update-multiple-sql-server-colu.html
It's fairly easy to generate SQL in FME and then pass it to e.g. the SQLExecutor. You could e.g. use the Schema (any format) reader to get all the field names in your table and then generate one UPDATE query per field, which is then executed by the SQLExecutor.
I can work round this by reading 1 feature from the table using the SQL Server Non-Spatial Reader and exposing fme_feature_type, then using that in a FeatureReader to do the Schema (any format) bit. However, when I inspect the results, I just get back the first row of the table with some extra columns.
