Convert subquery to JSON performance-CodePudding

I have the following query, which works but takes over 30 mins to run. The initial outer query is pulling back around 1400 rows and the subquery is then pulling back in some cases 100 odd rows.

;WITH products AS (
  SELECT
    PromotionID,
    ProductColourID,
    ProductCode
  FROM dbo.PromotionProducts
)
SELECT
  P.ID,
  P.Code,
  P.Name,
  P.EndDateTime,
  prods = (
    SELECT
      pp.productColourId,
      pp.ProductCode
    FROM products
    FOR JSON PATH
  )
FROM dbo.Promotion P
JOIN products PP ON P.ID = PP.PromotionID
WHERE P.EndDateTime > GETDATE()

Is there anything I can do to speed this up?

CodePudding user response：

Your query looks incorrect, as the subquery is not correlated to the outer query (so you will get the whole table on every outer row), and products is joined a second time unnecessarily.

Instead, just do a single correlated subquery:

SELECT
  P.ID,
  P.Code,
  P.Name,
  P.EndDateTime,
  prods = (
    SELECT
      pp.productColourId,
      pp.ProductCode
    FROM dbo.PromotionProducts PP
    WHERE P.ID = PP.PromotionID
    FOR JSON PATH
  )
FROM dbo.Promotion P
WHERE P.EndDateTime > GETDATE();

For this to be performant, you probably want the following indexes

Promotion (EndDateTime) INCLUDE (ID, Code, Name)
PromotionProducts (PromotionID) INCLUDE (productColourId, ProductCode)