SQL Server Performance

how to avoid this subquery

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by Lakshmikanthan V.R, Sep 27, 2011.

  1. Lakshmikanthan V.R New Member

    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]
  2. FrankKalis Moderator

    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
  3. Shehap MVP, MCTS, MCITP SQL Server

    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.
  4. Lakshmikanthan V.R New Member

    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
  5. preethi Member

    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]
  6. Shehap MVP, MCTS, MCITP SQL Server

    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)
  7. Shehap MVP, MCTS, MCITP SQL Server

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

    Attached Files:

Share This Page