Hi Set,
 
I think the query is taking much more time than expectation as i have not seen the query result so far coz its going on... as you sent.
 
Let me ask you one question, is it really required to use "exists" predicate.
 
because it returns true or false and not as set of records as "IN" predicate does.
 
Let me explain the total background.
 
We have total three table for this DELETE query.

And all the three tableS contain same empcode with more or less number of records.
 
Say for example we have following employee codes:
 
"EmpCode001","EmpCode002","EmpCode003","EmpCode004","EmpCode005",
"EmpCode006","EmpCode007","EmpCode008","EmpCode009","EmpCode001".
 
Now Table_A will contain only 10 records i.e. one record per employee.
 
Now Table_B (which maintains the history of each employee) contains
total 100 records(say for example) in this table.
 
Table_C contains total 1000 records(say for example).
 
One thing is common that all these 10 eployees data is present in all the three tables.
 
Now, say for example our sub query gives an output as employee codes as follows:
 
"EmpCode003","EmpCode004","EmpCode005"
 
Now, our intension is, not to delete these three employees records form the Table_C for the specified period,
 
i.e. even though these employees records are present for the period i.e. dt_Date <= '03/31/2007', we don't want to delete.
 
(Exxcept these three employee all employees data shhould be deleted.
After deleetion if we see the records then these three employees data
should be present in the Table_C for the period  dt_Date <= '31/03/2009')
 
Here when we execute the query which you gave, deletes no records.
 
we were using IN predicate which compares record by record with sub query.
 
But IN predicate got some limitations with the values, means it must be less than or equal to 1,500 values.
 
Now what should be the best way to deal with this situation.
 
Thanks in Advance.

Vishal Tiwari...

--- On Thu, 10/9/09, Vishal Tiwari <vishualsoft@...> wrote:


From: Vishal Tiwari <vishualsoft@...>
Subject: RE: RE: [firebird-support] Speed optimization required for DELETE query
To: firebird-support@yahoogroups.com
Date: Thursday, 10 September, 2009, 6:29 PM


 



Hi,
 
plz. check the query once again u wrote
 
at the end u wrote that
 
B.dt_AppliedDate >= '03/31/2007' 
and in the begining reverse i.e.  C.dt_date <= '03/31/2007'

 
is it okay.
 
plz. check once again.
 
regards
 
Vishal Tiwari...
 
 
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' ));

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

From: Svein Erling Tysvær <svein.erling. tysvaer@kreftreg isteret.no>
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

Dream of becoming a dancing sensation? Find a dance guru on Yahoo! India Local http://in.local yahoo.com/

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

















Looking for local information? Find it on Yahoo! Local http://in.local.yahoo.com/

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