Solved

Why does the DatabaseJoiner transformer work so much slower in FME 2018.0 vs FME 2017.1? (Bug?)


Badge

I have been developing an FME workflow in version 2017 and have since upgraded it to run in version 2018 but am noticing a vastly slower processing speed when running the DatabaseJoiner transformer in the 2018 version. I can literally count each feature as it gets joined. This used to happen in 5 seconds at the slowest in the 2017 version and in fact I can run the DatabaseJoiner in a workflow identical(data and workflow) to the slow 2018 version and it will run super fast. This may be indicative of a bug in this transformer. Can someone help me find out if this is the case? I used to be able to run this workflow in less than 3 hours but in the 2018 version it takes over 9 hours to finish processing.

icon

Best answer by debbiatsafe 28 June 2018, 00:51

View original

12 replies

Userlevel 2
Badge +17

Hi @krenty

I'm sorry you ran into this issue. This regression in the 2018 DatabaseJoiner has been determined to be intentional by our development team. FME now queries for each incoming feature instead of caching the whole dataset to memory.

As a workaround, please try using a reader and the new FeatureJoiner.

I hope this helps!

Userlevel 4

Hi @krenty

I'm sorry you ran into this issue. This regression in the 2018 DatabaseJoiner has been determined to be intentional by our development team. FME now queries for each incoming feature instead of caching the whole dataset to memory.

As a workaround, please try using a reader and the new FeatureJoiner.

I hope this helps!

That's very interesting, thanks for sharing. Am I correct in assuming that this won't be the case if caching is manually enabled in the FeatureJoiner?
Badge

Hi @krenty

I'm sorry you ran into this issue. This regression in the 2018 DatabaseJoiner has been determined to be intentional by our development team. FME now queries for each incoming feature instead of caching the whole dataset to memory.

As a workaround, please try using a reader and the new FeatureJoiner.

I hope this helps!

This definitely helps but not in the way I was hoping. Thank you for giving me a work around to try but I feel like the regression in the DatabaseJoiner and still keeping it as a transformer is a little silly as it is pretty unusable in its current state. If the FeatureJoiner works much faster I would only need that at this point. Why would one use the DatabaseJoiner now?

 

 

Userlevel 2
Badge +17
This definitely helps but not in the way I was hoping. Thank you for giving me a work around to try but I feel like the regression in the DatabaseJoiner and still keeping it as a transformer is a little silly as it is pretty unusable in its current state. If the FeatureJoiner works much faster I would only need that at this point. Why would one use the DatabaseJoiner now?

 

 

Hi @krenty

 

The DatabaseJoiner may be the better option if a small percentage of the total number of records in that database is to be joined. If all of the database records are to be joined, the FeatureJoiner would be the better transformer to use.

 

Edit: In addition, the DatabaseJoiner is not a blocking transformer while the FeatureJoiner is a blocking one. So if the main stream of features is large, you may want to use the DatabaseJoiner instead.

 

Userlevel 2
Badge +17
That's very interesting, thanks for sharing. Am I correct in assuming that this won't be the case if caching is manually enabled in the FeatureJoiner?
Hi @david_r

 

I believe the DatabaseJoiner queries the external database for each incoming feature instead of caching the external database so the performance of DatabaseJoiner shouldn't improve whether or not Feature Caching is enabled or not.

 

Userlevel 4
Hi @david_r

 

I believe the DatabaseJoiner queries the external database for each incoming feature instead of caching the external database so the performance of DatabaseJoiner shouldn't improve whether or not Feature Caching is enabled or not.

 

Sorry, I should've been more clear. I wasn't thinking about feature caching, but the prefetch query cache inside the DatabaseJoiner.
Userlevel 2
Badge +17

Safe is looking again at this issue and think we may be able to get the performance of DatabaseJoiner back in 2018.1. Thank you @krenty for raising this post!

I will post an update here when the reversion to the older DatabaseJoiner behaviour becomes available in 2018.1 beta builds.

Userlevel 2
Badge +17
The DatabaseJoiner has been reverted back to 2017 behaviour in the newest FME 2018.1 betas (builds 18507 and higher). Users should not see a performance drop when using the DatabaseJoiner.

 

You can download FME 2018.1 here. Thank you for your patience as we worked to resolve this issue.

 

Userlevel 2
Badge +17

Safe is looking again at this issue and think we may be able to get the performance of DatabaseJoiner back in 2018.1. Thank you @krenty for raising this post!

I will post an update here when the reversion to the older DatabaseJoiner behaviour becomes available in 2018.1 beta builds.

The DatabaseJoiner has been reverted back to 2017 behaviour in the newest FME 2018.1 betas (builds 18507 and higher). Users should not see a performance drop when using the DatabaseJoiner.

 

You can download FME 2018.1 here. Thank you for your patience as we worked to resolve this issue.

 

Hi, I just ran into this. I have a ton of workspaces with Joiners in them as you guys actually encouraged us to use it as a faster alternative to Feature Merger. I am very disappointed at having to rewrite all of them, as it is, it is unusable.

Userlevel 2
Badge +17

Hi, I just ran into this. I have a ton of workspaces with Joiners in them as you guys actually encouraged us to use it as a faster alternative to Feature Merger. I am very disappointed at having to rewrite all of them, as it is, it is unusable.

Hi @paperpanther

 

My apologies for the inconvenience. The behaviour of the DatabaseJoiner has been reverted to 2017 behaviour for 2018.1 so you should not see a regression in performance. Would you be able to try FME 2018.1?

 

I had this issue too. Upgrading to the latest version of FME (2018.1.2.0, Build 18590) did not improve performance.

 

But possibly I found the way to improve the performance of DatabaseJoiner.

 

Expanded the Optimize section of DatabaseJoiner

 

Typed Prefetch Query

 

Marked Prefetch Exhaustive to Yes.

 

I hope it will help someone.

Reply