Function in WHERE clause - dealing with poor performance

Question

I have a large table with over 100000 records.

I need to add one more condition in the where clause used to evalute the totalSum and return only records with the totalSum <> 0.

Ton of joins and temp tabes is used here and I did not intend to post it all.

Here is my function:

CREATE FUNCTION returnTotalSum(@clientID VARCHAR(20),@type INT,@currency VARCHAR(20),@date VARCHAR())

RETURNS INT
AS
BEGIN

DECLARE @totalSum BIGINT;

SET @totalSum = ( SELECT SUM (CONVERT(DECIMAL(18,4),P.iznos*(1-P.dp)/2))
                   FROM pts as p
                     INNER JOIN tippart t on p.tip = @type
                     INNER JOIN its i on p.partija = @clientID                      
                        WHERE p.currency = @currency and pts.dknizenja < @date
                GROUP BY p.partija )


RETURN @totalSum
END

I use it here:(last AND in the WHERE clause)

... 
880001,
NULL, 
NULL,
NULL, 
NULL,
CONVERT(INT,REPLACE('2017.12.31','.',''))                 
            FROM ITS I WITH(NOLOCK)      
              JOIN TIPPART R WITH(NOLOCK) ON I.TIP = R.TIP       
              LEFT JOIN #UNPVT_TIPSTOPE_TS T (NOLOCK) ON I.KAMGRUPA = T.GRUPA AND I.TIP = T.TIP AND I.VALUTA = T.SIFVAL   
              LEFT JOIN #UNPVT_TIPSTOPE_TS T1 (NOLOCK) ON I.KAMGRUPA = T1.GRUPA AND I.TIP = T1.TIP AND I.VALUTA = T1.SIFVAL AND T.GRUPA IS NULL         
              LEFT JOIN #TMP_DODATNA_KS DS (NOLOCK) ON I.PARTIJA = DS.PARTIJA AND I.VALUTA = DS.SIFVAL AND I.KAMGRUPA = DS.GRUPA
              LEFT JOIN #NML_RATE N (NOLOCK)  ON I.TIP = N.TIP AND N.SIFVAL = I.VALUTA AND N.GRUPA = I.KAMGRUPA
             -- LEFT JOIN TIPSTOPE TS (NOLOCK) ON I.TIP = TS.TIP AND TS.GRUPA = I.KAMGRUPA AND TS.SIFVAL = I.VALUTA
              LEFT JOIN #NML_RATE_PERIOD NML (NOLOCK) ON I.TIP = NML.TIP AND I.VALUTA = NML.SIFVAL AND NML.GRUPA = I.KAMGRUPA AND NML.SIFVAL = I.VALUTA
             --WHERE NOT EXISTS (SELECT * FROM [dbo].[IC_INPT_AR_X_INT_RATE_SNPST] WHERE PARTIJA = I.PARTIJA AND VALUTA = I.VALUTA AND APP = 'ST')  
                  WHERE I.DOTVARANJE <= '2017.12.31'
                  AND (T.TIP IS NOT NULL 
                        OR T1.TIP IS NOT NULL 
                        OR DS.PARTIJA IS NOT NULL)
                        AND dbo.returnTotalSum(i.partija,r.tip,t.sifval,i.dotvaranje) <> 0

I assume the problem with this is that it has to go through each record, compare, evaluate the condition. Considering there is no index in the table(I can't add index as I have no privileges) it tends to run forever.

Is there anything I can do to improve the performance of this function, do you have any suggestions on using something else beside functions and what?

You should rewrite it as an inline table valued function. It will be much quicker. — HoneyBadger, Feb 27 '18 at 14:33
Nothing in your query refers to `totalsum`. The variable is probably going to return an error -- multiple rows in the subquery. — Gordon Linoff, Feb 27 '18 at 14:34
And be careful with that NOLOCK hint you have everywhere. It is not a magical "go fast" button with no consequences. It is far more sinister than many people realize. https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, Feb 27 '18 at 14:34
I would also strongly suggest you store dates as dates instead of strings. — Sean Lange, Feb 27 '18 at 14:38
That code cannot work. `@iznos` is never declared or assigned but is what is returned. The query result used to assign `@totalSum` can return multiple rows. — paparazzo, Feb 28 '18 at 13:40
I accidentaly did not translate `iznos` from my native language to `@totalSum` while I was posting this code, but yes, it can return multiple rows. — unknown, Feb 28 '18 at 13:46

score 1 · Answer 1 · answered Feb 27 '18 at 14:50

Your function converted to an inline table valued function should something close to this.

CREATE FUNCTION returnTotalSum
(
    @clientID VARCHAR(20)
    ,@type INT
    ,@currency VARCHAR(20)
    ,@date VARCHAR(10) --Don't store dates as strings...
)
RETURNS TABLE AS RETURN

SELECT TotalSum = SUM(CONVERT(DECIMAL(18,4), P.iznos * (1 - P.dp) / 2))
FROM pts as p
INNER JOIN tippart t on p.tip = @type
INNER JOIN its i on p.partija = @clientID                      
WHERE p.currency = @currency 
    and pts.dknizenja < @date
GROUP BY p.partija

score 1 · Accepted Answer · answered Feb 27 '18 at 15:08

In your implementation calling the function as part of filtering in where is causing huge performance drain, possibly due to multiple index scans.

As as general guidance you should be able to reduce it if you fine achieving this without the function.

It will be difficult to give you accurate solution, without data structure and sample data.

Try the code below:

    ... 
    880001,
    NULL, 
    NULL,
    NULL, 
    NULL,
    CONVERT(INT,REPLACE('2017.12.31','.','')) 
    FROM ITS I WITH(NOLOCK)      
        JOIN TIPPART R WITH(NOLOCK) ON I.TIP = R.TIP       
        LEFT JOIN #UNPVT_TIPSTOPE_TS T (NOLOCK) ON I.KAMGRUPA = T.GRUPA AND I.TIP = T.TIP AND I.VALUTA = T.SIFVAL   
        LEFT JOIN #UNPVT_TIPSTOPE_TS T1 (NOLOCK) ON I.KAMGRUPA = T1.GRUPA AND I.TIP = T1.TIP AND I.VALUTA = T1.SIFVAL AND T.GRUPA IS NULL 
        LEFT JOIN #TMP_DODATNA_KS DS (NOLOCK) ON I.PARTIJA = DS.PARTIJA AND I.VALUTA = DS.SIFVAL AND I.KAMGRUPA = DS.GRUPA
        LEFT JOIN #NML_RATE N (NOLOCK)  ON I.TIP = N.TIP AND N.SIFVAL = I.VALUTA AND N.GRUPA = I.KAMGRUPA
        -- LEFT JOIN TIPSTOPE TS (NOLOCK) ON I.TIP = TS.TIP AND TS.GRUPA = I.KAMGRUPA AND TS.SIFVAL = I.VALUTA
        LEFT JOIN #NML_RATE_PERIOD NML (NOLOCK) ON I.TIP = NML.TIP AND I.VALUTA = NML.SIFVAL AND NML.GRUPA = I.KAMGRUPA AND NML.SIFVAL = I.VALUTA
        --WHERE NOT EXISTS (SELECT * FROM [dbo].[IC_INPT_AR_X_INT_RATE_SNPST] WHERE PARTIJA = I.PARTIJA AND VALUTA = I.VALUTA AND APP = 'ST')  
    LEFT OUTER JOIN -- Added this join with same logic from function rather than calling a function. 
    (
        SELECT SUM (CONVERT(DECIMAL(18,4),P.iznos*(1-P.dp)/2)) TotalSum
    FROM pts as p
        INNER JOIN tippart t on p.tip = r.tip
        INNER JOIN its i on p.partija = i.partija     
        WHERE p.currency = t.sifval and pts.dknizenja < i.dotvaranje
        GROUP BY p.partija 
    ) SumTable
    WHERE I.DOTVARANJE <= '2017.12.31'
    AND (T.TIP IS NOT NULL 
        OR T1.TIP IS NOT NULL 
        OR DS.PARTIJA IS NOT NULL)
        AND SumTable.TotalSum <> 0  -- This is similar to your old logic where you were comparing with function output.

Query explanation:

I have added in SumTable which is haivng left outer join with your existing query logic.
As the additional left outer join has group by p.partija, it won't mess up with your result set.
all the inputs of your old function has been replaced with related values, as we are doing inline query here.
Lastly, the where part which was indicated as culprit for performance drain is improved and won't call function, instead will use SumTable.TotalSum and compare it with 0.

I like it. Elegant replacement indeed. – unknown Feb 27 '18 at 15:20 — unknown, Feb 27 '18 at 15:20

Function in WHERE clause - dealing with poor performance

2 Answers2