Am I missing something really obvious here?
I need to do an update of a field (CODE) in the customer file, based on a value in another table (SEGMENT). There is not an entry in this table for every customer.
I had created an SQL index on CUSTNO for the table SEGMENT before running the UPDATE and the Customer file has a LF keyed on SOCI/CUSTNO.
When I ran this update in interactive SQL, after about 2 minutes it had only done a couple of hundred updates. For 600,000 customers, there are about 300,000 with an entry in SEGMENT. So hours of processing ! I killed it.
A quick few lines of RPG later and the program ran in about 2 minutes for the whole file.
Thanks Andrew
I need to do an update of a field (CODE) in the customer file, based on a value in another table (SEGMENT). There is not an entry in this table for every customer.
Code:
update CUSTFILE_file A set CODE = (select CODE from SEGMENT B where B.CUSTNO=A.CUSTNO) where SOCI='03' and exists (select * from SEGMENT D where D.CUSTNO=A.CUSTNO)
When I ran this update in interactive SQL, after about 2 minutes it had only done a couple of hundred updates. For 600,000 customers, there are about 300,000 with an entry in SEGMENT. So hours of processing ! I killed it.
A quick few lines of RPG later and the program ran in about 2 minutes for the whole file.
Thanks Andrew
Comment