CODEWITHSUNDEEP
oracle-databaseplsql
Piyush
Piyush

Reputation: 5315

How to check the SQL query performance and optimize it

I have below kind of script to execute which is taking approx 2 hours to update only 23% of records from the table. table has approx 170K records with 150 columns.

DECLARE
    V_EMP_SAL NUMBER;
BEGIN

For EmpIDs in (SELECT EmpID From Employees Where Emp_Address is null and Emp_phone is null and Emp_active='Y')

LOOP

  SELECT SUM(EMP_SAL) into V_EMP_SAL FROM Employees 
  where Emp_ID=EmpIDs.EmpID and Emp_Active='Y';

  UPDATE Employees
  Set EMP_SAL_HIKE = SAL- V_EMP_SAL
  WHERE EMP_ID=EmpIDs.EmpID;

  COMMIT;

END LOOP;

EXCEPTION
 WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROR OCCURED');
END;

Despite of writing this query in single update like below which is also taking similar time.

UPDATE Employees E1
Set E1.EMP_SAL_HIKE = E1.SAL- (SELECT SUM(EMP_SAL) FROM Employees 
                              where Emp_ID=E1.EmpID and Emp_Active='Y')
WHERE E1.Emp_Address is null 
AND E1.Emp_phone is null 
AND E1.Emp_active='Y';

my question is

  1. Why is it taking this much of time.
  2. how to know which part is taking more time.
  3. how to optimize this so that this gets executed very fast.

Thanks in advance.

Upvotes: 0

Views: 96

Answers (1)

Littlefoot
Littlefoot

Reputation: 143003

Row-by-row (in a loop) is slow-by-slow.

Your best option is to skip PL/SQL and loop completely and switch to a single update (or merge) statement, e.g.

merge into employees a
  using (select emp_id,
                sum(emp_sal) v_emp_sal
         from employees
         where emp_address is null
           and emp_phone is null
           and emp_active = 'Y'
         group by emp_id
        ) x
  on (e.emp_id = x.emp_id)
  when matched then update set
    a.emp_sal_hike = a.sal - x.v_emp_sal;

Upvotes: 2

Related Questions