Hi Tom,
Thanks for the feedback. I've moved the temp tables to internal function, increased max_locks_per_transaction and dropped the tables instead of deleting them.
The performance drop is till there, but it is much, much less then the previous case. Previously the whole execution took 04:36:14 and when I use the drop table approach it takes 00:01:44
less then 2 minutes compared to 4 and a half hours...
I will try TRUNCATE as well and see what happens then. Oh, btw, I realised I've used perform in the version of the code I've included in my initial message, it was simply to eliminate the main temp table in the outer query as a suspect.
Regards
Seref