Skip to main content
  • EN

Hey guys,

 

I'm still quite new to FME. I've been experimenting with using the DatabaseJoiner and SQLExecutor for my use case to join features already in the workspace to a database table. I have around 2+ million features in a workspace, so it takes quite awhile, and need to join them to an external database with a 1:many relationship (features:database records). Initially I thought that it would be much slower to use a DatabaseJoiner in this case, as it must send many smaller requests to the database, but now I'm not so sure. So I tried using an SQLExecutor to read in all the records and join them to the features with a FeatureMerger. The SQL query is set up so that it is only reading in the records that match one of the keys on the feature already in the workspace. However this has its' own issues I've found where the workspace fails about 2 days in translation, although I'm not sure if the error message is related to the SQLExecutor itself and could be solved by using a DatabaseJoiner instead :

 

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement. Query Text `tno query]'. Provider error `(-2147467259) TCP Provider: An existing connection was forcibly closed by the remote host.'

 

I've found mixed answers online, could anyone point me in the best direction for this task?

 

Thank you everyone

Merging large datasets outside of a database is always going to be slow. You may want to consider first writing your 2+ million features to a temporary database table, index the merge field(s) and do a regular SQL join with the SQLExecutor. I'm not saying it's automatically faster (it depends on a lot of factors), but it might be, in particular if the dataset that is already in the database is very large.

An alternative would be to use the InlineQuerier, which creates a temporary local database for you.

Reply


Community Stats

30,162
Posts
114,512
Replies
37,820
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.