I don't think it's a good idea to have MAX within an EXISTS, so try

Select A.Ref_No,
(EM.EMPNAME||' ['||A.EmpCode||']') as EMPCODE,
A.LveCode,
Min(a.FROM_DATE) As From_Date,
Max(A.To_Date) As To_Date,
Max(A.No_Of_Days) As No_Of_Days,
A.NEXT_APPROVER,
A.status
from LEAVE_TRANSACTIONS A
Join EMPLOYEEMASTER EM On a.EMPCODE=EM.EMPCODE
where exists (Select 1
from LEAVE_TRANSACTIONS B
where A.Ref_No = B.Ref_No
And B.LVESHORT>='2009'
and Upper(B.NEXT_APPROVER)='ADMINISTRATOR'
and B.sr_no is not null)
And A.LVESHORT>='2009' and Upper(A.NEXT_APPROVER)='ADMINISTRATOR'
AND Upper(a.status)='PENDING'
GROUP 1, 2, 3, 7, 8
order by 4 desc

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Vishal Tiwari
Sent: 3. november 2009 17:23
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Speed improvement required for query execution

 
 
Hi All,
 
I have one small query which needs to display all the pending leaves of all employees who fall under pertiular approver.
 
Table structure of Leave_Transactions table is in such a way that there could be more than one record for perticular transaction number (i.e. for "Ref_No" field).
 
But for each reference number there will be separate serial no let's have a look:
 
Ref_No Sr_No  From_Date    To_Date      Status     Next_Approver
10001     1       30/10/2009    31/10/2009   Apply      Administrator
10001     2       01/11/2009    05/11/2009   Pending   Administrator
 
 
 
So, the requirment is, Ref No. field's last record should contain "Pending" status, only such employee should be displayed.
 
Result is displying properly say 35,000 records but it is taking 19 seconds which i feel is too much time for such result, coz there are total 35,000 records present in Leave_Transactions table.

Primary Key for Leave_Transactions table is:
 
1) Ref_No,Sr_No

Index Keys for Leave_Transactions table are:
1) LVESHORT
2) EMPCODE
3) FROM_DATE
4) NEXT_APPROVER
5) STATUS
 
 
Primary key for EmployeeMaster table is:
 
1) EmpCode

Query is as follows:
 
 
Select DISTINCT(A.Ref_No),(EM.EMPNAME||' ['||A.EmpCode||']') as EMPCODE,A.LveCode,
Min(a.FROM_DATE) As From_Date, Max(A.To_Date) As To_Date,
Max(A.No_Of_Days) As No_Of_Days,
A.NEXT_APPROVER, A.status
from LEAVE_TRANSACTIONS A Join EMPLOYEEMASTER EM
On a.EMPCODE=EM.EMPCODE
where
exists (Select max(B.sr_no) from LEAVE_TRANSACTIONS B where A.Ref_No = B.Ref_No And
B.LVESHORT>='2009' and Upper(B.NEXT_APPROVER)='ADMINISTRATOR' Group by B.REF_NO)

And A.LVESHORT>='2009' and Upper(A.NEXT_APPROVER)='ADMINISTRATOR'
AND Upper(a.status)='PENDING'
GROUP BY A.REF_NO, A.EmpCode,EM.Empname,A.LveCode, A.NEXT_APPROVER,A.status
order by from_Date desc
 

So, as per this can anybody suggest the best way to improve the query execution speed.
 
Thanks in advance

Vishal Tiwari....
 


Connect more, do more and share more with Yahoo! India Mail. Learn more. http://in.overview.mail.yahoo.com/

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



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

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

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

Also search the knowledgebases at http://www.ibphoenix.com

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