TysonLPrice
Programmer
I inherited this ugly beast...I'm wondering if someone can see anything obvious to speed it up. For example I changed the "Case select count > 0" part to check where exists select top 1. That made it worse and I backed it off. I took the order by out and tried to sort it in the application but it runs out of memory. Just anything obvious, I really don't expect anyone to delve into it.
Code:
SELECT Replicate('0', 8 - Datalength(CONVERT(VARCHAR, e.PolicyNO))) +
CONVERT(VARCHAR, e.policyno) AS PolicyNumber,
e.LegalName,
Isnull(e.AssocMbrID, '') AS AssocMbrID,
ep.manual,
BaseRate = (SELECT BaseRate
FROM Manual m
WHERE m.Manual = ep.Manual
AND m.ManualType = ep.ManualType
AND m.EstExperienceYEar = ep.EstExperienceYEar),
ep.SemiPay1,
ep.SemiPay2,
ep.INdividualRate / 100 AS IndividualRate,
ep.IndividualAdmin / 100 AS IndividualAdmin,
d.DWRFRate1,
ep.DWRF2Rate,
ep.IndividualPremium,
gr.INdividualTMPercent,
g.TM AS GroupTM,
Isnull(a2.Name, a.Name) AS AssociationName,
CASE
WHEN asa.fkAssociation = Isnull(ag.fkAssociation, asa.fkAssociation)
THEN
Isnull(a2.Name, a.Name)
ELSE a.Name
END AS AffiliateName,
CASE
WHEN asa.fkAssociation = Isnull(ag.fkAssociation, asa.fkAssociation)
THEN
Isnull(a2.PolicyNo_Assoc, a.PolicyNo_Assoc)
ELSE a.PolicyNo_Assoc
END AS AffiliatePolicyNo,
vngp.NonGroupPremium,
s.Name AS SubAssociationName,
d.BwcFeeRate,
GroupNumber = (SELECT GroupNumber
FROM refGroup rg
WHERE pkrefgroup = fkrefgroup),
GroupName = (SELECT GroupName
FROM refGroup rg
WHERE pkrefGroup = fkrefGroup),
gr.EstExperienceYear AS EstExperienceYear,
e.PolicyNo,
c.Name AS ContactName,
Isnull(c.Email, '') AS ContactEmail,
l.Address1,
l.Address2,
l.City,
l.State,
l.Zip,
c.Phone,
Isnull(c.fax, l.fax) AS Fax,
g.AveSavingsPercent,
g.GroupPayroll,
g.GroupSavings,
g.Pooled,
gr.BaseRatedPremium,
gr.NonGroupFee,
ge.*,
'AccountantName' = CASE
WHEN active = 0 THEN ''
WHEN active = 1 THEN acc.Name
END,
'CMIRepName' = CASE
WHEN isactive = 0 THEN ''
WHEN isactive = 1 THEN cmi.CMIRepName
END,
NewExisting = CASE
WHEN ge.NewToGroup = 2 THEN 'Existing'
ELSE 'New'
END,
c.pkContact,
gr.GroupRatingId,
a.pkAssociation,
s.pkSubAssociation,
e.EmployerId,
rapp.Status AS FutureStatus,
res.Description AS CurrentStatus,
Isnull(g.TMForFee, 0) AS TMForFee,
asa.LogoCode,
Isnull(a.ContractStartDate, '1901-01-01') AS ContractStartDate,
Isnull(g.RetroSavingsPct, 0) AS RetroSavingsPct,
Isnull(gr.StandardPremium, 0) AS StandardPremium,
Isnull(amd.DuesPaid, '') AS DuesPaid,
SendEmailTo=CASE
WHEN (SELECT Count(ac.donotemail)
FROM AccountsEmployer ae2
JOIN AccountsContact ac
ON ac.fkEmployer = ae2.pkEmployer
AND ac.active = 1
AND donotemail = 1
WHERE ae2.PolicyNumber =
CONVERT(VARCHAR, e.policyno) + '-'
+
CONVERT(VARCHAR, e.busseqno)) > 0
THEN 'N'
ELSE 'Y'
END,
SendFaxTo=CASE
WHEN (SELECT Count(ac.donotfaxto)
FROM AccountsEmployer ae2
JOIN AccountsContact ac
ON ac.fkEmployer = ae2.pkEmployer
AND ac.active = 1
AND donotfaxto = 1
WHERE ae2.PolicyNumber = CONVERT(VARCHAR, e.policyno)
+ '-' +
CONVERT(VARCHAR, e.busseqno))
> 0
THEN 'N'
ELSE 'Y'
END,
Isnull(gr.QStdPrem, 0) AS QStdPrem,
Isnull(vngp.QIndPremium, 0) AS QIndPrem,
rasa.Description AS 'ASAStatus',
ae.TPATerminationDate AS 'TerminationDate',
Isnull(acc.SendAllMaterialsTo, 0) AS SendAllMaterialsTo,
pkRejectionreason = Isnull((SELECT pkRejectionreason
FROM refrejectionreason
WHERE description = ge.rejectionreason), 0),
e.ActuarialOrFullService,
rs.description AS RetroStatus,
e.emailnotifications,
e.PolicyNo AS XXPolicyNo
FROM Employers e
JOIN GroupEmployer ge
ON e.EmployerId = ge.fkEmployer
JOIN GroupRating gr
ON e.PolicyNo = gr.PolicyNo
AND e.BusSeqNo = gr.BusSeqNo
AND e.fkJurisdiction = gr.fkJurisdiction
AND gr.fkJurisdiction = 1
LEFT OUTER JOIN EmployerPayroll ep
ON gr.PolicyNo = ep.PolicyNo
AND gr.BusSeqNo = ep.BusSeqNo
AND gr.EstExperienceYear = ep.EstExperienceYear
AND ep.ManualType = 'RN'
JOIN Defaults d
ON gr.EstExperienceYear = d.EstExperienceYear
AND e.PublicEmployer = d.PublicEmployer
LEFT OUTER JOIN Accountant acc
ON e.fkAccountant = acc.pkAccountant
LEFT OUTER JOIN refAppStatus rapp
ON ge.fkAppStatus = rapp.pkAppStatus
LEFT OUTER JOIN refEmployerStatus res
ON e.fkEmployerStatus = res.pkEmployerStatus
JOIN v_NonGroupPremium vngp
ON gr.PolicyNo = vngp.PolicyNo
AND gr.BusSeqNo = vngp.BusSeqNo
AND gr.EstExperienceYear = vngp.EstExperienceYear
LEFT OUTER JOIN Groups g
ON ge.fkGroup = g.pkGroup
LEFT OUTER JOIN refGroup rg
ON g.fkrefGroup = rg.pkrefGroup
LEFT OUTER JOIN AssociationGroups ag
ON rg.GroupNumber = ag.GroupNumber
AND gr.EstExperienceYear = ag.EstExpYear
LEFT JOIN Association a2
ON ag.fkAssociation = a2.pkAssociation
JOIN AssociationSubAssociation asa
ON ge.fkAssociationSubAssociation = asa.pkAssociationSubAssociation
JOIN Association a
ON asa.fkAssociation = a.pkAssociation
JOIN SubAssociation s
ON asa.fkSubAssociation = s.pkSubAssociation
JOIN Location l
ON e.EmployerId = l.fkId
AND l.fkType = 'e'
AND l.PrimaryLocation = 1
LEFT OUTER JOIN Contact c
ON l.pkLocation = c.fkLocation
AND c.PrimaryContact = 1
LEFT OUTER JOIN refCMIRep cmi
ON e.fkCMIRep = cmi.pkCMIRep
LEFT JOIN AssocMemberDues amd WITH (nolock)
ON amd.PolicyNo = e.PolicyNo
AND amd.EstExperienceYear = asa.EstExperienceYear
LEFT JOIN refASAStatus rasa
ON asa.fkASAStatus = rasa.pkASAStatus
LEFT JOIN accountsemployer ae
ON ae.policynumber = CONVERT(VARCHAR, e.policyno) + '-' +
CONVERT(VARCHAR, e.busseqno)
LEFT OUTER JOIN refRejectionReason rr (nolock)
ON rr.description = ge.rejectionreason
LEFT JOIN accounts.dbo.v_EmployersWithTrueConsortiums v_c (nolock)
ON v_c.pkemployer = e.employerid
LEFT JOIN refRetroStatus rs(nolock)
ON gr.fkrefRetroStatus = pkrefretrostatus
WHERE asa.EstExperienceYear = '2011'
AND gr.EstExperienceYEar = '2011'
AND e.PublicEmployer = 0
AND Isnull(asa.RetroGroup, 0) = 0
ORDER BY policynumber,
a.name,
ep.manual
