Question

What is the most efficient way to handle SQL Server table updates in Linux containers?

  • 7 November 2017
  • 6 replies
  • 22 views

Badge

We are attempting to standup FME server in an automated fashion using Docker to spin up Linux containers. Because of the linux containers, our workspaces can only run SQL Server JDBC reader/writers per FME. Plus, in order to spin up the containers quickly, we avoid using any ESRI dependencies. For one of the workspaces, we are using Spatial readers and Non-Spatial writers because we are just updating attributes based on the processing results. The JDBC writers work but are twice as slow as the native SQL Server writers. Do we have any other options for updating the attributes in these SQL Server tables that may give us a performance boost since we don't actually have to write any data?


6 replies

Badge

Hi @ggarza,

Our goal with the SQL Server JDBC format is to match the performance of the "native"/Windows-only format as closely as possible. Significant improvements have been made for the JDBC reader in the 2018.0 release but the writer has received less attention (aside from basic writing, for which we are blocked on Microsoft adding spatial support to their bulk insert driver).

Please consider reporting this to support (refer to PR#80354) to escalate the priority.

In the interim, you *may* be able to work around the slowness by manually constructing your UPDATE statements and sending them using a SqlExecutor transformer. This will be particularly effective if you can send multiple statements in a single request.

Badge

Hi @ggarza,

Our goal with the SQL Server JDBC format is to match the performance of the "native"/Windows-only format as closely as possible. Significant improvements have been made for the JDBC reader in the 2018.0 release but the writer has received less attention (aside from basic writing, for which we are blocked on Microsoft adding spatial support to their bulk insert driver).

Please consider reporting this to support (refer to PR#80354) to escalate the priority.

In the interim, you *may* be able to work around the slowness by manually constructing your UPDATE statements and sending them using a SqlExecutor transformer. This will be particularly effective if you can send multiple statements in a single request.

Thanks for the details about the JDBC updates.

 

I will try the SQL Executor and report back with the results.

 

 

Badge

Hi @ggarza,

Our goal with the SQL Server JDBC format is to match the performance of the "native"/Windows-only format as closely as possible. Significant improvements have been made for the JDBC reader in the 2018.0 release but the writer has received less attention (aside from basic writing, for which we are blocked on Microsoft adding spatial support to their bulk insert driver).

Please consider reporting this to support (refer to PR#80354) to escalate the priority.

In the interim, you *may* be able to work around the slowness by manually constructing your UPDATE statements and sending them using a SqlExecutor transformer. This will be particularly effective if you can send multiple statements in a single request.

Any idea when the 2018.0 stable release will be out?

 

 

Badge

So after looking at this post, I ended up adding an index and using the SQL Executor.

This is a little faster on the updates than the writer, but I don't understand why.

If the SQL Executor is using a JDBC connection like the writer, why would the performance be better?

Badge

So after looking at this post, I ended up adding an index and using the SQL Executor.

This is a little faster on the updates than the writer, but I don't understand why.

If the SQL Executor is using a JDBC connection like the writer, why would the performance be better?

Essentially it is expensive to cross from C++ (FME's core) to Java (SQL Server JDBC format) and sending in completed UPDATE statements requires fewer boundary crossings. As we decrease the cost of those boundary crossings the performance of both solutions will improve, converging near the same point.

 

Badge
Any idea when the 2018.0 stable release will be out?

 

 

Doesn't look like we've committed to a date yet, but typically we aim for the first quarter of the new year.

 

 

Reply