Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
s33pie007
Creator
Creator

group-by over 200 million rows

Hi everybody,

I have a problem with the reload time of one of my apps I created.

it looks as follow:

a.png

the table consist of just over 200 million rows, and the reload time takes around 4 hours to complete.

Is there any way of reducing the reload time of this? If so, please let me know

Thanks

Stefan

1 Solution

Accepted Solutions
jsn
Employee
Employee

Hi Stefan,

Since you're aggregating by transaction day I'm not sure if you have 200 million transactions per day or in total. If that's the total table then you could surely cut down the time taken by using incremental loading.

I.e. instead of aggregating everything every day you store away the aggregated data in a QVD and just load the new records each day, aggregate them and replace the aggregated QVD.

The incremental load scenario will of course depend on whether you're just adding records or there are modifications/deletions being made to existing transactions as well as you'd need to re-aggregate those days as well. This can all be scripted though and if you search for "Incremental Load Scenarios" on the community you'll find a good baseline PDF with examples.

View solution in original post

6 Replies
settu_periasamy
Master III
Master III

Hi Stefan,

Did you try to store your "TEMP FCT BASKET TRN" into QVD.

Then,  Sum and Group by from the QVD Source ?

tresesco
MVP
MVP

Increasing hardware capacity is surely an option. Apart from that, I would try to reload it from a qvd rather than a resident load. While your hardware is not enough, resident load may suffer a bit.

Kushal_Chawda

Is there any specific reason of doing group by in Script?

jagan
Partner
Partner

HI,

Instead of doing Group By why can't you directly use Sum(Amt) and Sum(Qty) fields wherever you required?  If it taking more time to reload then try without doing Group by and handle this using Sum() in frontend.

Hope this helps you.

Regards,

jagan.

jsn
Employee
Employee

Hi Stefan,

Since you're aggregating by transaction day I'm not sure if you have 200 million transactions per day or in total. If that's the total table then you could surely cut down the time taken by using incremental loading.

I.e. instead of aggregating everything every day you store away the aggregated data in a QVD and just load the new records each day, aggregate them and replace the aggregated QVD.

The incremental load scenario will of course depend on whether you're just adding records or there are modifications/deletions being made to existing transactions as well as you'd need to re-aggregate those days as well. This can all be scripted though and if you search for "Incremental Load Scenarios" on the community you'll find a good baseline PDF with examples.

jonathandienst
Partner
Partner

@Johannes - Excellent suggestion. This is good example of where incremental loading can assist with large scale applications.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein