BigQuery function in CASE optimisation-CodePudding

A question about BigQuery engine and CASE optimization.

On the following query:

SELECT
   CASE WHEN UPPER(name) = 'JOHN' THEN 1
        WHEN UPPER(name) = 'MIKE' THEN 2
        WHEN UPPER(name) = 'RON' THEN 3
        ELSE 4 END AS score
FROM
  table

Is using the function UPPER on each WHEN is going to cost computing power for each WITH? Or the BigQuery engine knows how to optimise it?

CodePudding user response：

to avoid concern - just use

SELECT
  CASE UPPER(name)
    WHEN 'JOHN' THEN 1
    WHEN 'MIKE' THEN 2
    WHEN 'RON' THEN 3
    ELSE 4
  END AS score
FROM
  table

meantime, I would expected BigQuery engine does know how to optimize version in your question it :o)

CodePudding user response：

upper itself will use a trivial amount of CPU. It's a very simple function. How you're using it requires no disk access (see below). Whether or not BigQuery optimizes it to a single upper call or not, it should have little effect on the query performance.

Where upper can get you into trouble is if it is used in a where clause.

select *
from foo
where upper(name) = 'JOHN'

In a traditional SQL database, this query would be unable to use an index on name slowing down the query considerably as it would have to scan the whole table.

However, BigQuery is not a traditional SQL database and that should also perform well.