How fast does it work as a select, e.g. "Delete from Table_C C where..."
and if that is slow, what's the plan? How many rows does it return?
Instead of extracting the year and month, can you filter < '2007-04-01'
(if the plan shows it doesn't use a date index, and it's a useful
index)?

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of vishualsoft
Sent: Wednesday, September 09, 2009 11:08 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Speed optimization required for DELETE query

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'

Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.