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