Large data load hanging

[danielact]

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?

[ashfaq_haseeb]

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

[maxgro]

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.

[#4]

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