A Way to Improve SQL Execution efficiency by nearly 200 times like change =
Because the company's business needs to change the price, it is best to execute the following script once every 10 minutes on the front-end. It takes about 3 minutes to execute the script and 100% CPU. the user experience is too bad.
Update stk_bar set newretailprice = F2 from stk_bar bar, yhj_ysjgitem item
Where bar. item_code like item. F1 and bar. newretailprice <> item. F2
Later, this behavior affected user usage. Later, I thought of adding a primary key, index, etc. The effect was not satisfactory, and finally I thought of the optimization script:
Update stk_bar set newretailprice = 2 from stk_bar, yhj_ysjgitem item
Where Replace (bar. item_code, '','') = Replace (item. F1, '','') and bar. newretailprice <> item. F2 --- 1 s 60395 row
As a result, the execution can be completed in one second.
I was surprised that the execution efficiency was improved so quickly,
I used to know that like is time-consuming for text traversal, but I didn't expect the effect to be so obvious today.
Share it.