SQL execution

I am using Oracle DB. My sql commands are update commands. I believe it is directly sending to the oracle to execute rather than regular transformer which execute in batches. Please clarify. 

The reply from @ctredinnick is excellent and mirrors my own way of dealing with this.

I can add that part of the reason for the slow performance of the SQLExecutor is that it does not currently support prepared statements or bind variables*, meaning that the DB back-end has to redo parsing the entire SQL statement for each invocation (trigger feature), even though only the values change, not the SQL statement. This may cause significant overhead when the number of features increase and/or the SQL statement is non-trivial. Also, the SQLExecutor will create a separate database transaction for each trigger feature, which is fairly costly in itself.

*) It seems Safe has done some work on this matter but for Oracle only. Therefore I haven’t been able to test it myself. See the discussion here for details:

• SQLExecutor support for bind variables Open
• 41 Votes

If you’re handy with Python, it might even be interesting to try out the route suggested by @vlroyrenn on that page. Those libraries should (hopefully) support prepared statements, although it’s possible that you’ll add some overhead from the fmeobjects module when transferring attributes back and forth.

However, you should be able to get improved performance by grouping the UPDATE statements into transaction blocks of e.g. between 100 to 1000 update statements before passing the statement to the SQLExecutor, as suggested by @ctredinnick. You could populate a text attribute with something like the following, before passing it on to the SQLExecutor:

FME_SQL_DELIMITER @

begin transaction;
  update my_table set my_column = 'abc' where my_pk = 123;
  update my_table set my_column = 'def' where my_pk = 456;
  -- etc...
commit;

Note that you’ll have to set FME_SQL_DELIMITER to a character that doesn’t occur in your statement, to prevent FME from trying to split up the SQL before passing it onto the database.

Ultimately, for “regular” update statements, it might be (much) faster to use the Oracle writer / FeatureWriter with mode set to UPDATE, since those mechanisms use prepared statements and automatic transaction groups.

For the record, the libraries I menionned in my last comment on that FME Idea are all low-level C/C++ native bindings that FME could use as a last resort if it’s not possible for them to properly support SQL prepared statement parameter binding, and even then only two (Postgres and MySQL) would actually support that. If you’re using Python, though, you have some options, like SQL string composition for psycopg, or statement generation for SQLAmchemy Core expressions, on top of my head.

Regarding @harish‘s original issue of SQL executor being slow for large batches of features, the approach I prefer to use in these cases is to write all the features as-is with a FeatureWriter to a “temporary table” (FeatureWriter supports writing up to 2 billion-ish features per transaction) and run a single SQL query to move and transform the data to the actual table I’m trying to write to. It’s rather wonky, since FME switches sessions between statements, meaning actual temporary tables can’t be used, and you need to create and delete your own manually.