Oracle Application tuning: Avoid SQL to do a lot of cyclic logic processing

The former encounter a case: a colleague said before a very normal package, suddenly more slow than before, the execution time is very long, the night of the job call this package ran for several hours did not run out of data. So I was in the tracking, optimization process to locate a stored procedure in the package of SQL, I have simplified the original SQL (the corresponding table name, function all randomly replaced), in general, in a cursor, in a loop to update the table tmp_jo_orders, which need to get some values through the function, These values are used to update the field values of the target table

For Cur_jo in (SELECT job_order_no from tmp_jo_orders WHERE sew_start >=sysdate) LOOP

SELECT MAIN_ITC. Get_must_info (Cur_jo. Job_order_no, ' sewing ', ' BUTTON ') into the My_m_button from DUAL;
SELECT MAIN_ITC. Get_must_info (Cur_jo. Job_order_no, ' sewing ', ' LABEL ') into the My_m_label from DUAL;
SELECT MAIN_ITC. Get_must_info (Cur_jo. Job_order_no, ' sewing ', ' TAPE ') into the my_m_tape from DUAL;
SELECT MAIN_ITC. Get_must_info (Cur_jo. Job_order_no, ' sewing ', ' ZIPPER ') into the my_m_zipper from DUAL;
SELECT MAIN_ITC. Get_must_info (Cur_jo. Job_order_no, ' sewing ', ' OTHERS ') into the my_m_others from DUAL;
SELECT MAIN_ITC. Get_must_info (Cur_jo. Job_order_no, ' THREAD ', ' all ') to My_m_thread from DUAL;
SELECT MAIN_ITC. Get_must_info (Cur_jo. Job_order_no, ' interlining ', ' all ') into the my_m_interlining from DUAL;
SELECT MAIN_ITC. Get_must_info (Cur_jo. Job_order_no, ' PACKING ', ' all ') into the my_m_packing from DUAL;

UPDATE tmp_jo_orders A
SET M_button=my_m_button
, M_label=my_m_label
, M_tape=my_m_tape
, M_zipper=my_m_zipper
, M_others=my_m_others
, M_thread=my_m_thread
, m_interlining=my_m_interlining
, m_packing=my_m_packing
WHERE Job_order_no=cur_jo. Job_order_no;
END LOOP;

In fact, before the normal operation, a sudden performance problem, is because the select Job_order_no from Tmp_jo_orders WHERE sew_start >=sysdate The amount of data due to a sudden increase in traffic, So the number of cycles of cursors increased from the previous dozens of bursts to more than 8,000 times.

Assuming that the SQL execution time within the cursor takes 2 seconds, previously only 30 cycles, then the SQL needs to be 2*30=60 seconds, if the number of cycles suddenly increases to 8,000 times, 2*8000=16000 seconds, this is a few hours of time. As you can imagine, this performance suddenly drops to an intolerable level!

So how to optimize it? Reduce the number of cycles, of course. After a careful look at this SQL, figuring out the business logic of the man who wrote the SQL, the above loop processing could be replaced with one of the following SQL statements, without the need for a record update. At that time, after the revised test, found that the modified SQL, less than 1 minutes to run out.

UPDATE tmp_jo_orders A
SET M_button =main_interface. Get_must_info (job_order_no, ' sewing ', ' BUTTON ')
, M_label =main_interface. Get_must_info (job_order_no, ' sewing ', ' LABEL ')
, M_tape =main_interface. Get_must_info (job_order_no, ' sewing ', ' TAPE ')
, M_zipper =main_interface. Get_must_info (job_order_no, ' sewing ', ' ZIPPER ')
, M_others =main_interface. Get_must_info (job_order_no, ' sewing ', ' OTHERS ')
, M_thread =main_interface. Get_must_info (job_order_no, ' THREAD ', ' all ')
, M_interlining=main_interface. Get_must_info (job_order_no, ' interlining ', ' all ')
, M_packing =main_interface. Get_must_info (job_order_no, ' PACKING ', ' all ')
WHERE Sew_start >=SYSDATE;

In fact, this is just a special case, I just take it as a primer, I want to introduce the point of view: We know that SQL is a structured query language, good at structured queries, and not good at logic processing (Whie, IF. ELSE), but sometimes, a lot of people like to use SQL to deal with business logic, of course, not that can not be in the stored procedures, functions to do some business logic processing, but found that many people over-amplification of SQL logic processing function, the complex logic operations are all moved into the package, stored procedures for processing, For example, the loop operation above, a bad result of this is the performance problem, like a person who is good at sprinting, you want him to go to the long-distance running. Then the result of the game is certainly not good to go.

In the development, we have to do some optimization of business logic, to avoid complex logic operations, especially to avoid a very large number of cycles of business logic processing, on the one hand we want to simplify business logic, some business logic to the program to deal with, on the other hand we can use SQL very clever to achieve a lot of logic complex requirements, Avoid having to do a lot of complex logic processing, rather than writing more complex SQL statements under complex business. For example above, I used to tell the same idea in an article about Ms SQL Challenge issues.

Oracle Application tuning: Avoid SQL to do a lot of cyclic logic processing