Can I get better performance in FME when running spatial filter with database sqlserver.

Can you tell us a bit more? A screenshot of your workspace would help, along with an indication of the amount of data being read.

thanks for your reply. please see the attched file

Problem: - when run spatial filter transformer just simple intersection and update field in table based on this filter it takes long time to write data

1000 record in approximately 1 and half minute

See windows resource’s ( CPU, Memory)

Configuration: -

DB Engine :Sql Server 2012 and windows server 2012 . FME 2017.1

hamdyahmed wrote:

thanks for your reply. please see the attched file

Problem: - when run spatial filter transformer just simple intersection and update field in table based on this filter it takes long time to write data

1000 record in approximately 1 and half minute

See windows resource’s ( CPU, Memory)

Configuration: -

DB Engine :Sql Server 2012 and windows server 2012 . FME 2017.1

 

 

It looks like you have 6 separate readers for the SQL Server data, are you using a separate reader for each table? That's probably not necessary, and a hit on performance, so try and replace them all with a single reader.

There's also 2 writers which, again, may not be necessary.

Are the parcels and boundaries in the same projection? If so, you could use a single SQLCreator with a spatial query in it to perform the spatial filtering, that might be a big gain.

1. Upgrade to FME 2019 - this can be installed on same machine, different directory with no problems. https://www.safe.com/support/downloads/

2. Which writer? JDBC or regular?

3. Add Bulk Insert Yes:

4. Are you on wireless or wired network?

hamdyahmed wrote:

thanks for your reply. please see the attched file

Problem: - when run spatial filter transformer just simple intersection and update field in table based on this filter it takes long time to write data

1000 record in approximately 1 and half minute

See windows resource’s ( CPU, Memory)

Configuration: -

DB Engine :Sql Server 2012 and windows server 2012 . FME 2017.1

 

 

I cannot see the specifications of the server in terms of CPU, amount of RAM and disk space etc. , so it is hard to tell. There must be a bottleneck.

It looks like FME Desktop is installed on the same machine as the database server. That is not a best practice. If possible, try to run the same workspace on FME installed on a different machine as the Database Server.

Furthermore, do some research if you could tune your database. Finally, I see that you write "updates" to the database. I don't know if the table you write to has extensive "triggers on update" that could have negative impact on performance. If this is the case, you could try to disable these triggers when FME starts and enable the triggers again at the end. If a trigger is very time-consuming, check to rewrite it, create some more indexes on tables, etc.

@hamdyahmed In the database, you *should* have created an unique key constraint and an index on the 'Match' Column (in your case UUID).

- this server specification :-

process intel xeon cpu E5-2680 v3 @2.50GHz(3 processors)