nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Optimizing a livereport to find all video files in a certain folder

Hayden Ross

Hi,

When I add this statement into a Livereport it works just very very slowly... I was wondering if anyone could suggest a way to optimize this report.

Basically I want a user to select a folder (%1 is a user input for a container) and it will look through that folder (and its subfolders/project workspaces) and return any video files found.

select * from (select * from (select DTREE.PARENTID, DTREE.DATAID, DTREE.NAME, DVERSDATA.VERSIONID, DVERSDATA.DOCID, DVERSDATA.VERSION, DVERSDATA.VERCDATE, DVERSDATA.FILENAME, DVERSDATA.DATASIZE, DVERSDATA.FILETYPE, DVERSDATA.MIMETYPE from DVERSDATA RIGHT JOIN DTREE ON DVERSDATA.DOCID = DTREE.DATAID where DVERSDATA.MIMETYPE like 'video%%' OR DTREE.SUBTYPE=0 OR DTREE.SUBTYPE=202) start with ABS(PARENTID) = %1 connect by ABS(PARENTID) = prior ABS(DATAID)) where MIMETYPE like 'video%%' ORDER BY PARENTID, DATAID, VERSION

Find more posts tagged with

Comments

Rinita_Pranaykanti

Hi

select * from dtree d, dversdata where dtree.dataid = dversdata.docid and dtree.vernum = dversdata.versionnum and dataid in (select dataid from dtree start with dataid = %1 connect by prior parentid = abs(dataid)) and dversdata.mimetype like 'video%%'

Can you please try the above query and let me know the result. I will also check notes and see if I can get a better query.

Regards,
Rinita

From: eLink Entry: Content Server LiveReports Forum [livereportsdiscussion@elinkkc.opentext.com]
Sent: Saturday, December 15, 2012 2:13 AM
To: eLink Recipient
Subject: Optimizing a livereport to find all video files in a certain folder

Optimizing a livereport to find all video files in a certain folder

Posted by hayden.ross@peelregion.ca (Ross, Hayden) On 12-14-2012 15:36

Hi,When I add this statement into a Livereport it works just very very slowly... I was wondering if anyone could suggest a way to optimize this report. Basically I want a user to select a folder (%1 is a user input for a container) and it will look through that folder (and its subfolders/project workspaces) and return any video files found.select * from (select * from (select DTREE.PARENTID, DTREE.DATAID, DTREE.NAME, DVERSDATA.VERSIONID, DVERSDATA.DOCID, DVERSDATA.VERSION, DVERSDATA.VERCDATE, DVERSDATA.FILENAME, DVERSDATA.DATASIZE, DVERSDATA.FILETYPE, DVERSDATA.MIMETYPE from DVERSDATA RIGHT JOIN DTREE ON DVERSDATA.DOCID = DTREE.DATAID where DVERSDATA.MIMETYPE like 'video%%' OR DTREE.SUBTYPE=0 OR DTREE.SUBTYPE=202) start with ABS(PARENTID) = %1 connect by ABS(PARENTID) = prior ABS(DATAID)) where MIMETYPE like 'video%%' ORDER BY PARENTID, DATAID, VERSION

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

The information contained in this message may be confidential and legally protected under applicable law. The message is intended solely for the addressee(s). If you are not the intended recipient, you are hereby notified that any use, forwarding, dissemination, or reproduction of this message is strictly prohibited and may be unlawful. If you are not the intended recipient, please contact the sender by return e-mail and destroy all copies of the original message.

Appu_Nair1

Search for examples by Lindsay Davies it does not get any better also see this thread

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=16509548&objAction=viewincontainer&ShowReplyEntry=16510536#forum_topic_16510536

you can instead of doing connect by prior dataid=abs(parentid) you could

do prior dataid=parentid or prior dataid=-parentid

also in newer systems you could use dtreeancestors table so you don't need the connect by

Just thought of passing along small subsets hould be fine with ABS

Hayden Ross

Thanks Appu, forgot about DTREEANCESTORS, this cut down the time dramatically, here was my final report.

select DTREE.PARENTID, DTREE.DATAID, DTREE.NAME, DVERSDATA.VERSIONID, DVERSDATA.DOCID, DVERSDATA.VERSION, DVERSDATA.VERCDATE, DVERSDATA.FILENAME, DVERSDATA.DATASIZE, DVERSDATA.FILETYPE, DVERSDATA.MIMETYPE from DVERSDATA JOIN DTREE ON DVERSDATA.DOCID = DTREE.DATAID JoIN DTREEANCESTORS ON DVERSDATA.DOCID = DTREEANCESTORS.DATAID where DVERSDATA.MIMETYPE like 'video%%' and DTREEANCESTORS.ANCESTORID = %1