Category data livereport having performance issue in loading content server page.

Vikram_Saxena

Hi Team,

We have livereport queries written for extracting data from llattrdata table and dtree, query result is displaying date in front page, however once user navigating to front page there is performance issue while displaying data and it is taking 5-10 minutes in production system. In lower environment query is working to fetch results in 20-25 second. Please advise if we can do some customization in livereport to display data in fewer seconds as expected in production.

We have done update in section

 dataid in (select dtree.dataid from dtree ,dtreeancestors da where dtree.dataid = da.dataid
and dtree.subtype in (848,144) and da.ancestorid=%2) but it is not worked out.

Sample test livereport for reference,

%1 = catid def value

%2 = foldername where it is fectcing inc information.

Select INCNAME, max(createdate) from
(select
 (select name from dtree where dataid=z.ownerid) incname,
(select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=49) incnum,
(select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=72) incloc,
(select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=79) incorg,
 (select
 Concat(
 REPLACE( DISPLAY_URL , REGEXP_SUBSTR( DISPLAY_URL , '\$.*\$' ) ) ,
 (select valstr from llattrdata where id=z.ownerid and defid=%1 and attrid=50)
 )
 from
 OTSAP TableName
 where
 ID_BO_TYPE= ( select id_bo_type from OTSAP_TableName where dataid=z.ownerid)) sapurl,
 z.*
from
 dtree z
where
 dataid in (Select id from llattrdata where defid=(Select a.dataid from dtree a where a.subtype=131 and a.name='Project General' and a.ownerID='-2004')and VALSTR in('test Summary','IV Report'))
and
 dataid in (select dtree.dataid from dtree ,dtreeancestors da where dtree.dataid = da.dataid
and dtree.subtype in (848,144) and da.ancestorid=%2)
and
 subtype=144
and
 (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=117)='YES'
and
 (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=85)='NO'
and
 (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=87)='000'
and
 (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=149) <> 'Void'
and
 (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=144) = 'Incident'
and
 (
 (select defid from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=168 and (valstr='A-D' or valstr='E') ) = %1
 or
 (select defid from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=175 and (valstr='A-D' or valstr='E') ) = %1
 )

order by createdate desc) Group by incidentname
order by max(createdate) desc

appuGmail

I would say run the query in a SQL tool and with your DBA looking at it.Many tools will actually show you the inefficiencies per se.LLATTRDATA is a massive table.If possible see if rather than joining all the

llattrdata joins ,put them into a view(I know this will perhaps not make it any faster but the view will make your query more readable). Then the next test I would look is if you are seeing this because you are filtering rows for the

context user.

See this official view creator https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=3498953&objAction=View you can re purpose for your cats/atts.

Some times if the data need not be available real time you can change the query to create a table during off hours and then people will be getting faster results(albeit not real time)

Many DB vendors offer copies of tables (terms like materialized views etc) but a first start is to analyze the query and make sure if any inefficiencies there are addressed

..... appnair appunair( Livelink Rocks  )

Well, if I called the wrongnumber, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937 

On Tue, May 8, 2018 at 5:16 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Category data livereport having performance issue in loading content server page.

Posted byvikram.saxena@bp.com (Saxena, Vikram)On 05/08/2018 06:08 AM

Hi Team,We have livereport queries written for extracting data from llattrdata table and dtree, query result is displaying date in front page, however once user navigating to front page there is performance issue while displaying data and it is taking 5-10 minutes in production system. In lower environment query is working to fetch results in 20-25 second. Please advise if we can do some customization in livereport to display data in fewer seconds as expected in production.We have done update in section dataid in (select dtree.dataid from dtree ,dtreeancestors da where dtree.dataid = da.dataid and dtree.subtype in (848,144) and da.ancestorid=%2) but it is not worked out. Sample test livereport for reference,%1 = catid def value%2 = foldername where it is fectcing inc information.Select INCNAME, max(createdate) from (select  (select name from dtree where dataid=z.ownerid) incname, (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=49) incnum, (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=72) incloc, (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=79) incorg,  (select  Concat(  REPLACE( DISPLAY_URL , REGEXP_SUBSTR( DISPLAY_URL , '\$.*\$' ) ) ,  (select valstr from llattrdata where id=z.ownerid and defid=%1 and attrid=50)  )  from  OTSAP TableName  where  ID_BO_TYPE= ( select id_bo_type from OTSAP_TableName where dataid=z.ownerid)) sapurl,  z.* from  dtree z where  dataid in (Select id from llattrdata where defid=(Select a.dataid from dtree a where a.subtype=131 and a.name='Project General' and a.ownerID='-2004')and VALSTR in('test Summary','IV Report')) and  dataid in (select dtree.dataid from dtree ,dtreeancestors da where dtree.dataid = da.dataid and dtree.subtype in (848,144) and da.ancestorid=%2) and  subtype=144 and  (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=117)='YES' and  (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=85)='NO' and  (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=87)='000' and  (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=149) <> 'Void' and  (select valstr from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=144) = 'Incident' and  (  (select defid from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=168 and (valstr='A-D' or valstr='E') ) = %1  or  (select defid from llattrdata where id=z.ownerid and defid=%1 and vernum=(select versionnum from dtree where dataid=z.ownerid) and attrid=175 and (valstr='A-D' or valstr='E') ) = %1  )order by createdate desc) Group by incidentname order by max(createdate) desc

---

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	My Support