Solved

Performance disparity of Oracle Non-Spatial Reader on high-latency network

P
Badge

New to FME and befuddled by the performance disparity of Oracle Non-Spatial reader over high-latency network links. Needless to say, I understand the benefits of having FME close to the data, but…

 

I compared the performance of the Oracle Non-Spatial reader against a CSV export from Oracle SQL Developer of the same database view on various machines in various locations on our network. The size of the fetch was 46394 database rows with just 8 columns.

 

From a desktop machine in the data centre close to the source Geodatabase:

SQL Developer CSV Export:             8m16s

FME Desktop Reader:                       10m11s

 

From a remote desktop machine on VPN:

SQL Developer CSV Export:             8m50s

FME Desktop Reader:                       72m

 

Remote FME Server:

FME Server Reader:                           32m9s

 

The most interesting fact is that the SQL Developer export suffered almost no performance impact even when it was executed over a high latency network link whereas the FME reader was devastated.

 

Here is a Wireshark capture of the network packets of the remote desktop machine over VPN:

When running a CSV export with SQL Developer, the database server sends large 1360 byte payloads back to back and the client machine only needs to acknowledge occasionally.

Wireshark SQL Developer 

The equivalent fetch from FME Desktop seems to be endlessly handshaking with the database server using small 172-175 byte packet payloads and only occasionally receives a large 1360 byte data payload:

Wireshark FME ReaderAnyone else more familiar with FME Oracle readers and SQL*Net experience this kind of network behavior? What data transfer optimization is SQL Developer taking advantage of that FME Reader isn't?

 

Oracle SQL Developer 18.1.0.095

Oracle SQL*Net 18

Oracle Database 12.1.0.2

FME Desktop 2019.0.2/Win10

FME Server 2019.0.2/Win Server 2019

icon

Best answer by paulot 18 June 2021, 02:02

View original

4 replies

P
Badge

Actually, I think I've tracked it down to the conversion of the ST_GEOMETRY field in the database using SDE.ST_ASBINARY(OHLINE.SHAPE) AS SHAPE_WKB in the query's select statement. This creates a BLOB column. I also tried ST_ASTEXT, which creates a CLOB column and still had slow performance. When I remove the field from the query, the FME reader is as fast as my CSV Exports with SQL Developer.

 

As I understand it, FME cannot read ST_GEOMETRY and this is the standard way to extract the shape field?

ebygomm
Rank
Userlevel 1
Badge +21

When you export as csv from SQL developer you just get the word (BLOB) don't you? So you're not actually getting the geometry at all

P
Badge

When you export as csv from SQL developer you just get the word (BLOB) don't you? So you're not actually getting the geometry at all

Yes, you're right. The CSV export from SQL Developer doesn't read the BLOB which is another confirmation that the BLOB is the issue.

P
Badge

My solution was to put my query into a view and switch to the ESRI Geodatabase reader. Just because I'm new, I hadn't seen that reader before, but of course it handles all the ST_Geometry work without a BLOB apparently. :)

Reply


Community Stats

28,747
Posts
109,595
Replies
36,587
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.