Performence issus when making 6 deeps

[jlolling]

Performence issus when making 6 deeps

[jlolling]

Hi blag

yeas of course! I know such scenarios very well. We had such scenarios with database selects and when we had calculated the necessary queries it was terrible.
Thats because I have introduced the foreign key assignment to the tJSONDocOutput.
The idea is to read every level only once and not per parent node and add the current read object just to the right parent object.
The first flow have to got over the first level nodes.
Mark the column which is the key s key in the schema.
The components provides a list of the keys as SQL-comma separated list.
In your scenario you do not need this.
You have to have in your files for the second and following levels columns which references to the parent level.
You choose such this column per level and tell the component to use it a foreign key relation.

This is a screenshot of such a job which fetches the data level by level.
Here the setting for one of the second level components:

[blag001]

I'm definitely going mad on this one...
The FK mode can't find my key and blow-up every time.

I think the point is really about making a composite key that tell the region1 in paysA>region1>depA1 is not the same than paysB>region1>depB1

The job

the error

Create Doc

csv schema of pays

The first node

2nd CSV

region node

last CSV

And the belonging docOutput

---

If I disable the 3rd CSV/output (departments level), I'll have 1st and 2nd level as I want, but the 3rd and deeper level either go on error on CODCOURT2=00 or use CODCOURT2 as a unique key and put everything in the last occurrence of the key (aka all my default departments for the world end up in the Zimbabwe default region...)

[jlolling]

I agree with you, the limitation to only one key field is not comfortable. Mostly we found easy ways to build one combined key. I use the component tHashRow to do that (the result of a cooperation with some friends): https://github.com/jlolling/talendcomp_tHashRow
We have developed this component for the purpose of building keys for a Data Vault.

[jlolling]

I you have further more problems with it we can do a skype session and I take a look at your jobs and we fix everything together.

[blag001]

Thanks again Jan,
As I've got your confirmation tJSONDoc was not able to handle native composite key, I was able to try other things and find how to do it.

I'm working on a Plateform version, so I used tGenKey to make a composite key as a single column instead of your tHashRow. My fear was having to ship this pk_key in the generated JSON dump, but it even seem to work without!

I'm now able to make my 2h30 job in less than 120s, quite a nice change~

I'll add some screen-shot tomorrow.

[jlolling]

I know very well whats your pain. We have created web services with these components and those web services have to provide a multi-object response, e.g. 1000 products at once in one response.
It was terrible - we had minutes instead of seconds as response time. With the foreign key solution we was able to reduce the response time from 2-3 minutes to 1-2 seconds.

I have thought about combined keys but this what increase the complexity of the component to a not maintainable level.