Hi All,

I have one query that needs to delete certain records for the specified condition.


Fields name and their respective data types:
1) dt_Date Date
2) var_EmpCode Varchar(15)
3) var_PGCode Varchar(10)
3) var_Status Varchar(1)
4) var_Resigned_But_Not_Settled Varchar(1)
5) dt_AppliedDate Date

Note:
1) dt_Date is already indexed field in the table Table_C.
2) var_EmpCode and var_PGCode are indexed fields in
the table Table_A

i) Table Table_A contains unique employee ID and their
respective data i.e. one record per employee.

ii) Table Table_B contains the history of each employee
which are in table Table_A
i.e. it contains more than one record for
each emplyee which are in table Table_A.

iii) All the three table contains the fields var_EmpCode
and var_PGCode.

Query is taking much more time than expectation.

Thanks in Advance.

Vishal
************************************************
Query is as follows:

Delete from Table_C C
where
Extract(Year from C.dt_Date) <= '2007'
And
Extract(Month from C.dt_Date) <= '03'
And
C.var_EmpCode
Not In(
Select Distinct(A.var_EmpCode)
from Table_A A Join Table_B B
On
A.var_EmpCode = B.var_EmpCode
And
A.var_PGCode = B.var_PGCode
And
(A.var_Status = 'I'
or
A.var_Resigned_But_Not_Settled = 'Y')

Group By A.var_EmpCode
Having Max(B.dt_AppliedDate) < '03/31/2007'
);

Even if we modifiy sub query as shown below,
it takes same amount of time.

Modified sub query:

Select Distinct(A.var_EmpCode)
from Table_A A Join Table_B B
On
(A.var_EmpCode = B.var_EmpCode
And
A.var_PGCode = B.var_PGCode
)
where
(A.var_Status = 'I'
or
A.var_Resigned_But_Not_Settled = 'Y')

Group By A.var_EmpCode
Having Max(B.dt_AppliedDate) < '03/31/2007'