Query Performance Issue

[Das]

Hi All,

I have some performance issues with Datastage job.Job is running very slow and tranfering <1 per sec,here its around 20lakhs of record

For this job the source and target are same Informix Stage and that refernces only one table Tab1,extracting data based on the below query

select Col1
From Tab1
Where Col1 IN (select max(Col1) from Tab1
where A is NOT NULL AND B='Y" AND C=123
Group by F )


And update the same table at target.
Col1,A,C are indexed,the column to be updated in the target is also indexed.

Please suggest a solution to improve the performance.

[DSguru2B]

How much time does it take for the same query to run outside datastage?
Does your target table have triggers on it? How many bytes in a row? Do you have any warnings in the datastage log?

[ray.wurlod]

Correlated subqueries are always slowest. Not having an index on column B will cause a table scan to be required. Having an index on the target column will slow updates.