Is there a notable performance difference between SQL Creator performance vs. Oracle table Reader performance. We have a script that take 3.5 hours to run and are looking for ways to help it along.
Solved
SQL Creator performance vs. Oracle table Reader
+7- Contributor
Best answer by david_r
In principle it should be about the same.
But there's a few cases where the SQLCreator can be a lot faster:
- If you can use the ORDER BY in the SQLCreator rather than the Reader + Sorter in FME
- If you can do your JOINs in the SQLCreator rather than using Readers + FeatureMergers
- If you have a really wide table (lots of fields with e.g. blobs or large text blocks) but only need a couple of them, you could reduce the amount of data transferred by quite a lot by only selecting the necessary fields, for example:
select
field3,
field14,
field27
from
my_huge_table
To test if it's the reader performance that's holding you back, consider creating an empty workspace and copy/paste the Oracle readers into it. Connect them to a NULL writer and run. If the time is about the same, it's probably the database or the network that's holding you back. If your test workspace is way faster, it's probably somthing else in your workspace that's taking a long time.
- Best Answer
In principle it should be about the same.
But there's a few cases where the SQLCreator can be a lot faster:
- If you can use the ORDER BY in the SQLCreator rather than the Reader + Sorter in FME
- If you can do your JOINs in the SQLCreator rather than using Readers + FeatureMergers
- If you have a really wide table (lots of fields with e.g. blobs or large text blocks) but only need a couple of them, you could reduce the amount of data transferred by quite a lot by only selecting the necessary fields, for example:
select
field3,
field14,
field27
from
my_huge_table
To test if it's the reader performance that's holding you back, consider creating an empty workspace and copy/paste the Oracle readers into it. Connect them to a NULL writer and run. If the time is about the same, it's probably the database or the network that's holding you back. If your test workspace is way faster, it's probably somthing else in your workspace that's taking a long time.
+17- Contributor
Another one (added to the great list provided by @david_r:
SQLCreator should be faster if you need to use functions provided by the database (e.g. UPPER(field1), Count(*) ).
In general: if the database can execute functions instead of FME after the read.
Reply
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.