sql - MS SQL Server 函数性能问题
问题描述
希望有人可以帮助我解决 MS SQL 函数性能问题。我有以下功能可以匹配同一张表中的用户。用户 A 正在搜索他可以匹配的用户。如果他找到符合他的标准的用户 B,用户 B 会检查用户 A 是否符合他们的标准。如果它们已经匹配,则它们位于 ExcludedCandidates 表中。我遇到的问题是查询时间太长。
我尝试了很多东西,但对如何改进它没有更多的想法。
也许对查询的一些索引或更改可能会有所帮助。任何帮助将不胜感激。
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
我们使用的索引
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
解决方案
您在这里拥有的是一个多行表值函数,众所周知,它的性能很差。您很可能会发现将其转换为内联表值函数将提供更好的性能。
由于没有其他对象的定义,我无法对此进行测试(因此尚未检查任何语法错误),但是,这是对内联表值函数的文字转换。但是,您可能在这里可以做的更多(例如@dis >= (V.srv.STDistance(geography::Point(U.La, U.Lon, 4326)) / 1000)
不是 SARGable),但如果没有真正的目标,以及样本数据和预期结果,除了猜测之外,这不可能做的更多:
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
推荐阅读
- visual-studio - Visual Studio 2019“解决方案资源管理器”“查看代码”图标 C#
- python - 以相等的比例设置 matplotlib 3D 轴
- flutter - 是否可以在颤动的 TextFormField 中显示外键数据
- python - 调用 PutObject 操作时发生错误(InvalidArgument):计算的键的 MD5 哈希与提供的哈希不匹配
- database - 活动记录错误,是不是输入错误?
- django - 为什么 serializer.is_valid() 在这种情况下总是假的?
- asp.net-core - SQLDependancy 滞后
- c++ - 使用 UWP/c++ 录制具有同步关联元数据的视频
- flutter - Flutter listview 总是扩展到可用宽度
- sql - DBeaver 右键单击数据库和工具 -> 执行脚本:如何指定哪个模式?