how to avoid this subquery | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

how to avoid this subquery

Sub query is making complete operation too slow. How can i make to perform better? in sub query i am interested in count of rows for specified condition. SELECT sp.[Column1] ,k.[Column2] ,po.[Column3] ,h.[Column4] ,c.[Column5] ,s.[Column6] ,wt.[Column7] ,(SELECT COUNT(btr.Column8) FROM [dbo].[Table1] btr WHERE wt.[ColumnA] = btr.[ColumnA] AND btr.[ColumnB] = 2 AND btr.[ColumnD] = 1 AND btr.[ColumnE] = 1 AND btr.[ColumnC] = 0 ,(SELECT COUNT(btr.Column9) FROM [dbo].[Table1] btr WHERE wt.[ColumnA] = btr.[ColumnA] AND btr.[ColumnB] = 1 AND btr.[ColumnD] = 1 AND btr.[ColumnE] = 1 AND btr.[ColumnC] = 0 ,(SELECT COUNT(btr.Column10) FROM [dbo].[Table1] btr WHERE wt.[ColumnA] = btr.[ColumnA] AND btr.[ColumnB] = 3 AND btr.[ColumnD] = 1 AND btr.[ColumnE] = 1 AND btr.[ColumnC] = 0 ,(SELECT COUNT(btr.Column11) FROM [dbo].[Table1] btr WHERE wt.[ColumnA] = btr.[ColumnA] AND btr.[ColumnB] = 4 AND btr.[ColumnD] = 1 AND btr.[ColumnE] = 1 AND btr.[ColumnC] = 0 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]
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.
Thanks dude for your greeting and to workable solution, this solves my purpose. This is half of my entire situation i have. I will be happy if i can remove Distinct from other half situation i have. What should i do to achieve result. I am adding case statement to earlier query we discussed. Consider this data is outcome of case added query.
Query Output
Column1,Column2,Column3,Column4,Column5,Column7,T1CountOne,T1CountTwo,T2CountOne,T2CountTwo
1,1,xyz,abc,mnp,ijk,12,34,NULL,NULL
2,2,xyz,abc,mnp,ijk,NULL,NULL,124,45
3,1,hij,qrz,lhn,dio,93,9,NULL,NULL
4,2,oinl,sdio,lzdkj,dio,NULL,NULL,13,89
5,2,xyz,abc,mnp,ijk,NULL,NULL,39,40 Expected Output
Column1,Column3,Column4,Column5,Column7,T1CountOne,T1CountTwo,T2CountOne,T2CountTwo
1,xyz,abc,mnp,ijk,12,34,163,85
2,hij,qrz,lhn,dio,93,9,NULL,NULL
3,oinl,sdio,lzdkj,dio,NULL,NULL,13,89
Code:
SELECT
    sp.Column1,
    k.Column2,
    po.Column3,
    h.Column4,
    c.Column5,
    s.Column6,
    Case Column2 = 1 Then wt8.CountOne End,
    Case Column2 = 1 Then wt9.CountTwo End,
    Case Column2 = 2 Then wt8.CountOne End,
    Case Column2 = 2 Then wt9.CountTwo End,
    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 CountOne
    FROM
        dbo.Table1 btr
    WHERE
        btr.ColumnE = 1 AND
        btr.ColumnC = 0) wt8 ON wt.ColumnA = wt8.ColumnA
    JOIN
    (SELECT
        btr.ColumnA, COUNT(btr.Column8) AS CountTwo
    FROM
        dbo.Table1 btr
    WHERE
        btr.ColumnE = 1 AND
        btr.ColumnC = 0) wt9 ON wt.ColumnA = wt8.ColumnA

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

Copy this to CSV file to see clean formatting
Row 1, 2, 5 clubbed to single row because column 3, 4, 5, 7 is same
As Row 2 & 5 has column2 also same T2CountOne and T2CountTwo is added in output
try this one:
SELECT sp.[Column1]
,k.[Column2]
,po.[Column3]
,h.[Column4]
,c.[Column5]
,s.[Column6]
,wt.[Column7]
,Column8
,Column9
,Column10
,Column11
FROM [dbo].[Table2] sp
INNER JOIN [dbo].[Table2] wt
ON sp.[ColumnA] = wt.[ColumnA]
INNER JOIN(SELECT [ColumnA],
SUM(CASE WHEN [ColumnB] = 2 AND Column8 IS NOT NULL THEN 1 ELSE 0 END) Column8,
SUM(CASE WHEN [ColumnB] = 1 AND Column9 IS NOT NULL THEN 1 ELSE 0 END) Column9,
SUM(CASE WHEN [ColumnB] = 3 AND Column10 IS NOT NULL THEN 1 ELSE 0 END) Column10,
SUM(CASE WHEN [ColumnB] = 4 AND Column11 IS NOT NULL THEN 1 ELSE 0 END) Column11
FROM [dbo].[Table1]
WHERE btr.[ColumnD] = 1
AND btr.[ColumnE] = 1
AND btr.[ColumnC] = 0
GROUP BY [ColumnA])A
ON wt.[ColumnA] = a.[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]
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 :
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |