span8
span4
span8
span4
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.
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.
© 2019 Safe Software Inc | Legal