Performance Issue With Large Staging Table

[tanmoypalit]

Hi Ramesh,

I am also building my model from XML schemas. The model was built and also mapped to the relational database using a staging table.

Following is the diagram (also attached) of the model which has three levels (Please note that Unique_Identifier and Top_MD combines to be the primary key in the staging table query and both the Mapping Class was mapped to the same staging table columns)

Although the model works but it has serious performance issues. It takes 8 min to retrieve only 10 records.

As you have mentioned that XML Document Model has performance implications, it would be great if you can help me with the above scenario. I am using Teiid 7.7

Regards

Tanmoy Palit

[shawkins]

Tanmoy,

Explicit staging tables should currently be used with caution.  Depending on how the document model uses them,\ they can have a negative impact on performce.  In short a staging table is just a set of records without any indexes, so repeated access against large row counts can be problematic.  You should normally try your query without the staging table to see if the default xml planning is good enough.  The xml planner will automatically create staging tables for small results and will use dependent joins between parent and child mapping classes when possible so that the children are not iteratively fetched.  You can also consider using a materialized view instead of the staging table as that can be properly indexed.  Otherwise there are existing Teiid/Teiid Designer issues on staging tables that would need to be worked to enhance their performance either in explicit or implicit ways.

Steve

[tanmoypalit]

Thanks Steven for the reply.

I used a simple XML Schema (see Img1) without the staging table (keeping the database query same as before I created a view) and the performance is as expected.

But my original XML Schema has 2 mapping classes and I get all my data from single query (each part of the unique key needs to be mapped to a different mapping class).

Is it possible a map two different mapping class to the same view model object without using Staging table (see Im2)? I tried this and it did not work.

Regards

Tanmoy Palit

[shawkins]

> Is it possible a map two different mapping class to the same view model object without using Staging table (see Im2)?

There are a couple of considerations here.  Are two mapping classes needed to represent a parent with n children? 

If you are representing a 1-1 relationship, then two mapping classes are not needed and you should be able to map the parent/child element to a single mapping class. 

If there are multiple children and this logically represents a self join, then you should just let each of the mapping class queries separately reference the same view and use a mapping class input set to join between the parent and child.  The xml planner should be able to handle that efficiently.

If you are required for whatever reason to manually create a single result (like the staging table approach) and pull the child information from that result, then the staging table is currently the most straight-forward way of doing that - however without enhancements you'll hit a performance issue.

Steve

[tanmoypalit]

Hi Steven,

Thank you very much.

My mapping class do have multiple children so I mapped with the same view twice and it worked really well. I have tested and performance looked fine till now.

For knowledge sharing purpose,

I made a small adjustment as in the top level mapping class Unique_Identifier should not be repeated so I used DISTINCT in the first mapping query.

Again thanks!!

Regards

Tanmoy

[shawkins]

Tanmoy,

Just an update, https://issues.jboss.org/browse/TEIID-2338 should address similar performance issues with explicit staging tables with 8.3 and later.

Steve