sql sql-server function t-sql sql-tuning

Function in WHERE clause - dealing with poor performance

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?

Solution

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.