topic Large data load hanging in QlikView

Large data load hanging

danielact — Fri, 27 Jun 2014 19:24:42 GMT

I have a script which brings in data from a SQL server. The source table is enourmous - multiple billions of rows of data. In the SQL query, it gets aggregated down to about 700 million rows.

This ran perfectly fine for over a year. The source dataset continued to grow as new data came in. Then, one day, it just started hanging. The job wouldn't fail, it would just go on indefinitely. The load used to take about 4 hours. Suddenly, it was running for 24+ hours before we'd abort it.

We ran the query in SQL directly, and it ran in 4 hours. I brought the data straight into QlikView without aggregating, and tried to do the aggregation in the script on the QV side instead of the SQL server. It loaded the table just fine from SQL, then failed on the aggregation.

Any ideas why this might be happening?

Re: Large data load hanging

ashfaq_haseeb — Fri, 27 Jun 2014 19:33:00 GMT

Hi,

It usually happens with huge data with aggregation incolved.

For sure aggregation at qlik level is different then that of database level. Its obvious it will take time.

I could think of below things

1) you can try by increase resources like RAM and CPU cores at server level.

2) If not, then try by processing yearly data separately and storing them in yearly qvds so that you have limited data to process.

Hope it help.

Regards

ASHFAQ

Re: Large data load hanging

maxgro — Fri, 27 Jun 2014 19:52:23 GMT

Is there some field in SQL table you can use to split the aggregation? Month? Year?

I would split the work in smaller pieces; every piece will aggregate at the database (sql) level and store in a qvd.

Re: Large data load hanging

Thu, 03 Jul 2014 05:00:15 GMT

Hiiii,

I also think that this hanging issue is due to enormous data you have stored, you store your data in spearate fields of Sql and also increase the processing speed of

server. You may also check it can be a problem with the validation of connections.

Further, you may also go through the interactive and resourceful online video tutorial:- http://www.youtube.com/watch?v=8olMt2AOUJ8