SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Added a SELECT inside INNER JOIN. Very Slow Performance


Added a SELECT inside INNER JOIN. Very Slow Performance

Author
Message
comic_rage
comic_rage
Old Hand
Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)Old Hand (392 reputation)

Group: General Forum Members
Points: 392 Visits: 29
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



John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)SSC Guru (130K reputation)

Group: General Forum Members
Points: 130811 Visits: 19092
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
Luis Cazares
Luis Cazares
SSC Guru
SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)SSC Guru (160K reputation)

Group: General Forum Members
Points: 160689 Visits: 22697
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search