** This article is participating in the Java Theme Month – Java Debug Notes Event, see the event link for details **

1. Accident Background

The story begins with a vague demand from a product, which requires payment confirmation, write-off and debit operation for the unconfirmed payment data in the system at 1:00 am every day. It may sound simple, as shown in the figure below

Graph TD timer query configuration items --> traverse configuration items --> find out data to be processed according to configuration items --> confirm collection of data --> cancel processing --> Charge processing --> Write successfully processed data into the log table

Something like that. However, the business complexity is very high, because you do not know the verification of payment confirmation, what is the logic of write-off, what is the logic of charge, so there is a development to see the code, find problems notify the product, product increase demand, development received new demand and then change, alas, it feels like the development is doing the work of the product.

Ii. Accident Scene

Sometimes the most worrying thing is the demand that you do, and the demand that you do not understand, but in this case, the product is to insist that it must be online, something goes wrong, sorry for the development brother, to start your performance. After experiencing some minor problems, suddenly there was a problem that the timer that was supposed to run in the wee hours of the morning didn’t run.

The product was notified to the development at the first time. Why did it not run? The development looked at the log on Kibana at the first time and found that there were 12 pieces of data configured, but they stopped running when they ran to the eighth one

BusinessType 0 import 1 export 2 customs clearance 3 shipping ** @param orgGroupCode * @param businessType * @param paymentType * @param proceedsDate * @return */ @Query(value = "select * from rp_freight where ORG_GROUP_CODE = :orgGroupCode and BUSINESS_TYPE = :businessType " + "and PAYMENT_TYPE =:paymentType and GATHERING_STATUS = 1 and CONFIRM_STATUS ! = 1" + "and to_char(GATHERING_TIME,'yyyy-MM-dd') = :proceedsDate and arap='R' ", nativeQuery = true) List<RpFreight> getAutoConfirmRpFreight(@Param("orgGroupCode") String orgGroupCode, @Param("businessType") Integer businessType, @Param("paymentType") Integer paymentType, @Param("proceedsDate") String proceedsDate);Copy the code

When the SQL corresponding to this code executed the 8th configuration item, due to the large amount of data of the 8th configuration item, the SQL execution lasted for more than 4 minutes, which eventually resulted in the suspended thread and the program was not executed backwards. Let’s take it a step further and see why it’s so slow, and look closely, there’s a function here that converts time, okay

 to_char(GATHERING_TIME,'yyyy-MM-dd') = :proceedsDate 
Copy the code

Using a function to convert a field does not reverse the index, which leads to a full table scan, which is tragic.

3 Solutions

Get rid of the function, pass in the start and end time instead, problem solved

@Query(value = "select * from rp_freight where ORG_GROUP_CODE = :orgGroupCode and BUSINESS_TYPE = :businessType " + "and  PAYMENT_TYPE =:paymentType and COMMITMENT =:commitment and GATHERING_STATUS = 1 and CONFIRM_STATUS ! = 1" + "and GATHERING_TIME >= :getYesterBeginTime and GATHERING_TIME <= :getYesterEndTime and arap='R' ", nativeQuery = true) List<RpFreight> getAutoConfirmRpFreightCm(@Param("orgGroupCode") String orgGroupCode, @Param("businessType") Integer businessType, @Param("paymentType") Integer paymentType, @Param("commitment") String commitment, @Param("getYesterBeginTime") Date getYesterBeginTime, @Param("getYesterEndTime") Date getYesterEndTime );Copy the code