Home » RDBMS Server » Performance Tuning » re-write the query to improve performance
re-write the query to improve performance [message #386597] Mon, 16 February 2009 03:47 Go to next message
ind9
Messages: 65
Registered: January 2009
Member
 Select Emp_No Emp
 From Employees

    Emp 
--------
      58
      59
      60
      61
      62
      63
      64
      65
      66
9 rows selected.

Select Emp_No, Emp_Erng, Year, Month 
 From Emp_Details

    Emp   Emp_Erng   Year     Month
-------- -------- -------- --------
      58    10000     2008       03
      58        0     2008       03
      59    10200     2008       04
      60     1000     2008       04


Below select query is badly performing in production environment
------------------------------------------------------------------
Select Emp_No Emp,
       Sum(Emp_Erng) Erng
From      Emp_Details det
 Where Year = 2008    
   And Month <=3
Union
Select Emp_No Emp,
         0 Erng
From Employees e
 Where Not Exists 
   (Select 1 
     From  Emp_Details det
      Where Year = 2008    
       And Month <=3
       And Det.Emp_No = E.Emp_NO)

     Emp       Erng 
--------   --------
      58     10000     
      59         0
      60         0
      61         0
      62         0
      63         0
      64         0
      65         0
      66         0

So, I have re-written the query like this.
-------------------------------------------
Select /*+ Use_hash(e,det) */
        e.Emp_No Emp,
       Sum(det.Emp_Erng) Erng
From Employees e,
     Emp_Details det
Where e.emp_no = det.emp_no(+)
 And Year = 2008    
 And Month <=3
Group by e.emp_no

    Emp       Erng 
--------   --------
      58     10000 

But the query has to fetch all the employees as shown below.

     Emp       Erng 
--------   --------
      58     10000     
      59         0
      60         0
      61         0
      62         0
      63         0
      64         0
      65         0
      66         0


How i can re-write the query to fetch output like shown above.
Performance is a major factor.

Kindly assist.

Thanks
Re: re-write the query to improve performance [message #386622 is a reply to message #386597] Mon, 16 February 2009 04:39 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
With this one the sum is limited to the rows on Emp_Details and the join could be much more performing because rows joined here should be less than the ones on the other query, because they are already grouped.

Select Emp_No Emp,
	nvl(Erng,0) as Erng
From Employees
	left outer join (
     	select Emp_No,Sum(Emp_Erng) Erng
		from  Emp_Details
		Where Year = 2008    
 			And Month <=3
		group by Emp_No
	) using (emp_no)
Group by e.emp_no


Bye Alessandro
Re: re-write the query to improve performance [message #386642 is a reply to message #386622] Mon, 16 February 2009 06:12 Go to previous message
ind9
Messages: 65
Registered: January 2009
Member
Hi,

Thanks for your valuable suggestion.

With your suggestion i modified query. Now the query is doing fine.

Thanks alot.
Previous Topic: how to change clustering factor
Next Topic: TkProf-Perormance Tunning (merged)
Goto Forum:
  


Current Time: Fri Dec 09 15:52:58 CST 2016

Total time taken to generate the page: 0.14376 seconds