|Subject||Speed improvement required for query execution|
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:
Index Keys for Leave_Transactions table are:
Primary key for EmployeeMaster table is:
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,
from LEAVE_TRANSACTIONS A Join EMPLOYEEMASTER EM
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'
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
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]