求优化万万级数据库语句

求优化千万级数据库语句

ALTER PROCEDURE [dbo].[UP_SEARCH_SIMILARITY]

@userID int=7

as

BEGIN

set statistics time on

Declare @Similarity Table(UserID int, Similarity decimal(4,3))


Insert into @Similarity

select  up1.UserID,sum(case when up1.weight*up2.weight>0 then 1.0 else 0 end)/count(up1.UserID) as Similarity from [UT_PROGRAM] up1

join (select ProgramID,weight from [UT_PROGRAM] where userid=@userID) up2 on up1.ProgramID=up2.ProgramID

--where up1.UserID<>@userID 为增加效率 在下面语句排除自己

group by up1.UserID having sum(case when up1.weight*up2.weight>0 then 1 else 0 end)>0


select u.[UserID],[Email],[Nick],[HeaderPath],[Sex],[Birthday],[Longitude],[Latitude],s.Similarity from[UT_USER] u

join @Similarity s on s.UserID=u.UserID and s.UserID<>@userID

--where s.UserID<>@userID and Similarity>0

order by Similarity desc

END

牵涉到俩表
UT_PROGRAM 1000万数据
UT_USER 100万数据

现在运行的时间大概为 1600毫秒
求优化啊
------最佳解决方案--------------------
UT_PROGRAM 需要增加ProgramID的索引

------其他解决方案--------------------
UT_USER 中UserID为主键标示.
UT_PROGRAM 中3个字段UserID, ProgramID,weight 其中对 UserID, ProgramID建了一个非聚集唯一索引
------其他解决方案--------------------
如果没有where条件的话，那么大的数据量会很容易导致全表扫描，另外，表变量只能创建主键约束，大数据量的时候换临时表比较有效
------其他解决方案--------------------
Declare @Similarity Table(UserID int, Similarity decimal(4,3))
换成临时表 create table #Similarity
------其他解决方案--------------------
写法修改如下：

ALTER PROCEDURE [dbo].[UP_SEARCH_SIMILARITY] 

@userID int=7 

as

BEGIN

--set statistics time on

CREATE TABLE #Similarity (UserID int, Similarity decimal(4,3))   


select ProgramID,weight 

INTO #T

from [UT_PROGRAM] 

where userid=@userID


Insert into #Similarity 

select  up1.UserID,sum(case when up1.weight*up2.weight>0 then 1.0 else 0 end)/count(up1.UserID) as Similarity 

from [UT_PROGRAM] up1 join #T up2 

on up1.ProgramID=up2.ProgramID 

where up1.UserID<>@userID 

group by up1.UserID 

having sum(case when up1.weight*up2.weight>0 then 1 else 0 end)>0   


select u.[UserID],[Email],[Nick],[HeaderPath],[Sex],[Birthday],[Longitude],[Latitude],s.Similarity 

from[UT_USER] u join #Similarity s 

on s.UserID=u.UserID 

order by Similarity desc

END

------其他解决方案--------------------
为什么不用表变量而要用临时表?
说说理由
------其他解决方案--------------------
临时表可以创建索引，表变量只有主键约束，还不能称为索引，没有索引又要对应大数据量，一但筛选性不高的话，就会导致全表扫描。千万级表扫描怎么可能块呢？
------其他解决方案--------------------

求优化万万级数据库语句

相关推荐