Question

Using AttributeManager to update attributes in the Writer from matching records from the Reader. Takes a long time. 6 hours for 86,000 records. Is this normal?


Using AttributeManager to update 86,000 (existing) records using APN for key. Is this process optimized by FME? It takes a long time, and I assume it is N*N time. (Does the Reader iterate through each record, one at a time, then searching through all the records in the Writer to find the matching key?)


12 replies

Userlevel 4
Badge +25

Can you post a screenshot of your workspace? Specifically, how are you doing the matching?

Here is screen shots. Parameters on the Writer:

Feature Operation: Update

Table Handling: Use Existing

Update Spatial Column(s): No

Match Columns: parcel

or

WHERE Clause: "parcel"='@Value(parcel)'

 

imageimage

Userlevel 4
Badge +25

Here is screen shots. Parameters on the Writer:

Feature Operation: Update

Table Handling: Use Existing

Update Spatial Column(s): No

Match Columns: parcel

or

WHERE Clause: "parcel"='@Value(parcel)'

 

imageimage

I suspect that since parcel is unique for every feature you're essentially sending 86000 queries to that geodatabase. Have you tried the match columns option and if so, does that go faster?

I suspect that since parcel is unique for every feature you're essentially sending 86000 queries to that geodatabase. Have you tried the match columns option and if so, does that go faster?

Will try the match column option on file geodatabase, and report back here when done.

I suspect that since parcel is unique for every feature you're essentially sending 86000 queries to that geodatabase. Have you tried the match columns option and if so, does that go faster?

Here is result, using a file geodatabase on a network path, e.g. \\\\server1\\GISData\\FMEData.gdb

 

Translation was SUCCESSFUL with 12 warning(s) (86652 feature(s) output)

FME Session Duration: 6 hours 55 minutes 2 seconds. (CPU: 7387.8s user, 15737.0s system)

END - ProcessID: 25168, peak process memory usage: 206772 kB, current process memory usage: 109080 kB

Translation was SUCCESSFUL

imageimage

I suspect that since parcel is unique for every feature you're essentially sending 86000 queries to that geodatabase. Have you tried the match columns option and if so, does that go faster?

Here are the warnings. Maybe I need to adjust for this? (Feature Caching is ON?)

Feature Caching is ON

The workspace may run slower because features are being recorded on all output ports.

No rows matched UPDATE query to table/feature class 'OwnerInfo_TEST' where parcel = N'231232020'

No rows matched UPDATE query to table/feature class 'OwnerInfo_TEST' where parcel = N'151380055'

No rows matched UPDATE query to table/feature class 'OwnerInfo_TEST' where parcel = N'172390006'

No rows matched UPDATE query to table/feature class 'OwnerInfo_TEST' where parcel = N'215231016'

No rows matched UPDATE query to table/feature class 'OwnerInfo_TEST' where parcel = N'146141078'

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

-~ ~-

-~ Feature caches have been recorded at every stage of the translation. ~-

-~ To inspect the recorded features, ~-

-~ click the feature cache icons next to the ports. ~-

-~ ~-

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Userlevel 4
Badge +25

I suspect that since parcel is unique for every feature you're essentially sending 86000 queries to that geodatabase. Have you tried the match columns option and if so, does that go faster?

Feature caching off will definitely improve performance, but if you have a lot of those warnings I’d check to see if the parcel numbers are correct.

I suspect that since parcel is unique for every feature you're essentially sending 86000 queries to that geodatabase. Have you tried the match columns option and if so, does that go faster?

There were only those five mismatches, and expected. I will be able to try without feature caching later today. Thank you.

Userlevel 4
Badge +25

I suspect that since parcel is unique for every feature you're essentially sending 86000 queries to that geodatabase. Have you tried the match columns option and if so, does that go faster?

Ok, the other thing I noticed is that the system time is very high compared to the cpu time. System time is when FME needs to wait on something, like a database across a slow network. Are you able to copy the geodatabase to your local drive and try that?

Yes. I will use a local Windows drive (using a SSD).

I suspect that since parcel is unique for every feature you're essentially sending 86000 queries to that geodatabase. Have you tried the match columns option and if so, does that go faster?

Here is results: a little less overall time, but still a lot of system time. I'm curious enough to try on C:\\ instead of D:\\ and will post results on Monday. (Those 5 warnings are the expected non-match records).

 

Translation was SUCCESSFUL with 5 warning(s) (86652 feature(s) output)

FME Session Duration: 5 hours 20 minutes 56.8 seconds. (CPU: 7000.0s user, 12215.5s system)

END - ProcessID: 17056, peak process memory usage: 256780 kB, current process memory usage: 109360 kB

Translation was SUCCESSFUL

I suspect that since parcel is unique for every feature you're essentially sending 86000 queries to that geodatabase. Have you tried the match columns option and if so, does that go faster?

Here are more results using local C:\\ drive, slightly slower than D:\\ (but I was also working on other things at same time).

 

Translation was SUCCESSFUL with 5 warning(s) (86652 feature(s) output)

FME Session Duration: 6 hours 8 minutes 29.9 seconds. (CPU: 7508.1s user, 14629.1s system)

END - ProcessID: 19268, peak process memory usage: 256484 kB, current process memory usage: 109056 kB

Translation was SUCCESSFUL

Reply