Hi,
 
Thanks Set (I hope i am right with ur name and sorry if i am wrong).
 
Thanks for ur initiative, i will try with ur query.
 
And I am waiting for helen's replay too.
 
Thanks once again
 
Vishal Tiwari...

--- On Thu, 10/9/09, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:


From: Svein Erling Tysvær <svein.erling.tysvaer@...>
Subject: RE: RE: [firebird-support] Speed optimization required for DELETE query
To: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Date: Thursday, 10 September, 2009, 6:03 PM


 



Sorry again, your query should not contain doubly negated exists (finally, my brain awoke), the last should simply be exists:

Delete from Table_C C
where C.dt_date <= '03/31/2007'
And not exists(
select 1 from Table_A A
where A.var_Empcode = C.var_EmpCode and
(A.var_Status = 'I'
or A.var_Resigned_ But_Not_Settled = 'Y')
and exists(
select 1 from Table_B B
where A.var_EmpCode = B.var_EmpCode
And A.var_PGCode = B.var_PGCode
and B.dt_AppliedDate >= '03/31/2007' ));

Sorry for messing this up,
Set

-----Original Message-----
From: firebird-support@ yahoogroups. com [mailto:firebird-support@ yahoogroups. com] On Behalf Of Svein Erling Tysvær
Sent: 10. september 2009 14:31
To: firebird-support@ yahoogroups. com
Subject: RE: [firebird-support] Speed optimization required for DELETE query

When did my logic stop working? True and not true has never been true. Let me try again:

EmpCode002: EmpCode003:
True and True and
not exists (True and not exists(True and
not exists (False)) not exists(True) )
=
True and True and
not exists(True and not exists(True and
True) False)
=
True and not exists True and exists
=
Deleted Not deleted

I think this is how it works, although I find it much easier to write in SQL than the pseudocode I wrote above...

Set

-----Original Message-----
From: Svein Erling Tysvær
Sent: 10. september 2009 14:15
To: 'firebird-support@ yahoogroups. com'
Subject: RE: [firebird-support] Speed optimization required for DELETE query

I cannot answer for Helen's query (I'm not used to max without group by and was surprised that it didn't produce a syntax error), but here's a walkthrough of mine for EmpCode002 and EmpCode003:

Delete from Table_C C
where C.dt_date <= '03/31/2007'

/*Is the date early enough? True or both EmpCode002 and EmpCode003 */

And not exists(
select 1 from Table_A A
where A.var_Empcode = C.var_EmpCode and
(A.var_Status = 'I'
or A.var_Resigned_ But_Not_Settled = 'Y')

/*Do they exist with correct values in Table_A? True for both EmpCode002 and EmpCode003 */

and not exists( select 1 from Table_B B where A.var_EmpCode = B.var_EmpCode And A.var_PGCode = B.var_PGCode and B.dt_AppliedDate >= '03/31/2007' ));

/*Do they have a recent AppliedDate? True for EmpCode003, false for EmpCode002*/

So,

EmpCode002: EmpCode003:
True and True and
not (True and not (True and
not (False)) not (True))
=
True and True and
not (True and not (True and
True) False)
=
True and True and
not True not False
=
True False
=
Deleted Not deleted

Set

------------ --------- --------- ------

++++++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++

Visit http://www.firebird sql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoeni x.com

++++++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++
Yahoo! Groups Links

















Love Cricket? Check out live scores, photos, video highlights and more. Click here http://cricket.yahoo.com

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