Report pulling data from category table working sloe

Hi All,

We have a Livereport which is pulling out the data from the category table LLATTRDATA. The report works fine on DEV and QA box but on Prod the performance is very slow. Sometimes even the results are not retured after running the report.

We have several other reports which pulls data from WF table or the DTREE and those are working fine. I guess either there is some performance issue with the LLATTRDATA table or the report query is not correctly structured and needs some tunning. I tired running the query directly on the Database end and still it takes around 40 min to return the results whcih is really very slow.

Below are the no of entries and other detail of thecategory table,
Count of rows in LLATTRDATA= 591948346
It took 185 sec to get the count (select count(*) from LLATTRDATA)

Count of rows, categories on whcih the report is designed = 3394456
took 13 sec to return the count (select COUNT(*) from LLATTRDATA where DEFID in ('25427592', '25427685'))

I have attached the report to this. Can someone please help me out what is wrong? We have done all the tunning from DB end like increasing the indexes for LLATTRDATA table, tunning the performance etc.

Thanks,

Nikhil Sanghvi

LiveReport (1).rpt

Find more posts tagged with

Comments

Colin_Schmidt

I think the problem is the minimal selection criteria.

There could be multiple versions of a document. Do you want all values from all versions? If you want only the latest, you need to get that version from the DTREE and match it against the LLATTRDATA. (llattrdata.id = dtree.dataid llattrdata.vernum = dtree.versionnum) for each llattrdata reference in the JOINs or the WHERE. I prefer to use the "join" and put the selection criteria on the line referencing the table. It makes it cleaner and easier to understand where all of the selection criteria is specific.

I would also specify the Category ID and attribute ID(llattrdata.defid = <number> and llattrdata.attrid=<id#>) on each definition of the LLATTRDATA table. These two would be hardcoded and can be found in the CATREGIONMAP.

Colin J.

On Fri, Jan 8, 2016 at 8:08 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

[Attachments exceeded pre-set size limits. Use the links provided to access]
Report pulling data from category table working sloe Posted bySanghvi, Nikhil DOn 01/08/2016 08:06 AM

Hi All,We have a Livereport which is pulling out the data from the category table LLATTRDATA. The report works fine on DEV and QA box but on Prod the performance is very slow. Sometimes even the results are not retured after running the report.We have several other reports which pulls data from WF table or the DTREE and those are working fine. I guess either there is some performance issue with the LLATTRDATA table or the report query is not correctly structured and needs some tunning. I tired running the query directly on the Database end and still it takes around 40 min to return the results whcih is really very slow.Below are the no of entries and other detail of thecategory table,
Count of rows in LLATTRDATA= 591948346
It took 185 sec to get the count (select count(*) from LLATTRDATA)Count of rows, categories on whcih the report is designed = 3394456
took 13 sec to return the count (select COUNT(*) from LLATTRDATA where DEFID in ('25427592', '25427685'))I have attached the report to this. Can someone please help me out what is wrong? We have done all the tunning from DB end like increasing the indexes for LLATTRDATA table, tunning the performance etc.Thanks,Nikhil Sanghvi

Attachment Links:
LiveReport (1).rpt(5 KB)
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from WestRockCompany (www.westrock.com) or itssubsidiaries, which may be confidential, privileged or otherwise protected fromdisclosure. The information is intended to be disclosed to and used by only thenamed recipient(s). If you are not the intendedrecipient, then your review, use, disclosure, printing, copying, ordistribution of this message or its contents is prohibited. If you havereceived this message in error, please notify WestRock immediately at postmaster@westrock.com, and delete themessage from your system. Unless previously authorized in writing, thismessage does not constitute an offer, acceptance, or agreement of any kind.Sender is not liable for damage, errors or omissions related to or caused bytransmission of this message. © WestRock Company.

nikhil.sanghvi

Hi Colin,

I have included versionnum as well as the category id in the query, I have pasted the query below highlighting the part,

select
NVL(PN.VALSTR, ' ') as SDS_Product_Substance_Name,
NVL(SSS.VALLONG, ' ') as "SDS Substance Synonym",
NVL(VN.VALSTR, ' ') as VENDOR_SUPPLIER_NAME,
NVL(VVN.VALSTR, ' ') as SDS_Version_Vendor_Number,
NVL(CNT.VALSTR, ' ') as COUNTRY,
NVL(LANG.VALSTR, ' ') as Language,
NVL(SIN.VALSTR, ' ') as SDS_Internal_Number,
NVL(CONF.VALSTR, ' ') as Confidential,
NVL(QCS.VALSTR, ' ') as QC_Status,
NVL(SI.VALSTR, ' ') as Site,
NVL(BU.VALSTR, ' ') as Business,
NVL(OTID.VALSTR, ' ') AS SDS_OT_ID
from
LLATTRDATA PN, LLATTRDATA VN, LLATTRDATA VVN, LLATTRDATA CNT, LLATTRDATA LANG, LLATTRDATA SIN, LLATTRDATA CONF, LLATTRDATA QCS, LLATTRDATA SI, LLATTRDATA BU, LLATTRDATA OTID,
LLATTRDATA REG, LLATTRDATA SSS, DTREE DT
where
SIN.VALSTR < 'SDS100000000'
and (DT.DataID=PN.ID and PN.DefID='25427685' and PN.AttrID='2' and DT.VersionNum=PN.VerNum)
and (DT.DATAID=VN.id and VN.DEFID='25427685' and VN.ATTRID='3' and DT.VERSIONNUM=VN.VERNUM)
and (DT.DATAID=VVN.id and VVN.DEFID='25427685' and VVN.ATTRID='4' and DT.VERSIONNUM=VVN.VERNUM)
and (DT.DATAID=CNT.id and CNT.DEFID='25427592' and CNT.ATTRID='16' and DT.VERSIONNUM=CNT.VERNUM)
and (DT.DATAID=LANG.id and LANG.DEFID='25427592' and LANG.ATTRID='17' and DT.VERSIONNUM=LANG.VERNUM)
and (DT.DATAID=SIN.id and SIN.DEFID='25427685' and SIN.ATTRID='7' and DT.VERSIONNUM=SIN.VERNUM)
and (DT.DATAID=CONF.id and CONF.DEFID='25427592' and CONF.ATTRID='18' and DT.VERSIONNUM=CONF.VERNUM)
and (DT.DATAID=QCS.id and QCS.DEFID='25427685' and QCS.ATTRID='29' and DT.VERSIONNUM=QCS.VERNUM)
and (DT.DATAID=SI.id and SI.DEFID='25427592' and SI.ATTRID='11' and DT.VERSIONNUM=SI.VERNUM)
and (DT.DATAID=BU.id and BU.DEFID='25427592' and BU.ATTRID='9' and DT.VERSIONNUM=BU.VERNUM)
and (DT.DATAID=OTID.id and OTID.DEFID='25427685' and OTID.ATTRID='31' and DT.VERSIONNUM=OTID.VERNUM)
and (DT.DATAID=REG.id and REG.DEFID='25427592' and REG.ATTRID='10' and DT.VERSIONNUM=REG.VERNUM)
and (DT.DATAID=SSS.id and SSS.DEFID='25427685' and SSS.ATTRID='21' and DT.VERSIONNUM=SSS.VERNUM)
and PN.ID=CNT.ID
order by
PN.VALSTR ASC, VN.VALSTR ASC, SI.VALSTR ASC

eLink User

Here's a small suggestion as I designed a long time back .LLATTRDATA will become humongous so in our company I started out like you did for a HR related application owner.Everything worked in Dev and Test systems where data was small.However the other department's application which relied on jdbc/odbc hung when trying on production.I replaced my query with what OT suggested and that way I understood the joins much more easily and it made the other application look at it easily as well but again the sheer size of the data defeated the whole purpose.So I changed the view query to a table query so I created a table in 20 minutes or so .Later we started truncating the table and made that part of a informatica ETL job and the department customer was happy.

In these days of replication,materialized views and lots of new technology I am pretty sure you can get snapshots of that data for reporting purposes effectively.However pls feel free to engage OT DB resources as they are awesome people.I know Oracle has a way of suggesting improvements but cannot make out if your tables are sql or oracle.You might also try to use search index in the same way you are constructing the query and see if that is adequate.In really worse cases you might have to engage OT or write tables of just category data that you are interested ,like the ones you may find when you are doing forms(Manage relational data) so in essence if there is a category of interest there will be a simple table of data for that(kind of livelink in 8 days)

Just my $0.02 I don't know queries or databases like Colin does

..appu appnair appunair

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

On Mon, Jan 11, 2016 at 8:01 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Report pulling data from category table working sloe Posted bySanghvi, Nikhil DOn 01/11/2016 08:56 AM

Hi Colin,I have included versionnum as well as the category id in the query, I have pasted the query below highlighting the part,select
NVL(PN.VALSTR, ' ') as SDS_Product_Substance_Name,
NVL(SSS.VALLONG, ' ') as "SDS Substance Synonym",
NVL(VN.VALSTR, ' ') as VENDOR_SUPPLIER_NAME,
NVL(VVN.VALSTR, ' ') as SDS_Version_Vendor_Number,
NVL(CNT.VALSTR, ' ') as COUNTRY,
NVL(LANG.VALSTR, ' ') as Language,
NVL(SIN.VALSTR, ' ') as SDS_Internal_Number,
NVL(CONF.VALSTR, ' ') as Confidential,
NVL(QCS.VALSTR, ' ') as QC_Status,
NVL(SI.VALSTR, ' ') as Site,
NVL(BU.VALSTR, ' ') as Business,
NVL(OTID.VALSTR, ' ') AS SDS_OT_ID
from
LLATTRDATA PN, LLATTRDATA VN, LLATTRDATA VVN, LLATTRDATA CNT, LLATTRDATA LANG, LLATTRDATA SIN, LLATTRDATA CONF, LLATTRDATA QCS, LLATTRDATA SI, LLATTRDATA BU, LLATTRDATA OTID,
LLATTRDATA REG, LLATTRDATA SSS, DTREE DT
where
SIN.VALSTR < 'SDS100000000'
and (DT.DataID=PN.ID and PN.DefID='25427685' and PN.AttrID='2' and DT.VersionNum=PN.VerNum)
and (DT.DATAID=VN.id and VN.DEFID='25427685' and VN.ATTRID='3' and DT.VERSIONNUM=VN.VERNUM)
and (DT.DATAID=VVN.id and VVN.DEFID='25427685' and VVN.ATTRID='4' and DT.VERSIONNUM=VVN.VERNUM)
and (DT.DATAID=CNT.id and CNT.DEFID='25427592' and CNT.ATTRID='16' and DT.VERSIONNUM=CNT.VERNUM)
and (DT.DATAID=LANG.id and LANG.DEFID='25427592' and LANG.ATTRID='17' and DT.VERSIONNUM=LANG.VERNUM)
and (DT.DATAID=SIN.id and SIN.DEFID='25427685' and SIN.ATTRID='7' and DT.VERSIONNUM=SIN.VERNUM)
and (DT.DATAID=CONF.id and CONF.DEFID='25427592' and CONF.ATTRID='18' and DT.VERSIONNUM=CONF.VERNUM)
and (DT.DATAID=QCS.id and QCS.DEFID='25427685' and QCS.ATTRID='29' and DT.VERSIONNUM=QCS.VERNUM)
and (DT.DATAID=SI.id and SI.DEFID='25427592' and SI.ATTRID='11' and DT.VERSIONNUM=SI.VERNUM)
and (DT.DATAID=BU.id and BU.DEFID='25427592' and BU.ATTRID='9' and DT.VERSIONNUM=BU.VERNUM)
and (DT.DATAID=OTID.id and OTID.DEFID='25427685' and OTID.ATTRID='31' and DT.VERSIONNUM=OTID.VERNUM)
and (DT.DATAID=REG.id and REG.DEFID='25427592' and REG.ATTRID='10' and DT.VERSIONNUM=REG.VERNUM)
and (DT.DATAID=SSS.id and SSS.DEFID='25427685' and SSS.ATTRID='21' and DT.VERSIONNUM=SSS.VERNUM)
and PN.ID=CNT.ID
order by
PN.VALSTR ASC, VN.VALSTR ASC, SI.VALSTR ASC
[To post a comment, use the normal reply function]
Topic: Report pulling data from category table working sloe
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Colin_Schmidt

Did the addition of the selection criteria make any difference in performance?

Appu knows more about most things than my tunnel-vision concerning the DB tables, but your original query was too broad and provided little delimiting parameters. That would cause nearly everything to be extracted. To be specific is best in this case. SQL query performance analysis is something I've not done, not being a DB expert, . Being self-taught has its disadvantages.

Colin J.

On Mon, Jan 11, 2016 at 9:01 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Report pulling data from category table working sloe Posted bySanghvi, Nikhil DOn 01/11/2016 08:56 AM

Hi Colin,I have included versionnum as well as the category id in the query, I have pasted the query below highlighting the part,select
NVL(PN.VALSTR, ' ') as SDS_Product_Substance_Name,
NVL(SSS.VALLONG, ' ') as "SDS Substance Synonym",
NVL(VN.VALSTR, ' ') as VENDOR_SUPPLIER_NAME,
NVL(VVN.VALSTR, ' ') as SDS_Version_Vendor_Number,
NVL(CNT.VALSTR, ' ') as COUNTRY,
NVL(LANG.VALSTR, ' ') as Language,
NVL(SIN.VALSTR, ' ') as SDS_Internal_Number,
NVL(CONF.VALSTR, ' ') as Confidential,
NVL(QCS.VALSTR, ' ') as QC_Status,
NVL(SI.VALSTR, ' ') as Site,
NVL(BU.VALSTR, ' ') as Business,
NVL(OTID.VALSTR, ' ') AS SDS_OT_ID
from
LLATTRDATA PN, LLATTRDATA VN, LLATTRDATA VVN, LLATTRDATA CNT, LLATTRDATA LANG, LLATTRDATA SIN, LLATTRDATA CONF, LLATTRDATA QCS, LLATTRDATA SI, LLATTRDATA BU, LLATTRDATA OTID,
LLATTRDATA REG, LLATTRDATA SSS, DTREE DT
where
SIN.VALSTR < 'SDS100000000'
and (DT.DataID=PN.ID and PN.DefID='25427685' and PN.AttrID='2' and DT.VersionNum=PN.VerNum)
and (DT.DATAID=VN.id and VN.DEFID='25427685' and VN.ATTRID='3' and DT.VERSIONNUM=VN.VERNUM)
and (DT.DATAID=VVN.id and VVN.DEFID='25427685' and VVN.ATTRID='4' and DT.VERSIONNUM=VVN.VERNUM)
and (DT.DATAID=CNT.id and CNT.DEFID='25427592' and CNT.ATTRID='16' and DT.VERSIONNUM=CNT.VERNUM)
and (DT.DATAID=LANG.id and LANG.DEFID='25427592' and LANG.ATTRID='17' and DT.VERSIONNUM=LANG.VERNUM)
and (DT.DATAID=SIN.id and SIN.DEFID='25427685' and SIN.ATTRID='7' and DT.VERSIONNUM=SIN.VERNUM)
and (DT.DATAID=CONF.id and CONF.DEFID='25427592' and CONF.ATTRID='18' and DT.VERSIONNUM=CONF.VERNUM)
and (DT.DATAID=QCS.id and QCS.DEFID='25427685' and QCS.ATTRID='29' and DT.VERSIONNUM=QCS.VERNUM)
and (DT.DATAID=SI.id and SI.DEFID='25427592' and SI.ATTRID='11' and DT.VERSIONNUM=SI.VERNUM)
and (DT.DATAID=BU.id and BU.DEFID='25427592' and BU.ATTRID='9' and DT.VERSIONNUM=BU.VERNUM)
and (DT.DATAID=OTID.id and OTID.DEFID='25427685' and OTID.ATTRID='31' and DT.VERSIONNUM=OTID.VERNUM)
and (DT.DATAID=REG.id and REG.DEFID='25427592' and REG.ATTRID='10' and DT.VERSIONNUM=REG.VERNUM)
and (DT.DATAID=SSS.id and SSS.DEFID='25427685' and SSS.ATTRID='21' and DT.VERSIONNUM=SSS.VERNUM)
and PN.ID=CNT.ID
order by
PN.VALSTR ASC, VN.VALSTR ASC, SI.VALSTR ASC
[To post a comment, use the normal reply function]
Topic: Report pulling data from category table working sloe
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

eLink User

What are you trying to do with this less than operator on a string? is that a range brought by A D N .

SIN.VALSTR < 'SDS100000000'

Less than works very differently in string operations.

Just a thought...

..appu appnair appunair

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

On Mon, Jan 11, 2016 at 9:16 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Re Report pulling data from category table working sloe 2 Posted bySchmidt, ColinOn 01/11/2016 10:15 AM

Did the addition of the selection criteria make any difference in performance?
Appu knows more about most things than my tunnel-vision concerning the DB tables, but your original query was too broad and provided little delimiting parameters. That would cause nearly everything to be extracted. To be specific is best in this case. SQL query performance analysis is something I've not done, not being a DB expert, . Being self-taught has its disadvantages.

Colin J.
On Mon, Jan 11, 2016 at 9:01 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:
Re Report pulling data from category table working sloe Posted bySanghvi, Nikhil DOn 01/11/2016 08:56 AM

Hi Colin,I have included versionnum as well as the category id in the query, I have pasted the query below highlighting the part,select
NVL(PN.VALSTR, ' ') as SDS_Product_Substance_Name,
NVL(SSS.VALLONG, ' ') as "SDS Substance Synonym",
NVL(VN.VALSTR, ' ') as VENDOR_SUPPLIER_NAME,
NVL(VVN.VALSTR, ' ') as SDS_Version_Vendor_Number,
NVL(CNT.VALSTR, ' ') as COUNTRY,
NVL(LANG.VALSTR, ' ') as Language,
NVL(SIN.VALSTR, ' ') as SDS_Internal_Number,
NVL(CONF.VALSTR, ' ') as Confidential,
NVL(QCS.VALSTR, ' ') as QC_Status,
NVL(SI.VALSTR, ' ') as Site,
NVL(BU.VALSTR, ' ') as Business,
NVL(OTID.VALSTR, ' ') AS SDS_OT_ID
from
LLATTRDATA PN, LLATTRDATA VN, LLATTRDATA VVN, LLATTRDATA CNT, LLATTRDATA LANG, LLATTRDATA SIN, LLATTRDATA CONF, LLATTRDATA QCS, LLATTRDATA SI, LLATTRDATA BU, LLATTRDATA OTID,
LLATTRDATA REG, LLATTRDATA SSS, DTREE DT
where
SIN.VALSTR < 'SDS100000000'
and (DT.DataID=PN.ID and PN.DefID='25427685' and PN.AttrID='2' and DT.VersionNum=PN.VerNum)
and (DT.DATAID=VN.id and VN.DEFID='25427685' and VN.ATTRID='3' and DT.VERSIONNUM=VN.VERNUM)
and (DT.DATAID=VVN.id and VVN.DEFID='25427685' and VVN.ATTRID='4' and DT.VERSIONNUM=VVN.VERNUM)
and (DT.DATAID=CNT.id and CNT.DEFID='25427592' and CNT.ATTRID='16' and DT.VERSIONNUM=CNT.VERNUM)
and (DT.DATAID=LANG.id and LANG.DEFID='25427592' and LANG.ATTRID='17' and DT.VERSIONNUM=LANG.VERNUM)
and (DT.DATAID=SIN.id and SIN.DEFID='25427685' and SIN.ATTRID='7' and DT.VERSIONNUM=SIN.VERNUM)
and (DT.DATAID=CONF.id and CONF.DEFID='25427592' and CONF.ATTRID='18' and DT.VERSIONNUM=CONF.VERNUM)
and (DT.DATAID=QCS.id and QCS.DEFID='25427685' and QCS.ATTRID='29' and DT.VERSIONNUM=QCS.VERNUM)
and (DT.DATAID=SI.id and SI.DEFID='25427592' and SI.ATTRID='11' and DT.VERSIONNUM=SI.VERNUM)
and (DT.DATAID=BU.id and BU.DEFID='25427592' and BU.ATTRID='9' and DT.VERSIONNUM=BU.VERNUM)
and (DT.DATAID=OTID.id and OTID.DEFID='25427685' and OTID.ATTRID='31' and DT.VERSIONNUM=OTID.VERNUM)
and (DT.DATAID=REG.id and REG.DEFID='25427592' and REG.ATTRID='10' and DT.VERSIONNUM=REG.VERNUM)
and (DT.DATAID=SSS.id and SSS.DEFID='25427685' and SSS.ATTRID='21' and DT.VERSIONNUM=SSS.VERNUM)
and PN.ID=CNT.ID
order by
PN.VALSTR ASC, VN.VALSTR ASC, SI.VALSTR ASC
[To post a comment, use the normal reply function]
Topic: Report pulling data from category table working sloe
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Report pulling data from category table working sloe
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

MMTM_Advanced_Recognition_HOL.pdf

D'Souza_Aubrey

Hi Nikhil,

I had designe a similar query a long time ago. I recall using max(version) for the category. Depends on what your needs are. Take a single document example. This document can have a number of versions, each with a category value attached to it. Add to that, different versions of a category that can be added to the document. Thus, you not only require the document version, you also require the category version.

Another line of thought is to go into the Admin Pages and look into database maintenance.

Navigate to the Admin Pages
Database Administration > Maintain Current Database > Verify Database
Run a Level 6 Diagnostics and check for errorrs and the like.
Based on the results, you can then try to fix these errors from within Maintain Current Database > Test or Repair Known Database Issues. Ensure you do a full back up of the system before you attempt to fix errors, just as a precaution.

Not saying this will necessarily fix your query issue, but you never know sometimes, the answer lies elsewhere.