Skip to main content
Solved

Database block size in Database Reader vs SQLExecutor

helmoet
Forum|alt.badge.img+8

How to compare a series of tables across two databases? We use a setup that reads a .csv file that has two columns:

- Column 1 is a query in database 1, and

- Column 2 is a query in database 2

Both queries should return the same and ordered list of values (basically, a list of identifiers) and are fed into an SQLExecutor. Both resulting streams are compared using a FeatureMerger, and the UnmergedRequestor and UnUsedSupplier ports report the features that are not present in both databases. This works nice for relatively small tables. If a table is queried that has more than a million records, performance drops. I solved this performance issue by using database readers. I specified the Number of Records To Fetch At A Time (ORACLE) and the Bulk Read Size (MSSQL) and set them so that 100.000 records are fetched each time. I could work this out using a WorkspaceRunner, however my question is: Where are these parameters on the SQLCreator/SQLExecutor transformers?

Parameters available on the SQLExecutor:

 

Parameters available on the MSSQL Reader:

 

 

Parameters available on the Oracle Reader:

 

Any anwer would be greatly appreciated, also any comments on the way to solve this "database comparison" project.

Best answer by david_r

So I don't have access to an SQL Server instance just now, but I ran some quick tests using Oracle. Reading about 1.6 million non-spatial records, I was not able to detect a more than a slight difference between the SQLExecutor and the "classic" reader, even when setting the bulk read size to either 200, 5000 or 100000.

Best time with "classic" reader and bulk read size 5000: 57 seconds

Best time with SQLExecutor: 1 minute 10 seconds

So a small difference, but no game changer.

I'd love to know what your numbers would be @helmoet, between the SQLExecutor and the reader. Maybe there is something SQL Server-specific causing a performance issue?

View original
Did this help you find an answer to your question?

10 replies

M
Forum|alt.badge.img+2
  • markatsafe
  • October 21, 2019

@helmoet First I'd try replacing your original FeatureMerger with FeatureJoiner in your original workflow. That should be more performant, but will use more cache so make sure you have your FME_TEMP pointing to a SSD drive.

You might also try Suppliers First on the FeatureMerger if you prefer that. Just make sure the reader that supplies the Suppliers is the first one in the navigator. Then you'll only be cacheing the supplier records instead of caching both Requestor and Supplier.

You might also try the JDBC based reader for your SQL Server database reader.

If you wish to use SQLExecutor/Creator: I'm not familiar with the underlying SQL for Bulk Read, but that is likely something you'll have to enable in PL/SQl or equivalent, for example Bulk Collect. Perhaps one of our resident database experts might be able to help on that front @david_r

helmoet
Forum|alt.badge.img+8
  • helmoet
  • Author
  • October 22, 2019
markatsafe wrote:

@helmoet First I'd try replacing your original FeatureMerger with FeatureJoiner in your original workflow. That should be more performant, but will use more cache so make sure you have your FME_TEMP pointing to a SSD drive.

You might also try Suppliers First on the FeatureMerger if you prefer that. Just make sure the reader that supplies the Suppliers is the first one in the navigator. Then you'll only be cacheing the supplier records instead of caching both Requestor and Supplier.

You might also try the JDBC based reader for your SQL Server database reader.

If you wish to use SQLExecutor/Creator: I'm not familiar with the underlying SQL for Bulk Read, but that is likely something you'll have to enable in PL/SQl or equivalent, for example Bulk Collect. Perhaps one of our resident database experts might be able to help on that front @david_r

Ok, thanks Mark. My observation is that both databases perform considerably better when using traditional readers with large chunk sizes than when using SQLExecutors that lack the option to set a chunk size. Even if there are no blocking transformers further on the stream. Thanks for pointing @david_r in my direction, and the interesting article on Bulk Collect. Also I'll investigate the DatabaseJoiner.

david_r
Evangelist
  • david_rEvangelist
  • Best Answer
  • October 22, 2019

So I don't have access to an SQL Server instance just now, but I ran some quick tests using Oracle. Reading about 1.6 million non-spatial records, I was not able to detect a more than a slight difference between the SQLExecutor and the "classic" reader, even when setting the bulk read size to either 200, 5000 or 100000.

Best time with "classic" reader and bulk read size 5000: 57 seconds

Best time with SQLExecutor: 1 minute 10 seconds

So a small difference, but no game changer.

I'd love to know what your numbers would be @helmoet, between the SQLExecutor and the reader. Maybe there is something SQL Server-specific causing a performance issue?

helmoet
Forum|alt.badge.img+8
  • helmoet
  • Author
  • October 22, 2019
david_r wrote:

So I don't have access to an SQL Server instance just now, but I ran some quick tests using Oracle. Reading about 1.6 million non-spatial records, I was not able to detect a more than a slight difference between the SQLExecutor and the "classic" reader, even when setting the bulk read size to either 200, 5000 or 100000.

Best time with "classic" reader and bulk read size 5000: 57 seconds

Best time with SQLExecutor: 1 minute 10 seconds

So a small difference, but no game changer.

I'd love to know what your numbers would be @helmoet, between the SQLExecutor and the reader. Maybe there is something SQL Server-specific causing a performance issue?

Hi @david_r I'll run some tests.

helmoet
Forum|alt.badge.img+8
  • helmoet
  • Author
  • October 22, 2019

Ashamed....

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using Oracle Reader (default Bulk Read Size)          X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
DUAL                                                                   2753901
==============================================================================
Total Features Read                                                    2753901
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Translation was SUCCESSFUL with 2 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 5.4 seconds. (CPU: 87.1s user, 18.2s system)
END - ProcessID: 7676, peak process memory usage: 386108 kB, current process memory usage: 326028 kB
Translation was SUCCESSFUL

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using SQLExecutor                                     X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Read                                                          0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Translation was SUCCESSFUL with 1 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 24.9 seconds. (CPU: 116.7s user, 20.1s system)
END - ProcessID: 8456, peak process memory usage: 392392 kB, current process memory usage: 329276 kB
Translation was SUCCESSFUL

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using MSSQLReader (default Bulk Read Size)            X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
GeoAsset.GEBIED_H                                                      2753901
==============================================================================
Total Features Read                                                    2753901
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Microsoft SQL Server Non-Spatial Reader: Closing `EGDB' . Read operation complete
Translation was SUCCESSFUL with 14 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 17.7 seconds. (CPU: 116.4s user, 20.7s system)
END - ProcessID: 4340, peak process memory usage: 376720 kB, current process memory usage: 316692 kB
Translation was SUCCESSFUL



XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using SQLExecutor                                     X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Read                                                          0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Translation was SUCCESSFUL with 1 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 43.0 seconds. (CPU: 139.2s user, 21.2s system)
END - ProcessID: 9156, peak process memory usage: 381292 kB, current process memory usage: 321292 kB
Translation was SUCCESSFUL

 

I just checked the Oracle version too:

  • Same query,
  • First using Oracle Reader with no Bulk Read Size
  • Just have the results run into Inspector transformer.
  • Second Creator/SQLExecutor/Inspector.
  • In Oracle reader, Featuretype is DUAL, then using Select Statement to set query.
  • in MSSQL reader, Featuretype is just another (small) table, then also Select Statement to set query.

Results: not a very big difference. I'll rethink my setup. Thanks for helping me out @markatsafe, @david_r.

 

david_r
Evangelist
  • david_rEvangelist
  • October 22, 2019
helmoet wrote:

Hi @david_r I'll run some tests.

One interesting detail: how many input features does your SQLExecutors have? In my experience the SQLExecutors are in fact rather slow when having to execute thousands of queries (=input features), since the database server has to parse the SQL statement from scratch for each input feature, leading to a lot of overhead.

Bind variables would have resolved this issue but are currently not supported in the SQLExecutor, see also this old idea that unfortunately isn't getting as much love from Safe as from the users ;-)

https://knowledge.safe.com/idea/23829/sqlexecutor-support-for-bind-variables.html

david_r
Evangelist
  • david_rEvangelist
  • October 22, 2019
helmoet wrote:

Ashamed....

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using Oracle Reader (default Bulk Read Size)          X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
DUAL                                                                   2753901
==============================================================================
Total Features Read                                                    2753901
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Translation was SUCCESSFUL with 2 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 5.4 seconds. (CPU: 87.1s user, 18.2s system)
END - ProcessID: 7676, peak process memory usage: 386108 kB, current process memory usage: 326028 kB
Translation was SUCCESSFUL

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using SQLExecutor                                     X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Read                                                          0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Translation was SUCCESSFUL with 1 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 24.9 seconds. (CPU: 116.7s user, 20.1s system)
END - ProcessID: 8456, peak process memory usage: 392392 kB, current process memory usage: 329276 kB
Translation was SUCCESSFUL

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using MSSQLReader (default Bulk Read Size)            X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
GeoAsset.GEBIED_H                                                      2753901
==============================================================================
Total Features Read                                                    2753901
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Microsoft SQL Server Non-Spatial Reader: Closing `EGDB' . Read operation complete
Translation was SUCCESSFUL with 14 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 17.7 seconds. (CPU: 116.4s user, 20.7s system)
END - ProcessID: 4340, peak process memory usage: 376720 kB, current process memory usage: 316692 kB
Translation was SUCCESSFUL



XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using SQLExecutor                                     X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Read                                                          0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Translation was SUCCESSFUL with 1 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 43.0 seconds. (CPU: 139.2s user, 21.2s system)
END - ProcessID: 9156, peak process memory usage: 381292 kB, current process memory usage: 321292 kB
Translation was SUCCESSFUL

 

I just checked the Oracle version too:

  • Same query,
  • First using Oracle Reader with no Bulk Read Size
  • Just have the results run into Inspector transformer.
  • Second Creator/SQLExecutor/Inspector.
  • In Oracle reader, Featuretype is DUAL, then using Select Statement to set query.
  • in MSSQL reader, Featuretype is just another (small) table, then also Select Statement to set query.

Results: not a very big difference. I'll rethink my setup. Thanks for helping me out @markatsafe, @david_r.

 

No shame :-) Thanks for sharing your findings!

helmoet
Forum|alt.badge.img+8
  • helmoet
  • Author
  • October 23, 2019

After all, people were only interested IF there were differences. So I left the work to the database and performed only a counts query, which runs only on the database and completes within one minute (counting ~30,000,000 records). On the suggestion involving the FeatureJoiner (or, as it is called in our FME version 2017.1, DatabaseJoiner) that would be good, however the queries are read from .csv and the DatabaseJoiner does not accept an attribute for the prefetch query.

K
Forum|alt.badge.img
  • kees_v
  • February 24, 2020
helmoet wrote:

Ashamed....

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using Oracle Reader (default Bulk Read Size)          X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
DUAL                                                                   2753901
==============================================================================
Total Features Read                                                    2753901
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Translation was SUCCESSFUL with 2 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 5.4 seconds. (CPU: 87.1s user, 18.2s system)
END - ProcessID: 7676, peak process memory usage: 386108 kB, current process memory usage: 326028 kB
Translation was SUCCESSFUL

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using SQLExecutor                                     X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Read                                                          0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Translation was SUCCESSFUL with 1 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 24.9 seconds. (CPU: 116.7s user, 20.1s system)
END - ProcessID: 8456, peak process memory usage: 392392 kB, current process memory usage: 329276 kB
Translation was SUCCESSFUL

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using MSSQLReader (default Bulk Read Size)            X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
GeoAsset.GEBIED_H                                                      2753901
==============================================================================
Total Features Read                                                    2753901
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Microsoft SQL Server Non-Spatial Reader: Closing `EGDB' . Read operation complete
Translation was SUCCESSFUL with 14 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 17.7 seconds. (CPU: 116.4s user, 20.7s system)
END - ProcessID: 4340, peak process memory usage: 376720 kB, current process memory usage: 316692 kB
Translation was SUCCESSFUL



XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
X                                                                    X
X   Test query using SQLExecutor                                     X
X                                                                    X
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                            Features Read Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Read                                                          0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
                           Features Written Summary
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
==============================================================================
Total Features Written                                                       0
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Translation was SUCCESSFUL with 1 warning(s) (0 feature(s) output)
FME Session Duration: 2 minutes 43.0 seconds. (CPU: 139.2s user, 21.2s system)
END - ProcessID: 9156, peak process memory usage: 381292 kB, current process memory usage: 321292 kB
Translation was SUCCESSFUL

 

I just checked the Oracle version too:

  • Same query,
  • First using Oracle Reader with no Bulk Read Size
  • Just have the results run into Inspector transformer.
  • Second Creator/SQLExecutor/Inspector.
  • In Oracle reader, Featuretype is DUAL, then using Select Statement to set query.
  • in MSSQL reader, Featuretype is just another (small) table, then also Select Statement to set query.

Results: not a very big difference. I'll rethink my setup. Thanks for helping me out @markatsafe, @david_r.

 

I am baffled. In your example the Oracle reader reads 2.753.901 records in 2 minutes. In my case it takes more than 2 hours. (bulk read size 10.000). Does someone why? 0684Q00000ArMZSQA3.jpg

david_r
Evangelist
  • david_rEvangelist
  • February 24, 2020
kees_v wrote:

I am baffled. In your example the Oracle reader reads 2.753.901 records in 2 minutes. In my case it takes more than 2 hours. (bulk read size 10.000). Does someone why?

Two hours sounds excessive. Lots of things to consider:

  • FME version
  • Table size (e.g. number of columns)
  • Geometry type (no geometry is obviously fastest, huge polygons are slow)
  • Middleware used or not (e.g. ArcSDE vs Oracle Spatial)
  • Database server performance
  • Network performance
  • FME client PC performance
  • Table read sequence vs block order
  • Is the "table" a query or a view?
  • SQL complexity, if relevant
  • etc...

Reply


Helpful Members This Week

Community Stats

31,104
Posts
117,917
Replies
38,856
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.