4

In a large UPDATE statement with several subselects, we have been using a lot of nested IF() statements. I would like to refactor the more complex of these IF() statements into CASE statements primarily to improve readability and lessen the change of coding errors.

So a statement-part like this:

SET cr.Price=IF(cmr.dirty AND rtg.connectRateToMasterRate=0, cr.Price,
                (cmr.price + IF(rtg.RateDeviationType='FIXED_AMOUNT', 
                                rtg.masterRateRateDeviation, cmr.Price * 
                                rtg.masterRateRateDeviation / 100)  
               )) * IF(masterSettings.masterCurrencyConvertActive='TRUE', 
                       cuMaster.AValue / cu.AValue, 1),

Would turn into something like this:

SET
 cr.Price = CASE WHEN cmr.dirty AND rtg.connectRateToMasterRate
                 THEN
                   (cmr.Price +  
                    IF(rtg.RateDeviationType='FIXED_AMOUNT',  
                        rtg.masterRateRateDeviation,  
                        cmr.Price * rtg.masterRateRateDeviation / 100  
                      )  
                   ) * IF(masterSettings.masterCurrencyConvertActive='TRUE',  
                          cuMaster.AValue / cu.AValue,  
                          1  
                         )  
                 ELSE cr.Price  
            END

My question is if such a refactoring would have impact on the performance of the query. Bear in mind that this update query will update thousands of records, so even a small increase could have significant impact.

We are using MySQL 5.6.19 by the way.

Bascy
  • 2,017
  • 1
  • 21
  • 46
  • Neither SO nor the MySQL documentation seems to say much about the performance of the two. However, in both places I saw discussions about which one one should choose, and the usual answer given is whatever makes your code easier to read. I don't expect much of a performance difference either way, assuming the logical flow be identical, but making your code readable also matters. – Tim Biegeleisen Mar 01 '17 at 09:04
  • This might can help you: http://stackoverflow.com/questions/2429226/case-statements-versus-coded-if-statements – Kinchit Dalwani Mar 01 '17 at 16:00
  • The link refers to differences in SQL CASE statement or IF statements in Code handling the resulting data – Bascy Mar 01 '17 at 20:49

1 Answers1

2

The use of the IF as a function may result in a small overhead due to the function call. The only way to tell the difference is to clock the executions in a controlled environment. Still I won't expect big changes, and thousands of records doesn't seem to be that huge nowadays.

Cavaz
  • 2,996
  • 24
  • 38