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?
Question
What is the most efficient way to handle SQL Server table updates in Linux containers?
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+1- Safer
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.
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.
I will try the SQL Executor and report back with the results.
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.
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?
+1- Safer
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?
+1- Safer
Any idea when the 2018.0 stable release will be out?