Hi Helen,
 
Making you my GURU (Teacher)!!!!!!!!!!! in SQL field. :)
 
The query runs with tremendous speed.
 
Query took only one second to execute for both the conditions i.e. for "not exists" and for "exists" too.
 
but i made one change in sub query, as follows:
 "Select A.var_EmpCode ....and continued same query"
instead of
"Select 1....and continued query"
 
Thank You very much.
 
I hope i will come up with one or two more queries. :)
 
Have A Nice Day.
 
Thanking You with Best Regards.
 
Vishal Tiwari....
 
 
 
Delete from Table_C C
where
C.dt_date <= '03/01/2007'
And
not exists (
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
where
A.var_Empcode = C.var_EmpCode and
And
(A.var_Status = 'I'
or
A.var_Resigned_ But_Not_Settled = 'Y') )
and Max(B.dt_AppliedDat e) < '03/31/2007' /* are you sure about this? */
);

--- On Thu, 10/9/09, Helen Borrie <helebor@...> wrote:


From: Helen Borrie <helebor@...>
Subject: Re: [firebird-support] Speed optimization required for DELETE query
To: firebird-support@yahoogroups.com
Date: Thursday, 10 September, 2009, 11:14 AM


 



At 01:07 AM 10/09/2009, vishualsoft wrote:

>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_AppliedDat e) < '03/31/2007'
> );
>
> Even if we modifiy sub query as shown below,
> it takes same amount of time.
>

Simplify the thing!

Delete from Table_C C
where
C.dt_date <= '03/01/2007'
And
not exists (
select 1 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
And
(A.var_Status = 'I'
or
A.var_Resigned_ But_Not_Settled = 'Y') )
and Max(B.dt_AppliedDat e) < '03/31/2007' /* are you sure about this? */
);

A DESC index on B.dt_AppliedDate might be used there, too.

./heLen

















See the Web's breaking stories, chosen by people like you. Check out Yahoo! Buzz. http://in.buzz.yahoo.com/

[Non-text portions of this message have been removed]