CODEWITHSUNDEEP
mysqlsql
Bascy
Bascy

Reputation: 2089

Is there a performance difference in using IF() or CASE

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.

Upvotes: 6

Views: 6253

Answers (1)

Cavaz
Cavaz

Reputation: 3119

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.

Upvotes: 4

Related Questions