How to optimize a query with the same subquery twice?

Question

I am trying to improve query performance, the query structure is as follows:

select 'Total_amount', (select SUM(Total) from dbo.Invoices i where i.BU = bun.BU), case when (select SUM(Total) from dbo.Invoices i where i.BU = bun.BU) > 100000 then 'Good' else 'Not good' end from dbo.BusinessUnits bun 

I know that this example can be solved with the help of unions, but for my real requests I need subqueries. As you can see, I have the same subquery twice, one to give the actual value, and the other to calculate the state.

Is there a way to improve performance by simply calculating the subquery once?

Answer 1

You can try using OUTER APPLY as follows: -

 select 'Total_amount', SumTotal, case when SumTotal > 100000 then 'Good' else 'Not good' end from dbo.BusinessUnits bun OUTER APPLY (select SUM(Total) from dbo.Invoices i where i.BU = bun.BU) CA(SumTotal) 

SPECIAL THANKS FOR MARTIN SMITH for indicating that !!

Answer 2

Another option using WITH

 WITH total_table( 'Total_amount' ) AS ( SELECT SUM(Total) FROM Invoices INNER JOIN BusinessUnits ON (Invoices.BU = BusinessUnits.BU ) ) SELECT CASE WHEN Total_amount > 100000 then 'Good' ELSE 'Not good' END FROM total_table