Query taking too long to execute with scalar function in where clause

Question

Below is my scalar function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [CheckClients]
(
    @UserId Varchar(3),
    @DbrNo varchar(10),
    @V_DBR_CLIENT varchar(6)
)
RETURNS int
AS
BEGIN
     Declare @Flag int
     set @Flag=1

     if(@V_DBR_CLIENT='XXXXXX')
     BEGIN
         if((select COUNT(USR_CLI) 
             from USRAGYCLI 
             inner join DBR on DBR_CLIENT = USR_CLI 
             where USR_CODE = @UserId and DBR_SERIES like @DbrNo +'T') <> 
            (select COUNT(DBR_CLIENT) 
             from DBR 
             where DBR_SERIES like @DbrNo + 'T') OR 
            (select COUNT(DBR_CLIENT) 
             from DBR 
             where DBR_SERIES like @DbrNo +'T') <= 0)
         BEGIN
             set @Flag=0
         END
      END

      RETURN @Flag
END

This is my stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [SEL_CLI]
    @V_USER_ID VARCHAR(3),
    @V_NUMBER_OF_ROWS INT,
    @V_STARTS_WITH INT  
AS
BEGIN
    CREATE TABLE #tmpDbrNo 
    (
         Code VARCHAR(10),
         Name VARCHAR(100),
         NumberOfDebtors int,
         rownum int 
    )   

    ;WITH Temp AS
    (
        SELECT 
            CLT_NO AS Code,
            CLT_NAME AS Name,
            COUNT(DBR_NO) AS NumberOfDebtors
        FROM
            DBR 
        JOIN 
            USRAGYCLI ON DBR_CLIENT = USR_AGY_CLI
        JOIN 
            CLT ON DBR_CLIENT = CLT_NO
        WHERE       
            AND USR_CODE = @V_USER_ID           
            AND 1 = CheckClients(@V_USER_ID, DBR_NO, DBR_CLIENT)
        GROUP BY        
            CLT_NO, CLT_NAME
    )               
    INSERT INTO #tmpDbrNo   
        SELECT 
            Code, Name, NumberOfDebtors, 
            ROW_NUMBER() OVER (ORDER by Code) rownum 
        FROM
            Temp

    SELECT 
        Code, Name, NumberOfDebtors  
    FROM
        #tmpDbrNo 
    WHERE
        rownum BETWEEN @V_STARTS_WITH AND @V_STARTS_WITH + @V_NUMBER_OF_ROWS
END

Above query takes about 25 sec to execute which is too long to wait. And if I comment out the line where I have called the scalar function in the where clause, it takes 0 secs to execute the query.

Can anybody suggest better way which may take minimum secs to execute the query? I have tried to put call to function in case like as below, but no success.

AND 1 = CASE WHEN DBR_CLIENT='XXXXXX' THEN CheckClients(@V_USER_ID,DBR_NO,DBR_CLIENT) ELSE 1 END

That is because scalar function are horrible inefficient. And when you put it in the where clause is renders the query nonSARGable. In other words, it is has to calculate the value of your function for every single row in the table. Can probably convert your scalar function into an inline table valued function and the performance will be nearly as fast as excluding the function that you see now. — Sean Lange, Jan 20 '16 at 22:29
You can probably replace your function usage with a SQL `EXISTS` and improve performance considerably. — Nick.Mc, Jan 21 '16 at 05:09
it is compulsory to use function, rather than use inner subquery — Ajay2707, Jan 21 '16 at 05:27

Aasish Kr. Sharma · Answer 1 · 2016-01-26T05:53:33.453

You can optimize your scalar function query to reduce doing multiple read. Like:

ALTER FUNCTION [CheckClients] (
    @UserId VARCHAR(3),
    @DbrNo VARCHAR(10),
    @V_DBR_CLIENT VARCHAR(6)
    )
RETURNS INT
AS
BEGIN
    DECLARE @Flag INT

    SET @Flag = 1

    IF (@V_DBR_CLIENT = 'XXXXXX')
    BEGIN

        DECLARE @Count INT = ISNULL((
                SELECT COUNT(DBR_CLIENT)
                FROM DBR
                WHERE DBR_SERIES LIKE @DbrNo + 'T'
            ), 0);

        IF (
                (ISNULL((
                    SELECT COUNT(USR_CLI)
                    FROM USRAGYCLI
                    INNER JOIN DBR ON DBR_CLIENT = USR_CLI
                    WHERE USR_CODE = @UserId
                        AND DBR_SERIES LIKE @DbrNo + 'T'
                ), 0) <> @Count)
                OR (@Count <= 0)
            )
        BEGIN
            SET @Flag = 0
        END

    END

    RETURN @Flag
END

Also, you need to study your execution plan of the query to find out where the query is having high cost of execution time. And create non-clustered index if necessary.

-- EDITED LATER --

The non-Sargable Problem (Calling Scalar Function):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [SEL_CLI]
    @V_USER_ID VARCHAR(3),
    @V_NUMBER_OF_ROWS INT,
    @V_STARTS_WITH INT  
AS
BEGIN
    CREATE TABLE #tmpDbrNo 
    (
         Code VARCHAR(10),
         Name VARCHAR(100),
         NumberOfDebtors int,
         rownum int 
    )   

    ;WITH Temp AS
    (
        SELECT 
            CLT_NO AS Code,
            CLT_NAME AS Name,
            COUNT(DBR_NO) AS NumberOfDebtors
        FROM
            DBR 
        JOIN 
            USRAGYCLI ON DBR_CLIENT = USR_AGY_CLI
        JOIN 
            CLT ON DBR_CLIENT = CLT_NO
        WHERE       
            USR_CODE = @V_USER_ID           
            AND 1 = 
            (CASE 
                WHEN (@V_DBR_CLIENT = 'XXXXXX') THEN 
                    (CASE 
                        WHEN (
                                ISNULL((
                                    SELECT COUNT(USR_CLI)
                                    FROM USRAGYCLI
                                    INNER JOIN DBR ON DBR_CLIENT = USR_CLI
                                    WHERE USR_CODE = @UserId
                                        AND DBR_SERIES LIKE @DbrNo + 'T'
                                ), 0) <> ISNULL((
                                    SELECT COUNT(DBR_CLIENT)
                                    FROM DBR
                                    WHERE DBR_SERIES LIKE @DbrNo + 'T'
                                ), 0)
                            )
                            OR (ISNULL((
                                    SELECT COUNT(DBR_CLIENT)
                                    FROM DBR
                                    WHERE DBR_SERIES LIKE @DbrNo + 'T'
                                ), 0) <= 0)            
                        THEN 0 
                        ELSE 1 
                    END)
                ELSE 1 
            END)--CheckClients(@V_USER_ID, DBR_NO, DBR_CLIENT)
        GROUP BY        
            CLT_NO, CLT_NAME
    )               
    INSERT INTO #tmpDbrNo   
        SELECT 
            Code, Name, NumberOfDebtors, 
            ROW_NUMBER() OVER (ORDER by Code) rownum 
        FROM
            Temp

    SELECT 
        Code, Name, NumberOfDebtors  
    FROM
        #tmpDbrNo 
    WHERE
        rownum BETWEEN @V_STARTS_WITH AND @V_STARTS_WITH + @V_NUMBER_OF_ROWS
END

As you can see, the scalar function can be included in the same query, but if you study the function nicely than it is clear that the query in scalar function is not fully dependent on the query in the store procedure. It is making count and will reread and manipulate the data from the table every time.

So, with this type of query making non-Sargable to Sargable will not improve the performance. The possible solution to the problem will be

To previously add the required data in the table and check from there.
To study your query plans(Design and Execution) and optimize it accordingly.

This is still a scalar function and the OP is calling this from the where clause. It will be faster but barely. It is still a nonSARGable scalar function. — Sean Lange, Jan 21 '16 at 14:29
That's right but I think scalar function is not the big problem here. The problem is with the "LIKE" operator that is used here, The query is just counting the record, So, it can perform more better, if it has an index for DBR_SERIES. — Aasish Kr. Sharma, Jan 22 '16 at 03:41
We don't know if there is an index or not on that column. The problem might be the LIKE operator but we don't even know what is being passed as values. If it is a leading wildcard in there no amount of indexing will help. And of course as long as you stick to the scalar function indexing is pointless because it is nonSARGable. — Sean Lange, Jan 22 '16 at 14:21
I have added some more stuffs, I think this will make a solution. — Aasish Kr. Sharma, Jan 26 '16 at 05:55

score 0 · Answer 2 · answered Jan 21 '16 at 15:04

This is just a shot in the dark because we were not provided with any ddl or much to work with. I think I interpreted the existing logic in your scalar function correctly. As a general rule you should probably avoid using flags. This is a very old school mindset and is not suited to relational data very well at all. I suspect this could be greatly improved with an understanding of the actual requirements but this is the best I could do with the limited details.

CREATE FUNCTION [CheckClients]
(
    @UserId Varchar(3),
    @DbrNo varchar(10),
    @V_DBR_CLIENT varchar(6)
)
RETURNS table as return

with RowCounts as
(
    select
    (
        select COUNT(DBR_CLIENT) 
        from DBR 
        where DBR_SERIES like @DbrNo + 'T'
    ) as ClientCount
    , 
    (
        select COUNT(USR_CLI) 
        from USRAGYCLI u
        inner join DBR d on d.DBR_CLIENT = u.USR_CLI 
        where u.USR_CODE = @UserId 
            and d.DBR_SERIES like @DbrNo +'T'
    ) as UserCount
)

select case 
    when @V_DBR_CLIENT = 'XXXXXX' then
        Case when rc.UserCount <> rc.ClientCount then 0
            when rc.ClientCount < 0 then 0
            else 1
        end
    else 1
    end as Flag
from RowCounts rc

Thanks for answer. Have tried your function but seems no change in performance. — Mayur Pawar, Jan 21 '16 at 22:50
You should measure the performance, not just run it and see how long it takes. Look at the execution plan and figure out where the bottleneck is at. The function I posted here will out perform a scalar function and is way more flexible. But it could be the queries inside the function are the problem. We don't have table definitions or an execution plan to see what is really happening. We are just guessing at what the problem most likely is. — Sean Lange, Jan 22 '16 at 14:16

Query taking too long to execute with scalar function in where clause

2 Answers2