DELETE and INSERT using OPENQUERY to Oracle require long delay between them I'm doing a DELETE followed by an INSERT on an OPENQUERY to Oracle. I have tried a straight DELETE with a WHERE as well as filtering with a JOIN. INSERT is just an INSERT OPENQUERY/SELECT. I have also tried a filtered UPDATE followed by INSERT on new rows. Since I can't do a MERGE on OPENQUERY, I must do two statements back-to-back.
DELETE, UPDATE and the INSERT work as expected, but only if I run them 10 minutes apart. Individually, they complete in less than one second. When run back-to-back, using either sp_executesql or SSMS, whether running them individually or in one script, they take 10 minutes exactly.
Talked to the Oracle DBA, doesn't ring any bells for him.
The queries themselves, as much as I would like to share them to help you help me, I am not free to do so. Please take my word for the fact that they both work as expected in less than one second when run 10 minutes apart and take 10 minutes when run together.
This turned out to be an issue with OPENQUERY and the related &quot;inconsistent metadata&quot; issue requiring OPTION (RECOMPILE). Switching to EXEC AT for write queries (UPDATE, INSERT and DELETE) resolved the throttling effect.