Compose performance issue during datamart load

[garyclark]

Hi team,

We are facing performance issue in our compose project during datamart load. Please find the numbers below:
Run 1:Data mart load with full load option -->Took 15 mins to load 14,686,894
Run 2:Data mart load with incremental load option after full load--> Fact table load is taking forever so we had to manually abort the task.
Run 3:Data mart load with full load option and "Optimize for initial load" option checked--> Successfully loaded 12.5 mil records and stuck at fact table load step.
Prior to this run I have cleared data in DM.

Need your assistance to resolve this issue so please do share your thoughts and inputs. Unable to attach diagnostic package due below issue. Can you please provide resolution steps for this as well?

[jtompkins]

Is the step that hangs loading a transactional fact table? We have had our processing hang or take over 6 hours to load for one of our transactional fact tables and we need to manually update the task instruction to remove a DISTINCT and add an ordered hint.

[TimGarrod]

@garyclark  - if this is a product issue, I recommend submitting a support ticket (and attaching the deployment package there).   This is a public forum to ask questions - but I would recommend opening an actual ticket to get support assistance. 

[robertcur]

@garyclark As mentioned in some of my other posts. I'm no expert, but have been using Compose for more than 18 months every day now.  I know in the May 2022 release they have worked on performance issues, but here is something someone in my team accidentally came across that you can try.

In your data mart tables (EDIT), make sure that all columns from the same tables are together.  Thus have your columns in table order.  I.e.

From

Column1 - form Table ABC

Column2 - from Table ABC

Column3 - from Table XYZ

Column4 - from Table ABC

Column5 - from Table XYZ

To

Column1 - form Table ABC

Column2 - from Table ABC

Column4 - from Table ABC

Column3 - from Table XYZ

Column5 - from Table XYZ

This resolved a lot of performance issues for us on the data marts.

[Nanda_Ravindra · Accepted solution]

@garyclark your scenario Run2 and Run3 associated with Full load are both addressed in 2022.05-sp06 patch.

When running Full Load ETL statements, we were loading records directly into the  indexed data mart table using CTE ((Common Table)  and these inserts would take an excessively long time to complete. We have refactored this logic in our latest patch. So, please upgrade to the latest version and open a support case if you are still seeing the issue.

Thanks,

Nanda