how to avoid this subquery

Welcome to the forum!
Have you already tried to use derived tables and join onto them instead of using a subquery? So, basically something like

Code:

SELECT
    sp.Column1,
    k.Column2,
    po.Column3,
    h.Column4,
    c.Column5,
    s.Column6,
    wt.Column7,
    wt8.Column8
FROM 
    dbo.Table2 sp
    JOIN 
    dbo.Table2 wt ON sp.ColumnA = wt.ColumnA
    JOIN 
    dbo.Table3 k ON wt.ColumnZ = k.ColumnZ
    JOIN 
    dbo.Table4 pod ON pod.ColumnY = wt.ColumnY
    JOIN 
    dbo.Table5 po ON po.ColumnX = pod.ColumnX
    JOIN 
    dbo.Table6 h ON h.ColumnW = wt.ColumnW
    LEFT JOIN 
    dbo.Table7 c ON h.ColumnM = c.ColumnM
    LEFT JOIN 
    dbo.Table8 s ON s.ColumnN = c.ColumnN 
    JOIN
    (SELECT 
        btr.ColumnA, COUNT(btr.Column8) AS Column8
    FROM 
        dbo.Table1 btr
    WHERE 
        btr.ColumnB = 2 AND 
        btr.ColumnD = 1 AND 
        btr.ColumnE = 1 AND 
        btr.ColumnC = 0) wt8 ON wt.ColumnA = wt8.ColumnA

First Welcome to Forums,

For that query , we round first to index optimization next:

· Start read of Query Execution plan either estimated one or actual

· Then try to approach much index seek besides of the least I/O + CPU cost < 1 and much preferred to be < 0.1 as possible by following the next:

1. Avoiding any table scans thereby assuring clustered indexes are there at least which they should be if PKs are there

2. Implementing different compound covered indexes for those sub queries through :

· Putting all Where+ Join columns in key part by the same order (almost of cases)

· Putting all other select columns in include part

3. Implementing compression technology for indexes and particularly more page compression.

4. Implementing specific Filtered indexing according to parameters values companied with each sub query

5. Online index rebuild should be scheduled well + the same also for Update statistics.

Second , we would round to Locks /Deadlocks:

Perhaps might be there frequent locks delay if those queries are used frequently , thereby:

· Assure using with (nolock)hints for all select statements if it is applicable from business perspective (Almost applicable for such pure select statements)

· If not , please apply read committed snapshot isolation level using row versioning on DB level.

I do agree with Preethi about such coding algorithm of Case when , but I do believe the below code posts more precise statistics , :

SELECT sp.[Column1]

,k.[Column2]

,po.[Column3]

,h.[Column4]

,c.[Column5]

,s.[Column6]

,wt.[Column7]

,sum ( case when btr.Column8 is not null and btr.[ColumnB] = 2 and btr.[ColumnD] = 1 AND btr.[ColumnE] = 1 AND btr.[ColumnC] = 0 then 1 else 0 end)as column8
,sum ( case when btr.Column8 is not null and btr.[ColumnB] = 1 and btr.[ColumnD] = 1 AND btr.[ColumnE] = 1 AND btr.[ColumnC] = 0 then 1 else 0 end)as column9

,sum ( case when btr.Column8 is not null and btr.[ColumnB] = 3 and btr.[ColumnD] = 1 AND btr.[ColumnE] = 1 AND btr.[ColumnC] = 0 then 1 else 0 end)as column9

,sum ( case when btr.Column8 is not null and btr.[ColumnB] = 4 and btr.[ColumnD] = 1 AND btr.[ColumnE] = 1 AND btr.[ColumnC] = 0 then 1 else 0 end)as column9

FROM [dbo].[Table2] sp INNER JOIN [dbo].[Table2] wt ON sp.[ColumnA] = wt.[ColumnA]

INNER JOIN [dbo].[Table3] k ON wt.[ColumnZ] = k.[ColumnZ]

INNER JOIN [dbo].[Table4] pod ON pod.[ColumnY] = wt.[ColumnY]

INNER JOIN [dbo].[Table5] po ON po.[ColumnX] = pod.[ColumnX]

INNER JOIN [dbo].[Table6] h ON h.[ColumnW] = wt.[ColumnW]

LEFT JOIN [dbo].[Table7] c ON h.[ColumnM] = c.[ColumnM]

LEFT JOIN [dbo].[Table8] s ON s.[ColumnN] = c.[ColumnN]

INNER JOIN [TABLE1] btr on wt.[ColumnA] = btr.[ColumnA]

Since it would get sum of all data entity sticked with the overall condition for any count:

btr.Column8 is not null and btr.[ColumnD] = 1 AND btr.[ColumnE] = 1 AND btr.[ColumnC] = 0 and btr.[ColumnB] = X

But eventually , we have to jump to indexing enhancements part not to subject the query to any catastrophic performance depredations within business growth

Thereby, we have 2 choices either to have :

1- One filtered index with page compression to afford more business growth as next :

Createnonclusteredindex [TABLE1_index1] on [TABLE1]

([ColumnA] asc, [ColumnB]asc, [Columnc]asc, [Columnd]asc, [Columnd]as)

Include(Column8)WHERE ([ColumnD] = 1 AND [ColumnE] = 1 [ColumnC] = 0 )

2- 4 filtered indexes with page compression also to fit up with the 4 business cases there like below

Createnonclusteredindex [TABLE1_indexX] on [TABLE1]

([ColumnA] asc, [ColumnB]asc, [Columnc]asc, [Columnd]asc, [Columnd]as)

Include(Column8)WHERE ([ColumnD] = 1 AND [ColumnE] = 1 [ColumnC] = 0 and [ColumnB] =X)

BTW, if quite much data entity is there , you could use the attached one which is much faster :