Skip to main content
  • EN

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.

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

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?

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.

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.

 

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

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!

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.

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

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


Community Stats

30,112
Posts
114,305
Replies
37,745
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.