This is an understatement...
UPDATE TABLE a
SET a.EMPLID = (SELECT b.NEW_EMPLID FROM TABLE b WHERE a.EMPLID = b.EMPLID)
(SELECT b.NEW_EMPLID FROM TABLE b WHERE a.EMPLID = b.EMPLID);
The table has 310,000 rows.
The SQL transform uses a static connection and I am using most of the default settings. I wanted the row count returned. I was also careful with the order of the input and output columns.
When I run the query in SQL Plus it updates in about 2 minutes. When I run it in the SQL Transform...well...I aborted after letting it run almost 2 hours.
What is it in the SQL Transform that would prevent it from running as like SQLPlus or at least in the same ball park would be nice.
Thanks - Ken
When I got in this morning it was still running. 14 hours and counting.
I re-wrote the query to:
UPDATE tablename a
SET a.field = (SELECT b.newvalue from table b where a.emplid=b.emplid);
And it now performs as expected. 310,000 rows in 2 minutes. Like running it in SQLPlus.
Very interesting... hey can you help me on my issue with SQL transformation... i am just using
select col1 from table
and fetching the data through SQL transformation and placing in one target table... same way getting Target data into one table and at the end i am comparing both the tables to isolate the records not there in both the target tables and do my next process... but i see the issue to fetch data from Source into Target table using sql transformation... would be great if you give some tips. I know it's nothing to do with Queries... it's all SQL transformation performance.