Optimize nested for loop query

Question

I have two tables Emp & Emp_Details

Emp -> EmpId, EmpName
Emp_Details -> EmpDetailId, Domain, Year, Date_of_record

Each employee has many domains, where their perfomance is measured, and it can be measured more than once a year.

So emp1 can have more than one recodes in Emp_Details for Domain1 for year 2015.

Now I want to fetch latest records per Domain per year for an employee.

Currently I'm fetching using two nested FOR loops in Java, i.e. one FOR loop for each factor and another inner FOR loop for each year fetching latest Date_of_record.

How should I optimize it? I'm using PL/SQL.

@Vance sorry due to some proprietary issue, I'll not be able to post the code. Really sorry. — reiley
– reiley, Commented Nov 26, 2015 at 12:00
@reiley Nobody wants to steal your code, especially as it is apparently not very optimal. — David Aldridge
– David Aldridge, Commented Nov 26, 2015 at 12:55
@DavidAldridge, haha, still cant do. I was just helping a friend, it was not mine. Thank you. — reiley
– reiley, Commented Nov 26, 2015 at 14:33

jarlh · Accepted Answer · 2015-11-26 12:11:49Z

1

Do a GROUP BY, use MAX to find that years latest date (for each group).

select e.EmpName, ed.Domain, ed.Year, MAX(ed.Date_of_record)
from Emp e
  left join Emp_Details ed ON e.EmpId = ed.EmpId
group by e.EmpName, ed.Domain, ed.Year

answered Nov 26, 2015 at 12:11

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Avrajit Roy · Accepted Answer · 2015-11-27 07:11:12Z

0

Hello you can also try Analytical function to suffice your requirement. Let me know if this helps

SELECT a.empid,
  a.empname,
  a.domain,
  a.dor
FROM
  (SELECT e.EMPID,
    e.EMPNAME,
    ed.DOMAIN,
    ed.Date_of_record dor,
    ROW_NUMBER() OVER(PARTITION BY ed.EMPID,ed.DOMAIN ORDER BY ed.Date_of_record DESC) rnk
  FROM emp e,
    emp_details ed
  WHERE ed.empid = e.empid
  )a
WHERE a.rnk = 1;

edited Nov 27, 2015 at 7:11

user330315

answered Nov 27, 2015 at 6:57

Avrajit Roy

3,3131 gold badge15 silver badges25 bronze badges

Collectives™ on Stack Overflow

Optimize nested for loop query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related