LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: pauldasari

Please help me guide to filter out data from a huge brd file.
Lets say i have only employee_id in a tableA and i have the brd already available with all the employee details along with employee_id and need to filter out all the employee_id that is in the tableA and only keep rest of the details in the brd.

I could not use the x-ref related node because the it takes huge amount of time to complete and error out with buffer exception.

Thanks
Paul

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

If I understood your request properly, you want any record from table B that is NOT on table A.

Use the Lookup node. connect table A to the 2nd input pin, put table B on the 1st input pin, specify Employee_ID for the two lookup keys.

Then, on the output tab, use this code:

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: pauldasari

Thanks for your response.
I will try your suggestion and keep you posted, just wanted to know by doing with the lookup node would it have a huge performance impact and fails with a buffer exception?because the size of the data is about 25mil to 30mil rows...

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: pauldasari

I get the following error when tried your suggestion..
WARN: Maximum buffer size of 536870912 bytes exceeded. Aborting.
Error Code: brain.node.main.cpp.291

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ltolleson

Paul,

You should not use a lookup with 20M records in your lookup dataset. This would most likely throw and exception. The x-ref node is the correct node with the amount of data you have.

There are 2 LAE Server properties that are limiting your ability to run this amount of data. If you are on an Enterprise server you will need to get with your system administrator to change these values to allow for a larger buffer size. This will consume more memory during the running of the node, so please be aware that increasing these values can have an adverse affect on your server.

Property Type Value
ls.brain.node.join.maxbuffersize String 536870912
ls.brain.node.lookup.maxbuffersize String 536870912

The other option is to run the HashSplit node on the data and split both sets of data by a common key. This can be any key, but is most effective when the key is a numeric value. I would guess that you could use the Employee_id field as your split key. Attached is an example of how this would work. I split the data by 5, but you can add more if needed. The number of outputs should be a prime number (5, 7, 11, etc...). Because the way the HashSplit works you will always end up with like values in the same output (1-5). In this example you will see that I also added an x-ref directly to the datasets to show the output should be the same.
Attachments:
HashSplitExample.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: pauldasari

doing this is error out with buffer exception, any other suggestions is helpful
Thanks
Paul