CODEWITHSUNDEEP
sqlt-sql
Sasan
Sasan

Reputation: 614

Better solution for PARTITION BY?

I want to optimize this

WITH a as
(SELECT *
       ,ROW_NUMBER()  OVER (PARTITION BY applicationid ORDER BY AgreementStartDate desc) rn
       ,(select count(*) from RM_TbPackages where d.ApplicationID=ApplicationID) as PackageCount 
 FROM CM_VwSupplierApplications d)
select * from a
where rn=1
order by a.ApplicationID

Upvotes: 0

Views: 107

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44346

This query will run faster since it is not making a subselect for ever row in CM_VwSupplierApplications:

;WITH a AS
(
SELECT * ,ROW_NUMBER()  OVER (PARTITION BY applicationid ORDER BY AgreementStartDate desc) rn
FROM CM_VwSupplierApplications d
)
SELECT a.*, b.PackageCount
FROM a
OUTER APPLY
(  SELECT count(*) PackageCount 
   FROM RM_TbPackages 
   WHERE d.ApplicationID=ApplicationID) b
WHERE a.rn=1
ORDER BY a.ApplicationID

To improve it even more, you could consider index on table CM_VwSupplierApplications on the columns applicationid and AgreementStartDate

Upvotes: 1

StuartLC
StuartLC

Reputation: 107387

As per the comment, there is nothing wrong with the partition. One possible inefficiency is the subquery (select count(*) from RM_TbPackages where d.ApplicationID=ApplicationID) - a set based approach to this by computing all counts per Application and then joining to the count should improve performance:

WITH a as
(
   SELECT * , 
      ROW_NUMBER()  OVER (PARTITION BY applicationid ORDER BY AgreementStartDate desc) rn,
      x.PackageCount
   FROM CM_VwSupplierApplications d
      INNER JOIN 
      (select ApplicationID, count(*) as PackageCount 
         from RM_TbPackages
         group by ApplicationID )x
      on x.ApplicationID = d.ApplicationID
 )
select * from a
where rn=1
order by a.ApplicationID;

Upvotes: 1

Related Questions