December 27, 2017 at 5:59 am
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
December 27, 2017 at 6:17 am
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
December 27, 2017 at 9:14 am
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;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply