JJ32 JJ32 - 1 month ago 11
SQL Question

Adding criteria in procedure causes poor performance

I have a function fncDeptInfo. It currently returns about 1000 records in under a second:

ALTER FUNCTION [dbo].[fncDeptInfo]()
RETURNS TABLE
AS
RETURN
(
SELECT
tblContacts.Contact,
CASE tblContacts.Parent1
WHEN 1900 THEN 0
WHEN 1901 THEN 1
WHEN 1902 THEN 2
WHEN 1903 THEN 3
WHEN 1904 THEN 4
WHEN 1905 THEN 5
WHEN 1906 THEN 6
ELSE NULL
END AS PRArea,
DISTRICT.Contact AS DistrictID
FROM
tblContacts
LEFT OUTER JOIN
tblContacts AS DISTRICT ON tblContacts.Parent2 = DISTRICT.Contact
WHERE
(tblContacts.ContactType = 'Fire') AND
(tblContacts.SubType = 'Dept')
)


I have a procedure that calls this function below:

SELECT
fncDeptInfo.Contact, DEPTPAID.CurPaid,
fncDeptInfo.PRArea, fncDeptInfo.DistrictID
FROM
fncDeptInfo() AS fncDeptInfo
INNER JOIN
(SELECT
v_Item.BillToContact AS Contact,
SUM(CASE WHEN Expiration = @Date1 AND tblProgramCodes.FormatCode = 'Membership' THEN 1 ELSE 0 END) AS CurPaid
FROM
v_Item
INNER JOIN
tblProgramCodes ON v_Item.ProgramCodeID = tblProgramCodes.ProgramCode
GROUP BY
v_Item.BillToContact) DEPTPAID ON fncDeptInfo.Contact = DEPTPAID.Contact
WHERE
(fncDeptInfo.PRArea > 0) AND (fncDeptInfo.DistrictID > 0)
ORDER BY
fncDeptInfo.Contact


v_Item
is a very complex view that rolls up financial records across many different tables. It returns over 300,000 rows. The procedure as designed returns in 5 seconds.

If I add this piece to get Chief information to fncDeptInfo the procedure takes a minute and a half. But fncDeptInfo on its own still returns in about a second:

LEFT OUTER JOIN fncEmployee(GETDATE(), 'Chief') AS CHIEF
ON tblContacts.Contact = CHIEF.Contact2


If I add this criteria to the procedure it also now takes a minute and a half. If I remove fncDeptInfo from the procedure, however, it again returns in about 5 seconds:

WHERE CurPaid > 0


My guess is that the view is involved somehow in both cases and is repeatedly getting called. Can someone suggest a better way to design this so that performance doesn't suffer?

Answer

A straightforward option is to select your view into a temporary table, which prevents the repeated call of the view. Something like

IF Object_ID ('tempdb..vitem_tmp') is not null DROP TABLE #vitem_tmp

SELECT *
INTO #vitem_tmp
FROM v_Item

[Your query, referencing #vitem_tmp instead of v_Item]