Hi
I am trying to write to sql server rds on amazon using the jdbc writer, it is really slow. What options do I have to make this any faster. Currently it is inserting 1000 records to a 4 column (all strings) table @20s intervals. using sql server import export wizard is about 50x faster.
Thank you
Hi Oliver, this mostly comes down to the use (or lack thereof) of bulk interfaces for writing data to SQL Server. We have tentatively scheduled this for improvement in FME 2018.1*; if you want your vote formally counted please create a support ticket and reference PR#61819. Regardless I will try to remember to post an update here when the work is complete.
Thank you for your feedback.
* Our previous goal of 2018.0 had to be revised while we wait for Microsoft to upgrade their bulk loading driver to support spatial columns.
Hi Oliver, this mostly comes down to the use (or lack thereof) of bulk interfaces for writing data to SQL Server. We have tentatively scheduled this for improvement in FME 2018.1*; if you want your vote formally counted please create a support ticket and reference PR#61819. Regardless I will try to remember to post an update here when the work is complete.
Thank you for your feedback.
* Our previous goal of 2018.0 had to be revised while we wait for Microsoft to upgrade their bulk loading driver to support spatial columns.
Hi @olivermorris,
we are sorry that for this limitation. As @ravenkopelman already mentioned, improvements regarding this are on our roadmap! I created a support case on your behalf to make sure you'll be kept up to up to date regarding this.
Thanks
Thanks for the feedback, I will await the changes in 2018.
Will look at work arounds for now. Using FME Cloud limits us to JDBC only unfortunately.
Thanks for including my case, it will be great to be updated when you get performance improvments.
Thanks for the feedback, I will await the changes in 2018.
Will look at work arounds for now. Using FME Cloud limits us to JDBC only unfortunately.
Hi Oliver, this mostly comes down to the use (or lack thereof) of bulk interfaces for writing data to SQL Server. We have tentatively scheduled this for improvement in FME 2018.1*; if you want your vote formally counted please create a support ticket and reference PR#61819. Regardless I will try to remember to post an update here when the work is complete.
Thank you for your feedback.
* Our previous goal of 2018.0 had to be revised while we wait for Microsoft to upgrade their bulk loading driver to support spatial columns.
FME 2018.1 contains bulk loading support for SQL Server via JDBC. This dramatically improves writing performance.
Hi Oliver, this mostly comes down to the use (or lack thereof) of bulk interfaces for writing data to SQL Server. We have tentatively scheduled this for improvement in FME 2018.1*; if you want your vote formally counted please create a support ticket and reference PR#61819. Regardless I will try to remember to post an update here when the work is complete.
Thank you for your feedback.
* Our previous goal of 2018.0 had to be revised while we wait for Microsoft to upgrade their bulk loading driver to support spatial columns.
Hi @ravenkopelman. Unfortunately, the JDBC SQL Server Spatial Reader is still slow in FME 2018.1. I am running a simple workbench reading from a SQL Server database into an ESRI File Geodatabase (File GDB Open API Writer). The JDBC reader workbench took 20 min to output 175k records. When I replaced the reader with a normal SQL Server Spatial Reader, it took less than 2 minutes. I know this thread is about the writer, but I wanted to make sure you were aware about the reader as well.
Hi @ravenkopelman. Unfortunately, the JDBC SQL Server Spatial Reader is still slow in FME 2018.1. I am running a simple workbench reading from a SQL Server database into an ESRI File Geodatabase (File GDB Open API Writer). The JDBC reader workbench took 20 min to output 175k records. When I replaced the reader with a normal SQL Server Spatial Reader, it took less than 2 minutes. I know this thread is about the writer, but I wanted to make sure you were aware about the reader as well.
Thank you for the (surprising and disappointing) feedback @ggarza.
Can you elaborate on your situation so that we can do comparable experiments?
If we can reproduce this we will make it faster.
Thank you for the (surprising and disappointing) feedback @ggarza.
Can you elaborate on your situation so that we can do comparable experiments?
If we can reproduce this we will make it faster.
@ravenkopelman, here are the answers to your questions:
Yes, I can elaborate. The workspace reads 4 different feature types from a SQL Server 2016 database, passes the data through an AttributeManager (to set attribute) and TestFilter (to split data stream), and outputs to a FGDB Open API writer with 4 feature types.
1. Besides the geometry spatial type, the other types are varchar, int, float, and datetime2. There are no more than 35 attributes in each table. The varchar fields have lengths less than 15 characters.
2. No, a non-spatial table does not have the same problem. I did some further testing using the same tables with only the geometry column removed, and the same data output in one minute flat. The spatial component seems to be the issue.
3. They are close. They are virtual machines on the same subnet.
@ravenkopelman, here are the answers to your questions:
Yes, I can elaborate. The workspace reads 4 different feature types from a SQL Server 2016 database, passes the data through an AttributeManager (to set attribute) and TestFilter (to split data stream), and outputs to a FGDB Open API writer with 4 feature types.
1. Besides the geometry spatial type, the other types are varchar, int, float, and datetime2. There are no more than 35 attributes in each table. The varchar fields have lengths less than 15 characters.
2. No, a non-spatial table does not have the same problem. I did some further testing using the same tables with only the geometry column removed, and the same data output in one minute flat. The spatial component seems to be the issue.
3. They are close. They are virtual machines on the same subnet.
Thanks for the extra details! We will improve for FME 2019.0, and see if a 2018.x backport makes sense as well.
Hi @ravenkopelman. Unfortunately, the JDBC SQL Server Spatial Reader is still slow in FME 2018.1. I am running a simple workbench reading from a SQL Server database into an ESRI File Geodatabase (File GDB Open API Writer). The JDBC reader workbench took 20 min to output 175k records. When I replaced the reader with a normal SQL Server Spatial Reader, it took less than 2 minutes. I know this thread is about the writer, but I wanted to make sure you were aware about the reader as well.
@ggarza It took longer than expected, but in FME 2019.1, SQL Server (JDBC) Spatial reading is 3x faster than before (when reading single points). I'd be interested in hearing your "real world" results if you get the chance to report back.
Thank you for the feedback!
@ggarza It took longer than expected, but in FME 2019.1, SQL Server (JDBC) Spatial reading is 3x faster than before (when reading single points). I'd be interested in hearing your "real world" results if you get the chance to report back.
Thank you for the feedback!
Hi @ravenkopelman, I tip my hat to you and your team. Sorry for the delay, but I finally got around to testing the reader in FME 2019.2. The JDBC and Native spatial readers are now on par. For 183k records, the workspace ran at 1:25 and 1:30 minutes respectively. They are both well under the 2 minutes from FME 2018.1. And though I upgraded the workspace, I didn't have to upgrade any of the individual components. I call this good!