Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Loading a QVD Efficiently

Hi All,

I am working with a big table (200M rows), and having a response time issue

I'm using the table in two phases:

phase 1: loading up the table, renaming, and filtering by date (this takes about 5 minutes)

FactClicks_temp:

LOAD JobId as JobId,

  date(daystart(DATE)) as Date,

     CampaignId as CampaignID,

     PageType

FROM [1_raw\FactClicks.qvd] (qvd)

WHERE DATE>='2016-01-01';

STORE FactClicks_temp into [2_dw\FactClicks_temp.qvd] (qvd);

phase 2: group by, and using Sum If to create new columns (this takes about 20 minutes)

FactClicks:

LOAD JobId,

  Date,

  CampaignID,

  Cost,

  ClickSourceDesc,

  IsMobile,

    SUM(IF(PageType...) )

FROM [2_dw\FactClicks_temp.qvd] (qvd)

GROUP BY JobId, Date, CampaignID

Why phase 2 takes so much time? is it becuase i'm working on a non optimized load?

can I do something?

Thanks,

Nir

1 Solution

Accepted Solutions
avinashelite

its because of using GROUP BY statement in the second phase ...as the number of columns increases in the group by statement it will kill the time ....try to push the grouping to SQL and then import the data to Qlikview, this will help you to improve the performance

View solution in original post

9 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Neither of these loads will be optimised. The second one takes longer because of the group by.

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

its because of using GROUP BY statement in the second phase ...as the number of columns increases in the group by statement it will kill the time ....try to push the grouping to SQL and then import the data to Qlikview, this will help you to improve the performance

sunny_talwar

I have learnt it a hard way, but aggregating the data by using Group By statement slows down the reload speed.

jagan
Luminary Alumni
Luminary Alumni

Hi,

You are doing calculations Sum() and Group By(), so it will take time based on the data volume.  Instead of this why can't you directly do the Sum() in front end (Charts).

Regards,

Jagan.

Anonymous
Not applicable
Author

thank you,

Isnt' it better to the id on the SQL load?

Colin-Albert

With 200M rows I would look at implementing an incremental load so you are only processing modified rows and do not need to group and sum on all 200M rows every time you reload the data.

Whether it is better to process data on your SQL server or Qlik Server depends on which server has the available capacity for this processing. Both can be equally valid options.

mightyqlikers
Creator III
Creator III

Hi,

load raw qvd first

then do calculations by taking resident load

and do phase 1 save qvd

next take

do the sum()

group by using resident load of the raw table.

this might help you to fasten the reload time.

Regards
$@m.

Anonymous
Not applicable
Author

This is a great & helpful suggestion!

Anonymous
Not applicable
Author

I still dont understand why the resident load in Qlikview is slow,

why is the MSSQL server is so much better in this kind og aggregation?