I've develop a Stored Procedure that gets data from the table VisitorsInfluences and builds five galaxies with the "most influenced visitors" at the middle of each one, and the visitors that are most influenced by them around them on each galaxy. Is this clear so far?
I don't know why, the Stored Procedure is taking around 6 or 7 or 10 seconds sometimes to run, and it becomes really slow to display on the website.
Could you help me out with this? I don't know what is taking so long, but i know which query is, so I'll comment it below.
Thanks,
Brian
Stored Procedure:
ALTER proc [dbo].[XInfluencedByYCloudGetXml] (@VisitorId int, @VisitorIdLogged int, @mindTopicId varchar(17))
as
set nocount on
declare @threshold decimal(6,2),
@FirstObj varchar(17),
@GalaxyId int,
@MainObjTitle varchar(255),
@MyMindTopicId varchar(17),
@CoId varchar(17)
set @threshold = 0.0001
set @GalaxyId = 1
select @MyMindTopicId = topicid from MyMindTopicVisitor where visitorid = @visitoridLogged
declare @MMIV table
(
VisitorId int not null,
AuthorId varchar(17) not null,
MMIV decimal(6,4),
PRIMARY KEY(VisitorId, AuthorId)
)
declare @Universe table
(
GalaxyId int,
VisitorId int,
CoId varchar(20),
ObjType varchar(35),
ObjTitle varchar(255),
ObjFontSize tinyint,
ObjPosition tinyint
)
/* Get the most influencing authors for the visitor */
insert into @MMIV
select distinct top 5
vi.VisitorIdX,
vi.AuthorIdY,
vi.[value]
from
VisitorsInfluences vi
inner join
tblcontentobjects co on co.coid = vi.authoridy
where
vi.visitoridx = @visitorid
and co.visitorid <> @visitorid
and vi.[value] >= @threshold
group by
vi.VisitorIdX,
vi.AuthorIdY,
vi.[value]
order by
vi.[value] desc
/* Loop until MMIV is empty */
WHILE (select count(*) from @MMIV) > 0
BEGIN
select top 1
@FirstObj = authorid
from
@MMIV
order by
MMIV desc
--Insert the center object in the current galaxy
/* DEBUG: En esta query tenes que hacer lo mismo que te dije para la font. Chequear el valor de influencia para el visitorLogged
y dependiendo de ese valor setear el tamaño de fuente */
insert into @Universe
select
@GalaxyId,
tco.visitorid,
@FirstObj,
'Person',
tco.firstname + ' ' + tco.lastname,
case
when
(
select
[value]
from
visitorsinfluences vi
where
vi.visitoridx = @visitorIdLogged
and vi.authoridy = tco.coid
) >= 0.66
then 5
else
case
when
(
select
[value]
from
visitorsinfluences vi
where
vi.visitoridx = @visitorIdLogged
and vi.authoridy = tco.coid
) >= 0.33
then 3
else 1
end
end as font,
4
from
@MMIV mm
inner join
tblcontentobjects tco on tco.coid = mm.authorid
where
mm.authorid = @FirstObj
delete from @MMIV where authorid = @FirstObj
--Insert the cluster objects in the current galaxy
/* DEBUG: En esta query tenes que hacer lo que te dije para la font. Chequear el valor de influencia para el visitorLogged
y dependiendo de ese valor setear el tamaño de fuente */
**insert into @Universe
select top 5
@GalaxyId as galaxyid,
vi.visitoridx as visitoridx,
co.coid as coid,
'Person' as cotype,
co.firstname + ' ' + co.lastname as visitorname,
case
when
(
select
[value]
from
visitorsinfluences vi
where
vi.visitoridx = @visitorIdLogged
and vi.authoridy = co.coid
) >= 0.66
then 5
else
case
when
(
select
[value]
from
visitorsinfluences vi
where
vi.visitoridx = @visitorIdLogged
and vi.authoridy = co.coid
) >= 0.33
then 3
else 1
end
end as font,
case when vi.[value] >= 0.66 then 3 else case when vi.[value] >= 0.33 then 2 else 1 end end as position
from
VisitorsInfluences vi
inner join
tblcontentobjects co on vi.visitoridx = co.visitorid
left join
@universe u on u.coid = co.coid
left join
@mmiv mm on mm.authorid = co.coid
where
vi.authoridy = @FirstObj
and [value] >= convert(real,@threshold)
and vi.visitoridx <> @visitorid
--and vi.visitoridx not in (select visitorid from @Universe)
--and co.coid not in (select coid from @Universe)
--and co.coid not in (select authorid from @mmiv)
and u.coid is null
and mm.authorid is null
and u.visitorid is null
/*group by
vi.visitoridx,
co.coid,
v.firstname,
v.lastname,
case when vi.[value] = 1 then 5 else case when vi.[value] >= (@threshold / 2) then 3 else 1 end end,
case when vi.[value] >= 0.66 then 3 else case when vi.[value] >= 0.33 then 2 else 1 end end*/
order by
vi.[value] desc**
if ((select count(*) from @Universe where GalaxyId = @GalaxyId) = 1) and @GalaxyId <= 5
begin
insert into @Universe
select top 5
@GalaxyId as galaxyid,
-- convert(varchar, vi.visitoridx) as visitoridx,
vi.visitoridx as visitoridx,
co.coid as coid,
'Person' as cotype,
v.firstname + ' ' + v.lastname as visitorname,
0 as font,
case when vi.[value] >= 0.66 then 3 else case when vi.[value] >= 0.33 then 2 else 1 end end as position
from
VisitorsInfluences vi
inner join
tblvisitor v on v.visitorid = vi.visitoridx
inner join
tblcontentobjects co on v.visitorid = co.visitorid
left join
@universe u on u.coid = co.coid
left join
@mmiv mm on mm.authorid = co.coid
where
vi.authoridy=@FirstObj-- and vi.visitoridx = v.visitorid
and vi.visitoridx <> @visitorid
-- and convert(varchar, vi.visitoridx) not in (select objid from @Universe)
--and vi.visitoridx not in (select visitorid from @Universe)
--and co.coid not in (select coid from @Universe)
--and co.coid not in (select authorid from @mmiv)
and u.coid is null
and mm.authorid is null
and u.visitorid is null
order by
vi.[value] desc
end
-- delete from @MMIV where authorid in (select ObjId from @Universe)
delete from @MMIV where visitorid in (select visitorid from @Universe)
set @GalaxyId = @GalaxyId + 1
END
--Getting the XML output
select
@MainObjTitle = rtrim(ltrim(firstname)) + ' ' + ltrim(rtrim(lastname))
from
tblcontentobjects
where
visitorid = @visitorid
select
@CoId = co.coid
from
tblcontentobjects co
where
co.visitorid = @visitorid
SELECT
@MainObjTitle as '@MainObjTitle',
@CoId as '@CoId',
(
SELECT
s.GalaxyID AS [@Id],
(
SELECT
U.VisitorId AS [@VisitorId],
U.CoId AS [@CoId],
U.ObjType AS [@Type],
U.ObjTitle AS [Title],
U.ObjFontSize as [FontSize],
U.ObjPosition as [Position],
co.[role] as [Role],
co.Affiliation as [Org],
case when ctr.topicid is null then 0 else 1 end as [IsInMyMind],
isnull(imgs.coviewurllink, '') as [coPicture],
case
when co.visitorid is null then ''
when exists (
select *
from visitorrequests vrs
where vrs.RequestDate > dateadd(mi, -10, getdate()) and
vrs.visitorid = co.visitorid
) then '_online'
else '_offline'
end as [IsOnline],
case when mctr.topicid is null then 0 else 1 end as [HasSocialNetworkProfile]
FROM
@Universe AS U
inner join tblcontentobjects co
-- on convert(varchar, co.visitorid) = U.ObjId or co.coid = U.ObjId
on co.visitorid = U.VisitorId or co.coid = U.CoId
inner join tblvisitor v
on v.visitorid = co.visitorid
left join
(
select img.*, cir.coidb
from tblcointerrelations cir
inner join tblcontentobjects img
on img.coid = cir.coida
where img.cotype='images'
) imgs
on co.coid = imgs.coidb
left join tblCoTopicRelations ctr
on ctr.coid = co.coid and ctr.topicid = @MyMindTopicId
left join tblCoTopicRelations mctr
on mctr.coid = co.coid and mctr.topicid = @mindTopicId
WHERE
U.GalaxyID = s.GalaxyID
ORDER BY
U.ObjPosition DESC
FOR XML PATH('Object'), TYPE
)
FROM
(
SELECT GalaxyID
FROM @Universe
GROUP BY GalaxyID
) AS s
ORDER BY
GalaxyId
FOR XML PATH('Galaxy'), TYPE
)
FOR XML PATH('Universe')`
