QML Report

[DD_11312030]

I am using Windchill PDMLink Release 12.1 and Datecode with CPS 12.1.0.2

We intend to extract all relevant information and generate a consolidated file that maps each manufacturer part to its corresponding impacted end part (Commercial Reference Part, i.e., WTPart). Our system currently contains approximately 100,000 manufacturer parts, and this activity will ensure complete traceability and alignment between manufacturer parts and associated WTParts.

While the query performs correctly for individual parts, it does not scale effectively for large datasets.
When executed for the complete dataset of approximately 45000 manufacturer parts, the query requires more than 12 hours and still does not complete the report generation. The performance degradation makes the current approach impractical for production use or recurring data extraction activities.

[Marco_Tosin]

The query result depends on how it is executed.

Obviously, the larger the dataset to be retrieved, the longer it takes to complete the query.

This is the first time I’ve heard of someone running a report and not getting a result after 12 hours.

How many GB of RAM does your installation have?

Most installations have a maximum limit set for the number of results that can be returned, to prevent the report from running unnecessarily without producing results or from saturating the system’s RAM.

My advice is to “split” the report into multiple reports by filtering, for example, by supplier groups or some other attribute relevant to your needs.

[avillanueva]

Can you post your QML? Perhaps there is something inefficient with it. Your DBA should also be able to track long running queries, likely related to this and see about tuning to make it run better or use an index as opposed to full table scans. 

I have a QML that reports on Buy parts, part classifications and the AML/AVL. It does not take too long to run (about 10K parts) but have noticed first execution fails or times out. If I repeat the query, data might be cached and it does succeed.

For extremely large reports, I typically use a straight SQL call and SQLDeveloper or Toad for Oracle. They allow export straight to Excel for any analysis. 

[joe_morton]

This is almost certainly an issue with the report structure. Reports can run very quickly for large amounts of data if they're simple. Performance issues occur when the report is not simple.

Without seeing the report format directly, the general advice I can give is to avoid any "IF" logic. For example, this report will perform poorly at scale: Return all manufacturer parts, and show "IF" they have a particular type of document linked. You'd want to break this by using 2 reports. Example methods:

• Report all the manufacturer parts that don't have the document, and separately all the manufacturer parts that do have the document. You could then merge these 2 reports afterwards, outside of Windchill
• Report all the manufacturer parts, regardless of document linking. Separately, report all manufacturer parts that linked to the document. Merge these reports afterwards