ORACLE application optimization: Please avoid SQL processing in a large number of cyclic logic, oracle Optimization

ORACLE application optimization: Please avoid SQL processing in a large number of cyclic logic, oracle Optimization

I encountered a case a while ago: a colleague said that a previously running package was suddenly much slower than before and the execution time was very long, in the evening, the job calls this package and does not run data for several hours. So I located an SQL statement in a stored procedure in the package during the tracking and optimization process. I simplified the original SQL statement (the corresponding table names and functions were all replaced by random names ), as shown in the following figure, the TMP_JO_ORDERS table needs to be updated cyclically in a cursor, and some values need to be obtained 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 MY_M_BUTTON from dual;
SELECT MAIN_ITC.GET_MUST_INFO (CUR_JO.JOB_ORDER_NO, 'sewing ', 'label') INTO MY_M_LABEL from dual;
SELECT MAIN_ITC.GET_MUST_INFO (CUR_JO.JOB_ORDER_NO, 'sewing ', 'tape') INTO MY_M_TAPE from dual;
SELECT MAIN_ITC.GET_MUST_INFO (CUR_JO.JOB_ORDER_NO, 'sewing ', 'zipper') INTO MY_M_ZIPPER from dual;
SELECT MAIN_ITC.GET_MUST_INFO (CUR_JO.JOB_ORDER_NO, 'sewing ', 'others') INTO MY_M_OTHERS from dual;
SELECT MAIN_ITC.GET_MUST_INFO (CUR_JO.JOB_ORDER_NO, 'thread', 'all') INTO MY_M_THREAD from dual;
SELECT MAIN_ITC.GET_MUST_INFO (CUR_JO.JOB_ORDER_NO, 'interlining ', 'all') INTO MY_M_INTERLINING from dual;
SELECT MAIN_ITC.GET_MUST_INFO (CUR_JO.JOB_ORDER_NO, 'packaging', 'all') INTO MY_M_PACKING from dual;

UPDATE TMP_JO_ORDERS
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, the previous operation was normal and suddenly encountered performance problems because SELECT JOB_ORDER_NO FROM TMP_JO_ORDERS WHERE SEW_START> = SYSDATE's data volume suddenly increased due to a large increase in business volume, therefore, the number of cursors has increased from dozens of times to more.

Assume that the SQL statement execution time in the cursor takes 2 seconds, and the previous cycle is only 30 times, it takes 2*30 = 60 seconds to calculate the SQL statement. If the number of cycles suddenly increases to 8000 times, 2*8000 = 16000 seconds, which is a few hours. You can imagine that this performance will suddenly drop to an intolerable level!

So how to optimize it? Of course, it is to reduce the number of cycles. After carefully observing the SQL statement and understanding the business logic of the dude who wrote the SQL statement, the above loop processing can be completely replaced with the following SQL statement, and there is no need to record a record for update. At that time, the modified SQL statement was tested and found to run in less than 1 minute.

UPDATE TMP_JO_ORDERS
SET M_BUTTON = MAIN_INTERFACE.GET_MUST_INFO (JOB_ORDER_NO, 'twing', 'two ')
, M_LABEL = MAIN_INTERFACE.GET_MUST_INFO (JOB_ORDER_NO, 'serving', 'label ')
, M_TAPE = MAIN_INTERFACE.GET_MUST_INFO (JOB_ORDER_NO, 'twing', 'two ')
, 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, 'packaging', 'all ')
WHERE SEW_START> = SYSDATE;

 

In fact, this is only a special case. I just use it as an introduction and introduce it to my point of view: We know that SQL is a structured query language and is good at structured query, not good at logic processing (WHIE, IF .. ELSE), but sometimes, many people like to use SQL to process business logic. Of course, it does not mean that some business logic processing cannot be done in stored procedures or functions, I just found that many people excessively enlarge the logic processing function of SQL, and moved all complicated logic operations to the package and stored procedures for processing, such as the above cyclic operations, A bad result of this is performance problems. It is like a person who is good at sprinting. You have to ask him to join the long-distance race. Then the competition results will certainly not be good.

 

During development, we need to optimize the business logic to avoid complicated logic operations, especially the business logic processing with a large number of loops. On the one hand, we need to simplify the business logic, some business logic operations are transferred to the program for processing. On the other hand, we can use SQL to skillfully implement many complicated logic requirements, so that we can avoid doing a lot of complicated logic processing, rather than writing more complex SQL statements in complex services. for example, in the above example, I used to describe the ms SQL challenge in a previous article.