日期:2014-05-18  浏览次数:20386 次

写了一个SQL函数要求返回一个count关联了三个表
ALTER                   function   GetInvitePersonCount(@ActorCode   varchar(10))
returns   int
as
begin
declare   @PersonCount   int

select   @PersonCount=count(*)   from   InvitePerson   p  
where   exists(select   t.InviteID   from   InviteInfo   t     where   t.InviteID=p.InviteID   and      
exists(   select   j.ActorCode   from   ActorJoin   j     where   j.ActorCode=t.ActorCode    
and   (j.ActorCode=@ActorCode   or   j.ActorGroupCode=@ActorCode)))


return   @PersonCount

end

当这三个表数据量很大的时候执行数据就比较慢了,希望高手们看能不能优化一下!谢谢各位了

------解决方案--------------------
ALTER function GetInvitePersonCount(@ActorCode varchar(10))
returns int
as
begin
declare @PersonCount int

select @PersonCount=count(1) from InvitePerson p
inner join InviteInfo t on t.InviteID=p.InviteID
inner join ActorJoin j on j.ActorCode=t.ActorCode
where j.ActorCode=@ActorCode or j.ActorGroupCode=@ActorCode

return @PersonCount
end