Adding criteria in procedure causes poor performance

Question

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?

Take a look at the execution plan if you execute the query standalone in SSMS. Perhaps it will give you a hint as what indices you might be missing. — TT., Oct 21 '16 at 18:39
SSMS does tell me to create a nonclustered index. Is adjusting indexes generally the approach to solving this sort of situation? — JJ32, Oct 21 '16 at 18:41
It usually is. It is actually up to the database designer to add indices to speed up specific queries (or subqueries, derived tables). If the execution plan in SSMS tells you an index is missing, when you add that index it will probably speed up your query. — TT., Oct 21 '16 at 18:45
I tried adding the index it suggested but it was not effective against WHERE (PRArea > 0) AND (DistrictID > 0) AND (CurPaid > 0). It makes no further suggestions. PRArea and DistrictID are defined in the function. I suspect this is part of the problem. Any ideas, or is just a matter of further playing around with indexing? — JJ32, Oct 21 '16 at 18:50
Those functions are table valued functions? Are they inline table valued functions? Could you add the definitions of those to your question? — TT., Oct 21 '16 at 18:53
The DTA will suggest some truly awful indexes about as frequently as it suggests ones that will help. I have seen plenty of cases where the suggested index makes things slower. Your itvf doesn't look like an issue. Perhaps it is the view that is slow? Seeing an actual execution plan would be needed to trouble shoot this much further. — Sean Lange, Oct 21 '16 at 19:00
The view is very complex, and while SSMS does not suggest adding indexes I have no confidence it optimized as much as possible. That said the view performs very well on its own. This is the first time I've come up against an issue possibly involving it. — JJ32, Oct 21 '16 at 19:04
Well, the derived table using a view (which acts a bit like a derived table) problem. It makes it harder for the query optimizer to do its work. Do you really need the view? You can't see what parts from what tables you need and simplify the query that way? — TT., Oct 21 '16 at 19:13
I also need a total paid column, which is an end result from the view (I haven't shown it here for purposes of showing a minimal problem). If it weren't for that I could link in the base tables instead. — JJ32, Oct 21 '16 at 19:20
I've seen this before with derived table within derived table sitations... the query optimizer can't handle it anymore and comes up with an inadequate execution plan. You'll have to go back to the drawing board and come up with a different way of formulating your query. Alternatively, you can substitute all views and table functions you use with the actual queries they produce and see if you can fiddle with the query to get it to perform better (i.e. strip what isn't needed from them, analyze execution plan for better indices etc..) — TT., Oct 21 '16 at 19:25

score 1 · Accepted Answer · answered Oct 21 '16 at 19:21

1

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]

answered Oct 21 '16 at 19:21

Rominus

1,181
2
14
29

That could work. Also materialized views could be of benefit here (see [here](http://stackoverflow.com/q/3986366/243373) on SO). – TT. Oct 21 '16 at 19:32
v_Item is a bit large, I worry about overhead. However converting fncDeptInfo into a table variable first helped. Thanks for the suggestion. – JJ32 Oct 21 '16 at 20:32

Adding criteria in procedure causes poor performance

1 Answers1