Long refresh times

[#1]

Hi All,

I am reasonably new to Qlikview and am having trouble with a couple of my Pivot Tables.

When I reload my data, or add an expression, I get an egg timer and a green bar on the 2 pivot tables on my active sheet and it takes a good 20-30 minutes to unlock.  During this time I am unable to do anything in Qlikview and it goes to a (Not Responding) state.

Is there perhaps something wrong with my report, or is this down to the size of the data I am pivoting against (there are over 2 million lines in a number of my tables)?  As you can imagine, this can make working with it quite tiresome.

Thanks

Lee

[#2]

Hi Lee,

response times in QlikView depend on many different aspects :

• hardware capabilities (mostly RAM as QV is a In-Memory-Database)
• data structure in your table(s) and relations between tables
• formulas

Can you give some more details according to the points mentioned?

Regards,

Rolf

[mdmukramali]

Dear,

Lee.

Those issues araises due to Rolf mentioned Points.

if you have more records in table then it's better to create QVD's for those tables because the application will retrive the data from QVD's more and more faster then Database.

after that you need to perform incremental load.

hope this one will help you.

Thanks,

Mukram.

[#4]

Hi Rolf,

I am currently developing this report on a Win7 Desktop PC (3.1 AMD Quad, 8GB RAM). Once completed the data will be refreshed on our server, and the data flexed by the end user on a desktop of a similar build.

If the refresh was only drawn out following reload I wouldn't be concerned, but if each user has this kind of delay every time they want to change their selections I can see it becoming an issue.

The size of the tables I think are the main cause - I have 27 tables with between 3 and 15 fields each, a number of them exceeding 2 million rows (one is as much as 14 million).  Most of the tables are linked through one linkref.

With regards to formulae, individually they shouldn't have much of a drain on resource as they are reasonably simple (e.g. sum(if(month(date_field_1)='Jan',number_field_1,0)) ), but I can see how this being performed on several million rows can slow things down.

Mukram - the report I am working on is pointing at QVDs created by a seperate report.  The load time is great when grabbing the data, but the flexing still seems to 'freeze' when merely changing my current filter.

Thanks both for your responses - I will try running the report on our server and see how it goes, but if there are any things that I could do to improve the updating times of my pivot tables that'd be great.

Lee

[#5]

Hi Lee,

its me again with useless tips.

If you plan to work with huge or complex datasets, you should spend some time in data-modelling.

For example you might find it usefull to create ( in the loading script) a second table from your data showing a preaggregated view to reduce memory usage and enhance response time.

Second try to do all possible transformations at loading time. In your formula you use month(date_field_1). Instead of this add

     month(date_field_1) AS month_date_field_1,

to your load statement. If I have datasets with Date fields, I always add year, month, calendar week, day and weekday as I need these regularly.

Regards,

Rolf

[#6]

Hi Lee,

its me again with useless tips.

If you plan to work with huge or complex datasets, you should spend some time in data-modelling.

For example you might find it usefull to create ( in the loading script) a second table from your data showing a preaggregated view to reduce memory usage and enhance response time.

Second try to do all possible transformations at loading time. In your formula you use month(date_field_1). Instead of this add

     month(date_field_1) AS month_date_field_1,

to your load statement. If I have datasets with Date fields, I always add year, month, calendar week, day and weekday as I need these regularly.

Regards,

Rolf

[#7]

Hi Rolf,

Sounds good, they're the most fun to work with

Would you mind going into a little more detail regarding the second table? Not sure I fully understand your suggestion. I have since added such fields to my load of this field (month, year, etc) - I tend to do this anyway but missed that field initially.  Having to wait half an hour for the extract from our db to qvd I decided to make do with the data I had for the time being haha.

Thanks again,

Lee