Skip to main content

I have a very basic dynamic workbench that is reading data from SQL Server. Before writing data to a postgis database it is changing an attriubte name with SubstringExtractor. 

But it is extremely slow doing so after almost 3 hours 150 000 objects has been read by the reader. Typically 100 000’s of objects is handled in matters of minutes.

 

It is reading about 25 different feature classes I need to migrate daily.I cant wait hours for the workbench to be complete. 

2024-09-27 15:11:02|6056.7|  0.8|INFORM|Reading source feature # 47000
2024-09-27 15:11:53|6106.9| 50.2|INFORM|Reading source feature # 47500

It takes 50 seconds to read 500 features? 

What is wrong? Why is the process so slow? I cannot understand where the bottle neck is. 
 

Do you have FeatureCaching enabled? Try disable it before production runs.

How long does it take when you only read all features? How long does it take when you read and write without featurecaching? This to narrow down where the bottleneck is.


Are you implementing any restrictions, like a where clause or a spatial filter, in your reader? Is your db indexed correctly? Do you really need to also read geometry in your workspace if you only want to change an attribute name?


Similar comments:  All 3 of those components can have settings to make them slow.  It could be 1 or more combinations of things.

As @nielsgerrits highlights, make sure Feature Caching is turned off.

Then first diagnostic is to disable the FeatureWriter and SubstringExtractor (Ctrl-E toggles) and just run the workspace with the Reader only.  Then the second diagnostic is to run with only the FeatureWriter disabled.  Review the comparative performance and this should pointer to which workspace components are bottlenecking performance.

Note that string manipulation Transfers like SubstringExtractor, AttributeTrimmer, StringCaseChager etc.are all relatively expensive transformers in my experience and do cause noticeable bottlenecks in the workspace although not to the degree that you have indicated.

I would not think it alone would cause the bottleneck indicated, and instead believe the majority is more likely in the Writer or the Reader,  but using SQL Server function SUBSTRING() and getting the server to extract the substring rather than do it in the Workspace can yield some performance gains.

 

 


Reply