Case Clause Execution Procedure

Hi i have a SQL which is extremely slow. select case when (value=1) then (select from table where table.id=table_2.id) else 'false' end from table_2 wher

Solution 1:

Does this solve your problem?

SELECTCOALESCE(<Some Math Logic>, 'false')
FROM table_2 T2
    LEFTJOINtable T
        ON T.Id = T2.Id
           and T2.value =1WHERE<where clause>

Solution 2:

Good question. You need to see the execution plan to know for sure. The database engine is free to use any algorithm it sees fit so long as it gets you the results you asked for.

It could even outer join table to get the results in anticipation of value = 1. Or it could run the select from table and store the results into a temporary table that it can scan when it runs the main query.

Most likely, however, it is running the subquery for every row where value = 1. Hard to tell without seeing the plan.

It also depends on the details of . Are you taking aggregates? If so, a true join may be impossible and it may have to recalculate the answer for every row. If it's looking at values right on the table rows, then it may be able to optimize that away.

If you take the case statement out, does the overall query perform much faster? Want to make sure you are analyzing the correct sub-query.