Added a SELECT inside INNER JOIN. Very Slow Performance

  • HI,

    I have a sql and added another SELECT inside an INNER JOIN. It is executing very slow now. How can I fix this? Thanks.
    Based on IF a value is null, I want to select IncidentReportIDs OR BlockedIncidentReportID. I tried adding a case when statement inside the
    inner join, but I am sure how to. I figure it will either run one SELECT or the other but not both. Any help is appreaciated.

     SELECT

    case when ICB.IncidentCompanyBlockedId IS NULL then IncidentReportIDs else BlockedIncidentReportID END as IncidentReportIDs,

    FROM

    cmp.NotificationProduct NP

    INNER JOIN(SELECT DISTINCT A.CompanyNotificationId,

    (SELECT DISTINCT IR.IncidentReportId, IR.DocumentNumber, IR.ReceivedDate

    FROM cmp.NotificationProduct NP

    INNER JOIN irp.IncidentProduct IP ON NP.IncidentProductId = IP.IncidentProductId

    INNER JOIN irp.IncidentReport IR ON IR.IncidentReportId = IP.IncidentReportId

    LEFT JOIN irp.IncidentCompanyBlocked ICB ON IR.IncidentReportId = ICB.IncidentReportId

    WHERE A.CompanyNotificationId = NP.CompanyNotificationId

    AND ICB.IncidentReportId is null

    AND NP.CompanyNotificationId IS NOT NULL FOR XML PATH('IncidentReport'), root('List')

    ) AS 'IncidentReportIDs',

    (SELECT DISTINCT IR.IncidentReportId, IR.DocumentNumber, IR.ReceivedDate

    FROM cmp.NotificationProduct NP

    INNER JOIN irp.IncidentProduct IP ON NP.IncidentProductId = IP.IncidentProductId

    INNER JOIN irp.IncidentReport IR ON IR.IncidentReportId = IP.IncidentReportId

    LEFT JOIN irp.IncidentCompanyBlocked ICB ON IR.IncidentReportId = ICB.IncidentReportId

    WHERE A.CompanyNotificationId = NP.CompanyNotificationId

    AND ICB.IncidentReportId is not null

    AND NP.CompanyNotificationId IS NOT NULL FOR XML PATH('IncidentReport'), root('List')

    ) AS 'BlockedIncidentReportID'


    FROM cmp.CompanyNotification A

    ) E ON NP.CompanyNotificationId = E.CompanyNotificationId

  • I'm guessing the two correlated subqueries are slowing you down.  Each one is executed once for each row in your result set.  Please post your execution plan for help with a performance issue.  If you need assistance in rewriting the query, please post table DDL in the form of CREATE TABLE statements and sample data in the form of INSERT statements.

    John

  • You could try to change the DISTINCT with GROUP BY. That might force it to eliminate duplicates before creating the XMLs (although, it doesn't seem like you need the DISTINCT at all).
    The query is not complete, but I would suggest that you also try something like this:

    SELECT IncidentReportIDs
    FROM cmp.NotificationProduct NP

    INNER JOIN(SELECT
          A.CompanyNotificationId,

          (SELECT DISTINCT IR.IncidentReportId, IR.DocumentNumber, IR.ReceivedDate
          FROM cmp.NotificationProduct NP
          INNER JOIN irp.IncidentProduct IP ON NP.IncidentProductId = IP.IncidentProductId
          INNER JOIN irp.IncidentReport IR ON IR.IncidentReportId = IP.IncidentReportId
          LEFT JOIN irp.IncidentCompanyBlocked ICB ON IR.IncidentReportId = ICB.IncidentReportId
          WHERE A.CompanyNotificationId = NP.CompanyNotificationId
          AND ICB.IncidentReportId is null
          AND NP.CompanyNotificationId IS NOT NULL
          FOR XML PATH('IncidentReport'), root('List')) AS 'IncidentReportIDs'
       FROM cmp.CompanyNotification A
       GROUP BY A.CompanyNotificationId
    ) E ON NP.CompanyNotificationId = E.CompanyNotificationId
    WHERE ICB.IncidentCompanyBlockedId IS NULL
    UNION ALL
    SELECT BlockedIncidentReportID
    FROM cmp.NotificationProduct NP
    INNER JOIN(SELECT
          A.CompanyNotificationId,
          (SELECT DISTINCT IR.IncidentReportId, IR.DocumentNumber, IR.ReceivedDate
          FROM cmp.NotificationProduct NP
          INNER JOIN irp.IncidentProduct IP ON NP.IncidentProductId = IP.IncidentProductId
          INNER JOIN irp.IncidentReport IR ON IR.IncidentReportId = IP.IncidentReportId
          LEFT JOIN irp.IncidentCompanyBlocked ICB ON IR.IncidentReportId = ICB.IncidentReportId
          WHERE A.CompanyNotificationId = NP.CompanyNotificationId
          AND ICB.IncidentReportId is not null
          AND NP.CompanyNotificationId IS NOT NULL
          FOR XML PATH('IncidentReport'), root('List')) AS 'BlockedIncidentReportID'
       FROM cmp.CompanyNotification A
       GROUP BY A.CompanyNotificationId

    ) E ON NP.CompanyNotificationId = E.CompanyNotificationId
    WHERE ICB.IncidentCompanyBlockedId IS NOT NULL;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply