Hi Community,
I am using some SQLExecutors in my workbench and the this is taking so much time to execute. Is there any way I can execute it faster. Is it because the statement going from fme to outside db and execute it in batches of 1000 and commits the edits making the whole delay?. Is it possible to execute the whole sql actions using a plsql script and bind it to fme?. I may also need to have logs or exception tables for plsql script. Is it possible through fme?
+17
The SQLExecutor will execute once per feature which is passed into it. I believe the database connection is maintained between statements, but they are independent transactions each committed separately and immediately. So each statement has a time cost. If each SQL statement is for example a simple INSERT INTO table VALUES (), then features will be inserted one at a time and not batched.
You can batch inserts yourself, I’ve done this before with a GroupCounter, and then combination of ListBuilder/ListConcatenator to build up a long insert script, to minimise the number of statements needing to be run. INSERT VALUES has a limit of 1000 rows at a time, which is the reason for the GroupCounter. This will run a little bit slower than a SQL Writer doing a bulk insert, but it’s still good.
Also bear in mind the ; delimiter works like a GO command, not a regular SQL ; (meaning variables etc are dropped after it)
For logs/exceptions, I believe a try..catch flow will work, though I’m only familiar with microsoft SQL
+7
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
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:
If you’re handy with Python, it might even be interesting to try out the route suggested by
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
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.
+12
If you’re handy with Python, it might even be interesting to try out the route suggested by
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
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
