What is best approach instead of DB lookup?

[swaroopchenchu]

I have two Entities which one-to-many relation in mssql server

Entity A - Fields(id, age, gender, ActivationId, ... etc)

Entity B - Fields(id, Activation ,....etc)

so A.ActivationId == B.Activation

Entity B can have multiple records with same ActivationId

Earlier I was using DB lookup for Entity B with ActivationId came from response of query of InputTable of A.

DB lookup gets the top one record(thats fine for me) but it taking humongous time i have almost 2.8 million records, Hence i had to switch to incorporate in Input Query Itself with left join but later realized multiple rows from Entity B makes new record in destination. so count is not matched from source to destination.

Eg: LEFT JOIN QUERY
accIdn.idType, accIdn.idNumber, accIdn.expiryDate are newly placed in query

SELECT
// other fields of acc and con table
, accIdn.idType as AccIdentificationType
, accIdn.idNumber as AccIdentificationNumber
, accIdn.expiryDate as AccIdentificationexpiryDate
FROM ACCOUNT acc
LEFT JOIN XCONTACT con ON con.account = acc.BAN and con.type_=1
LEFT JOIN AccountIdentification accIdn on accIdn.BAN = acc.BAN
WHERE acc.spid = 410
AND acc.state != 2
AND acc.systemType !=0
AND (acc.parentBAN = '' OR acc.parentBAN IS NULL)
ORDER BY
acc.BAN

what is the best way to handle multiple records of Entity B? is it from query or other transform etc?

Any help is appreciated.

Thanks

[bamaer]

Have you looked at the merge join transform? Also: DB Lookup can be fast if you enable caching, but caching requires memory.
As a rule of thumb: lookups only get the first matching record, join transforms (merge join, database join etc) can generate new records if multiple matches are found.

[liosc]

IMHO the approach should be chosen considering a variety of factors (number of records of each table, record length, growth rate, available resources, ...). For example, if you have 2.8 millions rows but the record length and the growth are very limited you could bring in all rows. BTW the database lookup should be a good option, if it is taking humongous time probably there is no index suitable for the search. Sometimes there is an index that seems good but for some reasons the query optimizer is not using it. In many cases, It is useful to check the query plan. If database lookup is very slow, also other strategies could result in poor performances.

[swaroopchenchu]

Thanks bamaer and liosc
Since i am picking only record from second entity, i went ahead with rank in query and that solved for one pipeline.

but i have other pipeline where i do need all multiple records and then make it into list and then place at once so that primary key of first entity will go same as field in second Entity eg: A.ActivationId == B.Activation
These entity have 133Million and 125 Million records.

I think hop doesn't allow two Table input transform in one pipeline, if it had we could have query in second table input with ActivationId coming from first table Input transform.

I was migrating these two entities separately, but source system primary key is of GUID(eg: 163D24B4-88FC-E811-9F80-000C2979F806) type, with guid we cant scale in destination system
API performance is blown.

Hence two entities have to be handled in single pipeline to place primary Key same in other field of second Entity.

since these Entity's are huge. Any ideas to solve this?

[hansva]

You can use the database join transform, but it might be faster to just load both tables with separate table inputs and then use a join transform.

[bamaer]

Sure you can have multiple table inputs in one pipeline. You could even chain them and let the first take input get the ids that the second one uses as an input parameter (? syntax, check the docs). The options are there, but it all depends on your use cases, database performance, available memory etc.

[swaroopchenchu]

hop version: 2.7.0, 16GB RAM for hop pod and 8gb for heap space.

HOP_OPTIONS: -Xmx8g -XX:

source and destination resources looking okay

I used database join, pipeline is executing fine but it took 20 minutes for 1 million records processing.
As I previously mentioned, since i have 133 million to process, is there any other better way, we can fasttrack this?

[bamaer]

We already suggested merge join and chained table inputs, did you look into those options?

[swaroopchenchu]

thanks for the suggestion

1. The limitation with chained Input Table transforms is that the second Input Table does not forward fields produced by the first Input Table. Although those fields can be accessed inside the second transform through the ‘?’ parameters, they are not carried forward to the rest of the pipeline.
   proof:
   left pane with first input table output fields
   right pane with second input table output fields

2. As far as merge join(LEFT OUTER JOIN), defeats my purpose
   Because if second entity have multiple records, we don't want to place new record in first entity of same data.
   Imagine first entity have one record and second entity have two records for that Id.
   we have to place once in first Entity, with the same Id we generated we have to place two records in second entity.

Database joins allows us to do this, but need better approach to run it in migration window.

Thanks Again