MS SQL Server function performance issues

Question

Hopefully somebody can help me with a MS SQL function performance issue. I have the following function which matches users in the same table. User A is searching for users he can match with. If he finds a User B that matches his criteria, User B checks wether User A matches their criteria. If they have already matched they are in the ExcludedCandidates table. The problem I have is that the query takes too long.

I tried a lot of things but have no more ideas on how to improve it.

Maybe some indices or changes to the query could help here. Any help would be appreaciated.

create function [dbo].[FindUser](@UserId uniqueidentifier, @dis int, @gen int, @age datetime, @f int, @ti int, @s int, @La float, @Lo float)
returns @T table (UserId uniqueidentifier, Profile nvarchar(MAX), Filter nvarchar(MAX), F int, I bit, IsP bit)
as
BEGIN
    
    declare @Tmp table(UserId uniqueidentifier, a nvarchar(MAX), b nvarchar(MAX), c int, d bit, e bit)

    DECLARE @source geography
    select @source = geography::Point(@La, @Lo, 4326)

    insert into @Tmp
    SELECT TOP 10 U.UserId, U.Profile, U.F, @s as Fil, U.Ve, U.TT from Users AS U WITH (NOLOCK)
    WHERE ((@gen & U.Ge) != 0) AND
    (Sea = 1 OR Sea = 2) AND
    @s = U.Sear AND
    U.La is not null and U.Lon is not null AND
    @dis >= (@source.STDistance(geography::Point(U.La, U.Lon, 4326)) / 1000) AND
    (@f <= YEAR(GETUTCDATE()) - YEAR(@age)) AND (@ti >= YEAR(GETUTCDATE()) - YEAR(@age)) AND
    U.UserId != @UserId
    and not exists
    (select TOP 1 IC1.InitiatorUserId from ExcludedCandidates AS IC1 with (NOLOCK)
    where (IC1.InitiatorUserId = @UserId and IC1.PartnerUserId = U.UserId) OR
    (IC1.InitiatorUserId = U.UserId and IC1.PartnerUserId = @UserId)) 
    and exists(

        SELECT U.UserId from Users UserP with (NOLOCK)
        WHERE ((JSON_VALUE(UserP.Filter, '$.gender') & U.Ge) != 0) AND
        (Sea = 1 OR Sea = 2) AND
        @s = Sea AND
        (JSON_VALUE(UserP.Filter, '$.age.lo') <= YEAR(GETUTCDATE()) - YEAR(@age)) AND (JSON_VALUE(UserP.Filter, '$.age.up') >= YEAR(GETUTCDATE()) - YEAR(@age)) AND
         JSON_VALUE(UserP.Filter, '$.di') >= (geography::Point(UserP.La, UserP.Lon, 4326).STDistance(geography::Point(@La, @Lo, 4326)) / 1000) AND
        UserId = U.UserId

    )
    order by U.Sea DESC
    
    insert into @T
    select UserId, a, b, c, d, e from @Tmp


    return 
END

Indexes we use

CREATE NONCLUSTERED INDEX [NonClusteredIndex_Users_Search] ON [dbo].[Users]
(
    [Sea] DESC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex_Users_SearchSearchState] ON [dbo].[Users]
(
    [Sear] ASC,
    [Sea] ASC
)
INCLUDE (   [Filter],
    [La],
    [Lon]) WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
GO

Answer 1

I think I would change this to a stored procedure if you don't have an explicit reason you need a function. You create a function to include the results in a select statement with another table. It doesn't sound like that's what you are doing here ...

Also, I would separate the logic that parses the JSON data from the table and perform that apart from the main query. Those parsing statements are probably taking you the most time. You can potentially optimize that separately.

Last, ensure you have an index on your ExcludedCandidates tables like the following ...

CREATE INDEX [IX_ExcludedCandidates_UserIDs] ON [dbo].[ExcludedCandidates] (
    InitiatorUserId, PartnerUserId
)

Here's the PROC definition I would try ...

CREATE PROC [dbo].[FindUser] (
    @UserId uniqueidentifier,
    @dis int,
    @gen int,
    @age datetime,
    @f int,
    @ti int,
    @s int,
    @La float,
    @Lo float
) AS BEGIN
    DECLARE @source geography = geography::Point(@La, @Lo, 4326)

    SELECT
        *
    INTO
        #UserInfo
    FROM (
        SELECT
            UserId,
            La,
            Lon,
            Sea,
            JSON_VALUE(Filter, '$.gender') gender,
            JSON_VALUE(Filter, '$.age.lo') age_lo,
            JSON_VALUE(Filter, '$.age.up') age_up,
            JSON_VALUE(Filter, '$.di') di
        FROM
            Users
        ) S
    WHERE
        di >= (geography::Point(La, Lon, 4326).STDistance(geography::Point(@La, @Lo, 4326)) / 1000) 

    SELECT TOP 10
        U.UserId,
        U.Profile,
        U.F,
        @s AS Fil,
        U.Ve,
        U.TT
    FROM
        Users AS U WITH (NOLOCK)
    WHERE
        ((@gen & U.Ge) != 0) AND (
            Sea = 1
            OR Sea = 2
        ) AND 
        @s = U.Sear AND
        U.La IS NOT NULL AND
        U.Lon IS NOT NULL AND
        @dis >= (@source.STDistance(geography::Point(U.La, U.Lon, 4326)) / 1000) AND 
        (@f <= YEAR(GETUTCDATE()) - YEAR(@age)) AND 
        (@ti >= YEAR(GETUTCDATE()) - YEAR(@age)) AND 
        U.UserId != @UserId AND 
        NOT EXISTS (
            SELECT TOP 1
                IC1.InitiatorUserId
            FROM
                ExcludedCandidates AS IC1 WITH (NOLOCK)
            WHERE (
                    IC1.InitiatorUserId = @UserId AND
                    IC1.PartnerUserId = U.UserId
                ) OR (
                    IC1.InitiatorUserId = U.UserId AND
                    IC1.PartnerUserId = @UserId
                )
        ) AND 
        EXISTS(
            SELECT
                U.UserId
            FROM
                #UserInfo UserP WITH (NOLOCK)
            WHERE
                (UserP.gender & U.Ge) != 0 AND 
                UserP.Sea IN (1, 2) AND
                @s = UserP.Sea AND (UserP.age_lo <= YEAR(GETUTCDATE()) - YEAR(@age)) AND 
                (UserP.age_up >= YEAR(GETUTCDATE()) - YEAR(@age)) AND 
                UserP.UserId = U.UserId
        )
    ORDER BY
        U.Sea DESC
END

Answer 2

What you have here is a multi-line table value function, which are known to perform poorly. You will very likely find that converting it to an inline-table value function will provide much better performance.

Due to not having the definition of the other objects, I can't test this (so any syntax errors have not been checked), however, this is a literal conversion to a inline table-value function. There is, however, likely more you can do here (for example @dis >= (V.srv.STDistance(geography::Point(U.La, U.Lon, 4326)) / 1000) is not SARGable), but without a true goal, along with sample data and expected results, this would be impossible to do more than guess at:

CREATE FUNCTION [dbo].[FindUser](@UserId uniqueidentifier, @dis int, @gen int, @age datetime, @f int, @ti int, @s int, @La float, @Lo float)
RETURNS table --@T table (UserId uniqueidentifier, Profile nvarchar(MAX), Filter nvarchar(MAX), F int, I bit, IsP bit)
AS RETURN

    
    --declare @Tmp table(UserId uniqueidentifier, a nvarchar(MAX), b nvarchar(MAX), c int, d bit, e bit)
    --
    --DECLARE @source geography
    --select @source = geography::Point(@La, @Lo, 4326)

    SELECT TOP (10)
           U.UserId,
           U.Profile,
           U.F,
           @s as Fil,
           U.Ve,
           U.TT
    FROM dbo.Users AS U-- WITH (NOLOCK) --Unless you really undersatnd what NOLOCK does, you shou.dn't be using this.
         CROSS APPLY(VALUES(geography::Point(@La, @Lo, 4326)))V(src)
    WHERE ((@gen & U.Ge) != 0)
      AND (Sea = 1 OR Sea = 2)
      AND @s = U.Sear
      AND U.La IS NOT NULL
      and U.Lon IS NOT NULL
      AND @dis >= (V.srv.STDistance(geography::Point(U.La, U.Lon, 4326)) / 1000)
      AND (@f <= YEAR(GETUTCDATE()) - YEAR(@age)) AND (@ti >= YEAR(GETUTCDATE()) - YEAR(@age))
      AND U.UserId != @UserId
      AND NOT EXISTS (SELECT 1 --No need for a TOP (1) here
                      FROM ExcludedCandidates AS IC1-- with (NOLOCK) --Unless you really undersatnd what NOLOCK does, you shou.dn't be using this.
                      WHERE (IC1.InitiatorUserId = @UserId AND IC1.PartnerUserId = U.UserId)
                         OR (IC1.InitiatorUserId = U.UserId AND IC1.PartnerUserId = @UserId)) 
      AND exists(SELECT 1
               from Users UserP-- with (NOLOCK) --Unless you really undersatnd what NOLOCK does, you shou.dn't be using this.
               WHERE ((JSON_VALUE(UserP.Filter, '$.gender') & U.Ge) != 0)
                 AND (Sea = 1 OR Sea = 2)
                 AND @s = Sea
                 AND (JSON_VALUE(UserP.Filter, '$.age.lo') <= YEAR(GETUTCDATE()) - YEAR(@age))
                 AND (JSON_VALUE(UserP.Filter, '$.age.up') >= YEAR(GETUTCDATE()) - YEAR(@age))
                 AND JSON_VALUE(UserP.Filter, '$.di') >= (geography::Point(UserP.La, UserP.Lon, 4326).STDistance(geography::Point(@La, @Lo, 4326)) / 1000)
                 AND UserId = U.UserId)
    ORDER BY U.Sea DESC;

GO