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?
+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.
ravenkopelman wrote:
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.
ravenkopelman wrote:
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
ggarza wrote:
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
ggarza wrote:
Any idea when the 2018.0 stable release will be out?
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.