set statistics time ondeclare @userid int =4declare @count int
set @count = (select COUNT(*) from UT_PROGRAM where UserID=@userid and TYPE<>0);
select userid,
(select sum(2 - abs(u1.type-u2.type)) from UT_PROGRAM u1
join UT_PROGRAM u2 on u1.ProgramID=u2.ProgramID
where u1.UserID=@userid and u1.TYPE<>0 and u2.UserID=u.userid and u2.TYPE<>0)*1.0/((select COUNT(*)+ @count from UT_PROGRAM where UserID=u.UserID and TYPE<>0) ) as s
from dbo.UT_USER u where u.UserID<>@userid order by UserID牵涉到俩表
UT_PROGRAM 1000万数据
UT_USER 100万数据
现在运行的时间 大概为 11600毫秒
求优化啊
set @count = (select COUNT(*) from UT_PROGRAM where UserID=@userid and TYPE<>0);
select userid,
(select sum(2 - abs(u1.type-u2.type)) from UT_PROGRAM u1
join UT_PROGRAM u2 on u1.ProgramID=u2.ProgramID
where u1.UserID=@userid and u1.TYPE<>0 and u2.UserID=u.userid and u2.TYPE<>0)*1.0/((select COUNT(*)+ @count from UT_PROGRAM where UserID=u.UserID and TYPE<>0) ) as s
from dbo.UT_USER u where u.UserID<>@userid order by UserID牵涉到俩表
UT_PROGRAM 1000万数据
UT_USER 100万数据
现在运行的时间 大概为 11600毫秒
求优化啊
UT_PROGRAM 中字段UserID, ProgramID,type,weight
UT_PROGRAM 已经增加ProgramID的索引
简单瞄了一下,WHERE条件中还有不等号,还有参与运算的,基本都不符合SARG,索引优化效果不大,建议调整下语序
declare @userid int =4 , @tmp_Sum numeric(38,1) = 0select @tmp_Sum=sum(2 - abs(u1.type-u2.type)) from UT_PROGRAM u1
join UT_PROGRAM u2 on u1.ProgramID=u2.ProgramID
where u1.UserID=@userid and u1.TYPE<>0 and u2.UserID=u.userid and u2.TYPE<>0
declare @count int
set @count = (select COUNT(1) from UT_PROGRAM where UserID=@userid and TYPE<>0);
select userid,
@tmp_Sum/((select COUNT(1)+ @count from UT_PROGRAM where UserID=u.UserID and TYPE<>0) ) as s
from dbo.UT_USER u where u.UserID<>@userid order by UserID
粗略改了下,具體要看你要怎麼查詢.