Too Many Sub Queries Hours To Run

[HulaGirl]

I am new to SQL, can anyone tell me your opinion on the following stored procedure? SQL server takes hours to process this.Thanks so much...



CREATE PROCEDURE dbo.rpt_sp

@StartDate DATETIME
, @EndDate DATETIME
, @ServiceStartDate DATETIME
, @ServiceEndDate DATETIME
, @CataStartAmt INT
, @CataEndAmt INT

AS

--Strip off possible time componenet from parameter values

SELECT @StartDate = CAST(CONVERT(VARCHAR, @StartDate, 101) as DATETIME)
SELECT @EndDate = CAST(CONVERT(VARCHAR, @EndDate, 101) as DATETIME)
SELECT @ServiceStartDate = CAST(CONVERT(VARCHAR, @ServiceStartDate, 101) as
DATETIME)
SELECT @ServiceEndDate = CAST(CONVERT(VARCHAR, @ServiceEndDate, 101) as
DATETIME)

SELECT dbo.DHVedio_getFee(b.GroupID) [Money],

pp2.supID [Network],

e.mbrID [MemberID],
UPPER(m.fullname)[MemberName],
c.transid [transID],
c.startdate [ServiceFrom],
c.enddate [ServiceTo],
c.paiddate [PaymentDate],

-- total paid --line level (all transs in the selected date ranges)
(c.totalpaid-(SELECT SUM(cd3.interest) FROM transdetail cd3 WHERE
cd3.transid = c.transid)) [TotalPaid],

-- total paid --trans level (all transs in the selected date ranges)
(Sub.AcuAmt - Sub2.Interest ) [AcuAmt],

Sub.MultiNetwork,

-- Determine the estimated reimbursement if any
trans
WHEN sub5.minThresholdDate BETWEEN @StartDate AND @EndDate THEN
trans
WHEN (Sub.AcuAmt - Sub2.Interest ) BETWEEN 150000 AND 999999 THEN
ROUND(((Sub.AcuAmt - Sub2.Interest ) - 150000)*0.85,2)
WHEN (Sub.AcuAmt - Sub2.Interest ) >= 1000000 THEN ROUND(722500 +
((Sub.AcuAmt - Sub2.Interest ) - 1000000),2)
END
ELSE
trans
WHEN sub5.maxThresholdAmt BETWEEN 150000 AND 999999 THEN ROUND((Sub.AcuAmt
- Sub2.Interest )*0.85,2)
WHEN sub5.maxThresholdAmt >= 1000000 THEN ROUND((Sub.AcuAmt -
Sub2.Interest ),2)
END
END [EstRemb]

FROM trans c
JOIN supplierview p ON p.supID = c.supID
JOIN SupInfo pp ON (p.supID = pp.supID)
JOIN mbrView m ON m.mbrID = c.mbrID
JOIN mbrship e ON e.mbrShipID = c.mbrShipID
JOIN mbrBenefit b ON (b.GroupID = e.GroupID AND b.programid = pp.programid)

-- Determine the acumulated ttl of totalamt paid to a mbr in the selected
period
JOIN (SELECT Subm.mbrID,
SUM(Subc.totalpaid)[AcuAmt],
trans
WHEN COUNT(DISTINCT Subpp.supID)>1 THEN '*'
ELSE ''
END [MultiNetwork] --To determine if AcuAmt includes multiple networks.
FROM trans Subc JOIN mbrView Subm ON Subm.mbrID = Subc.mbrID
LEFT JOIN mbrRegion Subpcp ON (Subpcp.mbrShipID = Subc.mbrShipID and
Subpcp.pcptype = 'pcp'and Subc.startdate between Subpcp.effdate and
Subpcp.termdate)
LEFT JOIN supplierview Subp ON Subp.supID = Subpcp.networkid
LEFT JOIN SupInfo Subpp ON Subpp.supID = Subp.supID
WHERE Subc.status IN ('PAID', 'REVERSED') AND
(Subc.Startdate BETWEEN @ServiceStartDate AND @ServiceEndDate)
AND (Subc.paiddate BETWEEN @StartDate AND @EndDate)
GROUP BY Subm.mbrID)[Sub] ON Sub.mbrID = m.mbrID


-- Interest per member in the selected period
JOIN (select c4.mbrID, SUM(cd4.interest)[Interest] FROM trans c4 JOIN
transdetail cd4 ON c4.transid = cd4.transid
where (c4.Startdate BETWEEN @ServiceStartDate AND @ServiceEndDate)
AND (c4.paiddate BETWEEN @StartDate AND @EndDate)
group by c4.mbrID) [Sub2] on Sub2.mbrID = m.mbrID

-- Determine the acumulated ttl of totalamt paid to a mbr in the selected
period
JOIN (SELECT t.mbrID
, maxThresholdAmt = MAX(t.runningtotal)--max running total in parameter
range
, minThresholdDate = MIN(t.paiddate)--earliest date a member reaches cata
in parameter range
FROM (SELECT c7.mbrID
, c7.transid
, c7.paiddate
, runningtotal = (SELECT SUM(c8.totalpaid)--totalpaid
FROM (SELECT c9.mbrID
, c9.paiddate
, c9.transid
, totalpaid = c9.totalpaid - SUM(cd9.interest)
FROM trans c9 LEFT join transdetail cd9 ON c9.transid = cd9.transid
GROUP BY c9.mbrID, c9.transid, c9.paiddate, c9.totalpaid ) c8
WHERE c8.paiddate <= c7.paiddate --result will be in date order.
AND c8.mbrID = c7.mbrID)
FROM trans c7
) t
WHERE t.runningtotal BETWEEN @CataStartAmt AND @CataEndAmt
AND t.paiddate <= @EndDate
GROUP BY t.mbrID)[Sub5] ON Sub5.mbrID = m.mbrID

-- get network code
LEFT JOIN mbrRegion pcp ON (pcp.mbrShipID = c.mbrShipID AND pcp.pcptype =
'pcp'AND c.startdate BETWEEN pcp.effdate AND pcp.termdate)
LEFT JOIN supplierview p2 ON p2.supID = pcp.networkid
LEFT JOIN SupInfo pp2 ON pp2.supID = p2.supID

WHERE c.status IN ('PAID', 'REVERSED') AND
(c.Startdate BETWEEN @ServiceStartDate AND @ServiceEndDate)
AND(c.paiddate BETWEEN @StartDate AND @EndDate)


ORDER BY m.mbrID

 

[ClayG]

I too would be a bit wary of so many subqueries, especially where they get 4 levels deep.

If I read &quot;Determine the acumulated ttl of totalamt paid ..&quot; correctly, it calculates a running total so that it can take the Max of it as the final sum. It seems that could be replaced with a simple sum.

I would be very suspicious that the same tables appearing in multiple subqueries means this procedure should be simplified.

I suggest you draw a mini-datamodel (E.R. diagram) of what tables this query is using & their relationships, and write a query for that.

One situation where subqueries are hard to avoid is where you have two tables that are both many-to-1 to a master (looks like mbrship) but not to each other. trans is m:1 with mbrship, if mbrRegion also has many for each mbrship then this is an example, because (it seems) trans does not relate to a region. In such cases you probably do need a subquery on mbrRegion. Its hard to be sure without also knowing the primary keys of the tables.

mbrship -> trans -> transdetail : is a different example of a possible need for subquery, if you need to Sum from trans and also from transdetail. Because, if they were simply joined, multiple transdetail for a trans would cause the value in trans to be duplicated. But if you are not summing from trans, they can be simply joined.

Sometimes its better to create temporary tables (Select Into #tmptable).

 

[HulaGirl]

ClayG,
Thanks.