Skip to main content
Question

SQLExecutor Very slow when using DELETE FROM in Oracle

  • February 9, 2024
  • 1 reply
  • 72 views

romar
Contributor
Forum|alt.badge.img+3
  • Contributor

I am having issues with glacial speeds of using a delete from statement within a SQLExecutor pointed to Oracle Database.

 

The SQL query is something like:

DELETE FROM ABC

WHERE ID = @value(id)

AND TO_CHAR(DATETIME,’YYYYMMDDHH24’) > @value(timefrom)

AND TO_CHAR(DATETIME,’YYYYMMDDHH24’) <= @value(timeto)

 

(note: The query above is paraphrased and might contain syntax-errors, I have checked and confirmed the syntax in the actual version)

 

The Oracle Table contains about 40 million rows, so I expect this to take some time (1-5 minutes per query) but not the >30 minutes for the first query (at which point I gave up, I have not confirmed if the query will ever finish)

 

Currently I am using a workaround of Loading the primary keys of all features that fit the selection criteria and using a writer to DELETE all corresponding records, but this seems unnecessarily inefficient and wasteful.

 

I can imagine that the error is caused by Oracle not returning a ‘succeeded’  or ‘finished’ message, causing FME to consider Oracle as busy forever.

 

Does anyone have any ideas to solve this issue?

1 reply

romar
Contributor
Forum|alt.badge.img+3
  • Author
  • Contributor
  • February 9, 2024

This issue seems to have resolved itself overnight. I Will need more time to check for consistency.

I am mainly interested in people, who have encountered this before and have found a different workaround.


Reply


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