Storing nested XML optimization

verban

I found this discussion for string XML structure on multiple tables, but this doesn't help me:
https://forum.vertica.com/discussion/240894/work-with-xml-data-structure-in-vertica
I have fully relational data (next image) that has more than 50 data class. In my case some times one class point to himself (like RelatedIncident or AssociatedActors) or various type of relation (one-one, one-many, ..)
Data Structure:

So i'm using one table for one data class now and there is a foreign key for each table. the foreign key is an Identity (auto increment data type) for each table.
First Issue:
Some times storing whole xml takes more than 1 minute (BEGIN, Insert T1, Insert T2, ..., Insert Tn, COMMIT)).
And load time will be awful if it inserted from multiple source concurrently.
Insert time depends on xml complexity so each message has 20-50 insert statement.
*) i should say i can't use copy statement, because i retrieve ID of each node of xml after insert (by SELECT LAST_INSERT_ID();) and use this ID in his childs as foreign key.

Insert T1 values (.....); --> then SELECT LAST_INSERT_ID(); ---> ID: x
Insert T2 values (x, ....); ; --> then SELECT LAST_INSERT_ID(); ---> ID: y
Insert T3 values (y, ....); ; --> then SELECT LAST_INSERT_ID(); ---> ID: z
Insert T4 values (z, ....); ; --> then SELECT LAST_INSERT_ID(); ---> ID: ...

2nd Issue:
Data query from tables is very slow. I query like this:
select A1.a, A2.a, A3.a, A4.a, ... from T1 A1 join T2 A2 on A2.a_id=A1.id join T3 A3 on A3.a_id=A2.id join T4 A4 on A4.a_id=A2.id join T5 A5 on A5.a_id=A3.id .... where A5.value='blabla' and ... group by 1,2,3,...
I think this is because of multiple join (usually 10-15 join for main classes or lookup value)

verban

any comment?

verban

any comment?

verban

Up!