Question

SQL Server update table to replace empty string with null taking forever

  • 21 July 2017
  • 8 replies
  • 30 views
tim_wood
Rank
Badge +7

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.

8 replies

david_r
Rank
Userlevel 4

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.

tim_wood
Rank
Badge +7

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.

 

david_r
Rank
Userlevel 4

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.

tim_wood
Rank
Badge +7

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 :)

 

 

tim_wood
Rank
Badge +7

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.

 

 

tim_wood
Rank
Badge +7

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

 

 

tim_wood
Rank
Badge +7

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! :-)

 

tim_wood
Rank
Badge +7

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

Reply


Community Stats

28,706
Posts
109,463
Replies
36,551
Members
Terms & ConditionsCookie 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.