topic Re: Job Failing on left join with 2 Billion records against 9 million records in QlikView

Job Failing on left join with 2 Billion records against 9 million records

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I am trying to execute a job by joining two tables, one with 2 Billion records against the other with 9 million records. Job is failing to execute, throwing out a general script error. we have around 400GB RAM on our server.

Could some one explain the limitation with joins in Qlikview.

Thanks.

Re: Job Failing on left join with 2 Billion records against 9 million records

jonathandienst — Mon, 07 Nov 2016 05:38:28 GMT

That is a large join. You may simply be running out of RAM. Have you monitored memory use of the model? Is it in an outer join? What is the cardinality of the join key (the common fields)? Are there common fields?

Re: Job Failing on left join with 2 Billion records against 9 million records

Gysbert_Wassenaar — Mon, 07 Nov 2016 07:15:06 GMT

Is the join necessary in the first place? If you don't need to do calculations in the script that use fields from both tables together you probably don't need to join them. Perhaps using Keep instead of Join will do the job. Or perhaps you can use a mapping table and the Applymap function instead of a join.

Re: Job Failing on left join with 2 Billion records against 9 million records

Tue, 08 Nov 2016 06:03:27 GMT

Thank you for the reply Jonathan,

I have 400GB RAM in my server. I hope that is enough for this but I am not sure. And also I didn't monitor memory use of model. I there any way to monitor the memory use in qlikview itself or do I have to check it in Task manager?

Also is there any way to calculate the amount of RAM that requires for execution of the file?

Re: Job Failing on left join with 2 Billion records against 9 million records

Tue, 08 Nov 2016 06:10:35 GMT

Thank you for the reply Gysbert. I have few calculations in the table such as formatting the dates, setting up the flags and concatenating the fields.

May I know how fast the execution gonna be, if I go for keep instead of join?

Re: Job Failing on left join with 2 Billion records against 9 million records

Gysbert_Wassenaar — Tue, 08 Nov 2016 12:05:21 GMT

If you don't need to do calculations that involve two fields from two different tables then you probably don't need a join to make sure the two fields are in a single table. A keep will be a lot faster since it won't have to create a new table.