OK, then I did manage to populate some test tables (very rough, probably not similar to your case) and the following three queries produced the same result:

Your original query:
Delete from Table_C C
where C.dt_date <= '31.03.2007'
And C.var_EmpCode not In (
select 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) <= '31.03.2007')

Helens suggestion:
Delete from Table_C C
where C.dt_date <= '31.03.2007'
And not exists (
select a.var_pgcode 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_empcode = c.var_empcode
And (A.var_Status = 'I'
or A.var_Resigned_But_Not_Settled = 'Y')
group by a.var_pgcode
having Max(B.dt_AppliedDate) <= '31.03.2007')

My suggestion:
Delete from Table_C C
Where C.dt_date <= '31.03.2007'
And not exists (
select * from Table_A A
where C.Var_EmpCode = A.var_EmpCode
and (A.var_Status = 'I'
or A.var_Resigned_But_Not_Settled = 'Y')
and not exists (
select * from Table_B B
where A.var_EmpCode = B.var_EmpCode
And A.var_PGCode = B.var_PGCode
And B.dt_AppliedDate > '31.03.2007'))

I hadn't understood your query properly before - I thought you wanted to delete old records unless there still were activity, whereas you wanted to delete old records unless they were inactive.

I think there are slight differences between these three queries, though my head doesn't work too well today and I cannot tell for sure (mainly my suspicion is regarding possible NULL values in var_pgcode).

As for timing, that can only be done on data similar to yours, and I'm more than 99% certain that my test did not resemble your situation correctly.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Vishal Tiwari
Sent: 14. september 2009 15:45
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Deifference between IN and EXISTS

Hi All,

Is there anything wrong with EXISTS predicate and IN predicate

Anybody can tell me where i am making mistake in query no. 2.

Only Query no. 1 deletes certain records and Query no. 2 deletes no records.

will EXISTS predicate give benifit in query no. 2, means can delete any records with some modification in query no. 2.

Both the queries are given below.

regards

Have A Nice Day.

Vishal Tiwari...
*******************************
Query No.1:

Delete from Table_C C
where
C.dt_date <= '03/01/2007'
And
C.var_EmpCode not In (
select 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') )
and Max(B.dt_AppliedDat e) <= '03/31/2007' );


Query No.2:

Delete from Table_C C
where
C.dt_date <= '03/01/2007'
And
not exists (
select 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') )
and Max(B.dt_AppliedDat e) <= '03/31/2007' );