Dataset2 of report SCCM_Report-Site_Discovery.rdl is not efficient, cause timeout issue when there are > 1000 rows

[IvyMS1990]

[Enter feedback here]

Found one performance issue of this report: SCCM_Report-Site_Discovery.rdl. Dataset2 query 2 tables together without join. It cause DB timeout, and report cannot be shown. SELECT COUNT(DISTINCT IESystemInfo.[IEVer0]) AS SystemIEVerCount ,COUNT(DISTINCT IEURLInfo.Domain0) AS DomainCount ,COUNT(DISTINCT IEURLInfo.[URL0]) AS URLCount ,CAST(MIN(IESystemInfo.[IEVer0]) AS INT) AS SystemIEVerMin ,COUNT(DISTINCT IEURLInfo.[ActiveXGUID0]) AS ActiveXGuidCount ,MIN(IEURLInfo.[DocMode0]) AS DocModeMin FROM [dbo].[v_GS_IESYSTEMINFO] AS IESystemInfo, [dbo].[v_GS_IEURLINFO] AS IEURLInfo

It should be updated to below with join. SELECT COUNT(DISTINCT IESystemInfo.[IEVer0]) AS SystemIEVerCount ,COUNT(DISTINCT IEURLInfo.Domain0) AS DomainCount ,COUNT(DISTINCT IEURLInfo.[URL0]) AS URLCount ,CAST(MIN(IESystemInfo.[IEVer0]) AS INT) AS SystemIEVerMin ,COUNT(DISTINCT IEURLInfo.[ActiveXGUID0]) AS ActiveXGuidCount ,MIN(IEURLInfo.[DocMode0]) AS DocModeMin FROM [dbo].[v_GS_IESYSTEMINFO] AS IESystemInfo inner join [dbo].[v_GS_IEURLINFO] AS IEURLInfo on IESystemInfo.resourceID=IEURLInfo.ResourceID

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 2883b89f-a5ec-c549-3a9e-22c010d0b527
• Version Independent ID: 6518eff2-f389-a4c7-0d8d-f3ba47c650cc
• Content: Enterprise Site Discovery Step by Step Guide
• Content Source: edgeenterprise/edge-ie-mode-site-discovery.md
• Product: microsoft-edge
• GitHub Login: @appcompatguy
• Microsoft Alias: collw

[okpedro]

I independently came to the same conclusion. The query as it stands contains a cross-join. Increase in data exponentially aggravates performance. After analyzing the two views being joined I believe this should never have been a cross join. It only makes sense as an INNER JOIN. I assume all the DISTINCT counts are a sloppy way to circumvent the duplicates created by the cross join.