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.
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.
Thanks @david_r
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.
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.
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 like the sound of that :)
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.
Schema (any format) expects a file or URL. Can I get it to read the schema of an SQL Server table?
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.
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.
I had an XLSX that included the field names so I copied them out of that into a CSV and used that as the source for my SQLExecutor query. I always need to remember to increase the query timeout...!
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.
4 mins 12.1 seconds. Get in! :-)
