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; } } }

LLATTRDATA table is too large for queries

Mikhail_Guskov

Hello!

Time ago our team made custom view by OScript module. That module uses SQL-script to retrive data for view. SQL-query searches for CS-objects in the database for the values of attributes of objects. The most used table is LLATTRDATA.

Everything was fine until the number of rows in the LLATTRDATA has grown more than 30 millions. SQL-query execution time is longer than 10 minutes.SQL-query optimized, all the necessary indexes included. The trend is that during the year the number of rows will exceed 6 billion.

Is there a recipe how to solve the problem of performance when such huge amounts of data?

Enviroment: Content Server 10; Oracle Database.

Find more posts tagged with

Comments

eLink User

Talk to OT Professional services(Probably Australia from Developer Notes).They have a oscript module that will emulate the Livelink 8 type of category reporting,in case you are not familar Livelink 8 had each category's metadata in its own table.Just like when you add a form template you see an option called "Manage Relational Table" on installing this code you will get the same and you define a table.So in essence on create,update or delete this table is maintained along with llattrdata. But when you write queries you are not fighting it out in llattrdata territory.This module is a life saver here.

Alternatively use a DB mechanism to create temp tables out of LLATTRDATA for your category.

.....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 Thu, Jan 21, 2016 at 7:26 AM, eLink Entry: Content Server Development Forum <development@elinkkc.opentext.com> wrote:

LLATTRDATA table is too large for queries Posted byGuskov, MikhailOn 01/21/2016 08:20 AM

Hello!Time ago our team made custom view by OScript module. That module uses SQL-script to retrive data for view. SQL-query searches for CS-objects in the database for the values of attributes of objects. The most used table is LLATTRDATA.Everything was fine until the number of rows in the LLATTRDATA has grown more than 30 millions. SQL-query execution time is longer than 10 minutes.SQL-query optimized, all the necessary indexes included. The trend is that during the year the number of rows will exceed 6 billion.Is there a recipe how to solve the problem of performance when such huge amounts of data?Enviroment: Content Server 10; Oracle Database.
[To post a comment, use the normal reply function]
Forum: Content Server Development Forum
Content Server: Knowledge Center

eLink User

or just call SearchAPI from oscript.Most queries against category can be adequately be run from search.

.....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 Thu, Jan 21, 2016 at 8:09 AM, eLink Entry: Content Server Development Forum <development@elinkkc.opentext.com> wrote:

Re LLATTRDATA table is too large for queries Posted byeLinkOn 01/21/2016 09:09 AM

Talk to OT Professional services(Probably Australia from Developer Notes).They have a oscript module that will emulate the Livelink 8 type of category reporting,in case you are not familar Livelink 8 had each category's metadata in its own table.Just like when you add a form template you see an option called "Manage Relational Table" on installing this code you will get the same and you define a table.So in essence on create,update or delete this table is maintained along with llattrdata. But when you write queries you are not fighting it out in llattrdata territory.This module is a life saver here.Alternatively use a DB mechanism to create temp tables out of LLATTRDATA for your category.

.....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 Thu, Jan 21, 2016 at 7:26 AM, eLink Entry: Content Server Development Forum <development@elinkkc.opentext.com> wrote:

LLATTRDATA table is too large for queries Posted byGuskov, MikhailOn 01/21/2016 08:20 AM

Hello!Time ago our team made custom view by OScript module. That module uses SQL-script to retrive data for view. SQL-query searches for CS-objects in the database for the values of attributes of objects. The most used table is LLATTRDATA.Everything was fine until the number of rows in the LLATTRDATA has grown more than 30 millions. SQL-query execution time is longer than 10 minutes.SQL-query optimized, all the necessary indexes included. The trend is that during the year the number of rows will exceed 6 billion.Is there a recipe how to solve the problem of performance when such huge amounts of data?Enviroment: Content Server 10; Oracle Database.
[To post a comment, use the normal reply function]
Forum: Content Server Development Forum
Content Server: Knowledge Center

[To post a comment, use the normal reply function]
Topic:	LLATTRDATA table is too large for queries
Forum:	Content Server Development Forum
Content Server:	Knowledge Center

Hans Stoop

One option is to create a facet for the attributes you want to query and make your query against the facet tables.

Using search is indeed another good alternative and with the custom view search forms you have an easy to use UI.

Hans

From: eLink Entry: Content Server Development Forum [mailto:development@elinkkc.opentext.com]
Sent: Donnerstag, 21. Januar 2016 14:27
To: eLink Recipient
Subject: LLATTRDATA table is too large for queries

LLATTRDATA table is too large for queries

Posted by Guskov, Mikhail On 01/21/2016 08:20 AM

Hello!

Is there a recipe how to solve the problem of performance when such huge amounts of data?

Enviroment: Content Server 10; Oracle Database.

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

John Simon

If you are an Oracle shop then another option is table partitioning and using parallelism. I am not familiar enough with SQL server to say if they have these same concepts (but I would guess it does).

Cheers...

HFCDev

Similar to Appu's suggestion, at Suncor, they were (maybe still are) using a module from VDR to "flatten" particular categories. Just like the Category tables from Livelink 8 that Appu described, you'd have one table per category and one row per object that had the category. I don't know if this module is even a product or it was a one-off for Suncor's 9.7.1 installation.I also don't know how well this work work for multi-valued attributes (this feature was central to the decision to change the category model from the one in LL8 to the one in LL9.x and Content Server today).

Just me thinking off the top of my head, you could either place a trigger on LLAttrData to write data for a particular value of CatID to its own flattened structure, thus avoiding doing anything to Content Server. Then your reports could leverage that table instead. There would be effort of course to initially populate it with the values you currently have.

-Hugh Ferguson

HFCDev

Just re-read Appu's post. he covered the db trigger idea already. And I'm willing to bet that the VDR piece and OTPS Australia module are one and the same.

-Hugh

David Templeton

Hugh

Then as already suggested, why not use dynamics column or facets… supported by the core server. No add-on needed. Btw, this was one of the reasons this infrastructure was developed. Any queries against llattrData simply really suck!!!! Yeap I said it, since the data is in a normalized form and unless there is very simple reporting, that means no multiple inner joins on itself, the optimizer starts doing tables scans like crazy.

In most cases, for reporting, you only need a subset of the total data ( <20%, more like 10% ) then set up the system to do efficiently.

With the columns and facets you need to display them, they will be maintained and the indexes etc is maintained by the system. In the end don’t fight the system, and what worked yesterday may not be valid today since we do make improvements, reallyJ

David Templeton

Abramov_Alexei

Quoted Hugh Ferguson on 02/04/2016 05:40 PM:

Just re-read Appu's post. he covered the db trigger idea already. And I'm willing to bet that the VDR piece and OTPS Australia module are one and the same.

-Hugh

Hugh,

Do you know the name of that module, by any chance?

John Simon

I believe it is attributesync.

Cheers...

David Templeton

Can someone please tell me why columns and facet tables are not the recommended solution here? No triggers etc, no additional software.

David

Hans Stoop

Hi David,

I normally would not advice to use the columns table, because they combine values of multi-values attribute and also truncate the complete result.

I expect the value is also converted to strings, which makes sorting of integer values a challenge. Therefore you should not recommend the column table in general unless you specify the complete caveat.

Using the facet table would be the best solution in this case.

Hans

From: eLink Entry: Content Server Development Forum [mailto:development@elinkkc.opentext.com]
Sent: Freitag, 5. Februar 2016 19:56
To: eLink Recipient
Subject: RE Re LLATTRDATA table is too large for queries

RE Re LLATTRDATA table is too large for queries

Posted by Templeton, David On 02/05/2016 01:55 PM

Can someone please tell me why columns and facet tables are not the recommended solution here? No triggers etc, no additional software.

David

[To post a comment, use the normal reply function]
Topic:	LLATTRDATA table is too large for queries
Forum:	Content Server Development Forum
Content Server:	Knowledge Center