Table being scanned multiple times for select/count-CodePudding

I inherited some un-optimized SQL Server queries. There is a lot going on above this code sample, but this is where 75% of the query execution is coming from. Because in each of these select/counts, table 1 is being scanned. Is there a way to get table 1 scanned for everything in one go. Notice were counting based off of column3, but the values in the where clause are coming from column4. This makes it a little more tricky than other solutions I have seen.

SELECT 
    table1.column1,
    table1.column2
    (SELECT COUNT (DISTINCT table1.column3)
     FROM table1
     WHERE table1.column4 = 'value') AS 'count of values'
    (SELECT COUNT (DISTINCT table1.column3)
     FROM table1
     WHERE table1.column4 = 'value2') AS 'another count of different values'
FROM 
    table1
WHERE 
    table1.column55 IN ('random value')

This goes on for about 6 more times. Meaning there are about 6 more SELECT COUNTs than shown, but the format is the same.

CodePudding user response：

Your code is obfuscated so much it is kind of hard to tell what is going on but I think something like this should be close. It will at least only query table1 a single time instead of over and over.

with MyCounts as
(
    select Value1 = count(distinct case when table1.column4 = 'value' then table1.column3 end)
        , Value2 = count(distinct case when table1.column4 = 'value2' then table1.column3 end)
    from table1
)

select t.*
    , c.Value1
    , c.Value2
from table1 t
cross join MyCounts c