Are duplicate math operations reran in case expressions?-CodePudding

In the example below, does the sql execution do anything to "cache" the results of fuel cost? Or does the math get ran potentially three times? This is not a real use case, I just drummed up this example to mimic a more realistic need.


select case when shipFuelSurcharge   shipCost > 0
                        then 'over'
                    when shipFuelSurcharge   shipCost < 0
                        then 'under'
                    when shipFuelSurcharge   shipCost = 0
                        then 'equals'
            end as ExampleMathCase
    from shippment

CodePudding user response：

The question can't be answered without knowing what the actual query is. The database doesn't execute SQL queries in the way they're written. The query optimizer will simplify and optimize them, then create an execution plan based on the table schemas, indexes and data statistics. The expensive part is reading the data from disk, not calculating simple additions and comparison.

For example, these two queries have the same execution plan and cost the same as far as the query optimizer is concerned:

declare @shippment table(id int primary key,shipFuelSurcharge numeric(18,4), shipCost numeric(18,4))

select case when shipFuelSurcharge   shipCost > 0
                        then 'over'
                    when shipFuelSurcharge   shipCost < 0
                        then 'under'
                    when shipFuelSurcharge   shipCost = 0
                        then 'equals'
            end as ExampleMathCase
from  @shippment;

select case when valuesum > 0
                        then 'over'
                    when valuesum < 0
                        then 'under'
                    when valuesum = 0
                        then 'equals'
            end as ExampleMathCase
from  (select shipFuelSurcharge   shipCost  as valuesum from @shippment) x

The scalar calculation is the same in both cases.

[Expr1003] = Scalar Operator(CASE WHEN ([shipFuelSurcharge] [shipCost])>(0.0000) THEN 'over' 
    ELSE CASE WHEN ([shipFuelSurcharge] [shipCost])<(0.0000) THEN 'under' 
    ELSE CASE WHEN ([shipFuelSurcharge] [shipCost])=(0.0000) THEN 'equals' 
    ELSE NULL END END END)

Execution plans work on streams of rows and precalculating the sum would require another node in the flow. That doesn't mean the actual expression doesn't get simplified either by the engine or the CPU though.

The relative cost for this expression is 0, because the IO cost is far greater than a few float additions and comparison.

Things are different if the actual query uses the operation result for filtering, ordering or grouping. The server can't use any indexes to accelerate the query and has to calculate the expression results first before proceeding.

This query results in a more complex and expensive execution plan :

declare @shippment table(id int primary key,shipFuelSurcharge numeric(18,4), shipCost numeric(18,4))


select ExampleMathCase,count(*)
from (
    select case when shipFuelSurcharge   shipCost > 0
                        then 'over'
                    when shipFuelSurcharge   shipCost < 0
                        then 'under'
                    when shipFuelSurcharge   shipCost = 0
                        then 'equals'
            end as ExampleMathCase
    from  @shippment) xx 
group by ExampleMathCase;


select case when shipFuelSurcharge   shipCost > 0
                        then 'over'
                    when shipFuelSurcharge   shipCost < 0
                        then 'under'
                    when shipFuelSurcharge   shipCost = 0
                        then 'equals'
            end as ExampleMathCase
from  @shippment;

The more complex query is considered almost 5 times more expensive: