CODEWITHSUNDEEP
sqlsql-server-2008optimization
baileyswalk
baileyswalk

Reputation: 1228

SQL Cross apply statement performing poorly

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

Answers (2)

Paul Maxwell
Paul Maxwell

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

Jason
Jason

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

Related Questions