Hi SET,
 
Welcome back again.
 
Thanks for your valuable suggestion.
 
As per requirement, i did some changes in your mailed query and i am getting proper result.
 
Ya it was new chapter for me to learn EXISTS predicate.
 
But one more thing SET, if you have a good document on EXISTS predicate, so i will request you to mail me, coz in different situations i am finding EXISTS predicate is playing a vital role. And with this it takes a greate efforts to make SQL as per EXISTS predicate.
 
Rest is fine SET.
 
It was nice to talk to you after such a long time.
 
And freely speaking, i was expecting reply from you ;) and finally got it.
 
But sorry for my late repty coz i needed to check the result.
 
 
Thanking You With The Best Regards And Expecting To Hear From You.
 
Thanks Once Again.
 
Have A Nice Day
 
Vishal Tiwari...
 
 

--- On Wed, 4/11/09, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:


From: Svein Erling Tysvær <svein.erling.tysvaer@...>
Subject: RE: [firebird-support] Speed improvement required for query execution
To: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Date: Wednesday, 4 November, 2009, 2:48 PM


 



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.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

















Add whatever you love to the Yahoo! India homepage. Try now! http://in.yahoo.com/trynew

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