Reputation: 1228
The following statement is executing unexpectedly slowly, the child table does hold a lot of rows but the parent does not, indexing looks good - I think there's a better way to do this.
SELECT P.[ID]
,C1.[MinChildDate]
,C2.[MaxChildDate]
FROM Parent AS P
CROSS APPLY
(
SELECT
MIN([Date]) AS MinChildDate
FROM Child
WHERE Child.ParentID=P.ID
) AS C1
CROSS APPLY
(
SELECT
MAX([Date]) AS MAXChildDate
FROM Child
WHERE Child.ParentID=P.ID
) AS C2
Upvotes: 0
Views: 36
Reputation: 35623
Just use a simple group by query:
SELECT
P.[ID]
, MIN(child.[Date]) AS MinChildDate
, MAX(child.[Date]) AS MAXChildDate
FROM Parent AS P
INNER JOIN Child ON P.ID = Child.ParentID
GROUP BY
P.[ID]
;
Upvotes: 1
Reputation: 945
Remove a CROSS APPLY
to cut the logical reads of the Child
table in half.
SELECT P.[ID]
,C1.[MinChildDate]
,C1.[MaxChildDate]
FROM Parent AS P
CROSS APPLY
(
SELECT
MIN([Date]) AS MinChildDate,
MAX([Date]) AS MAXChildDate
FROM Child
WHERE Child.ParentID=P.ID
) AS C1
Upvotes: 1