求优化千万级数据库语句

Question

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)>0select 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毫秒
求优化啊 

Answer 1

UT_USER 中UserID为主键 标示.
UT_PROGRAM 中3个字段UserID, ProgramID,weight  其中对 UserID, ProgramID建了一个非聚集唯一索引 

Answer 2

如果没有where条件的话，那么大的数据量会很容易导致全表扫描，另外，表变量只能创建主键约束，大数据量的时候换临时表比较有效

Answer 3

Declare @Similarity Table(UserID int, Similarity decimal(4,3))
换成临时表 create table #Similarity 

Answer 4

UT_PROGRAM 需要增加ProgramID的索引  

Answer 5

写法修改如下：
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=@userIDInsert 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 

Answer 6

select ProgramID,weight INTO #Tfrom [UT_PROGRAM] where userid=@userID
这个为什么也要用 临时表??

Answer 7

CREATE TABLE #Similarity (UserID int, Similarity decimal(4,3))   这里面并没有创建 索引啊??

Answer 8

PROGRAM 中3个字段UserID, ProgramID,weight  其中对 UserID, ProgramID建了一个非聚集唯一索引  

Answer 9

额~~~~~我觉得是不是你理解错了，索引是建了表再建的....CREATE TABLE #Similarity (UserID int, Similarity decimal(4,3))  然后在create index xxx on #Similarity (列名)。临时表可以创建随意，但是表变量不可以

Answer 10

1、不是必须在这个表创建临时表，最好看看执行计划。因为虽然表小，但是和大表关联，不一定高效。
2、你是不是应该考虑是否有一些潜在可以添加的where条件？