Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table in my DB with approximately 220 millions of records and rising every day.
In my data script, I decided to store the records in on QVD for each month but reading the data from the QVDs is taking more time than I had expected (around 15 min) and it's consuming a lot of resources in my server.
Would it be better if I had one QVD per year? Is it faster for Qlik to read a single QVD than several of them?
Thanks,
Juan
Well, I'm supposed to make all records available, so that's why I read the qvd of all months. The problem I'm seeing is that when I load the 220 million records in the table, the App stops working (I can't open it, I see the animated circles indefinitely).
try to debug script with a few records to see that data model is ok (no synth keys or loops)
When you have typically more than a million records it is always important to look at how you can help Qlik to get the most out of it's built-in compression mechanisms.
"link_timestamp",
"link_date",
"link_time",
"link_time_15min",
"link_minute",
"link_hour",
[link.id],
speed,
"speed_mph",
TravelTimeIndex,
traveltime
Splitting a high-cardinality field like a timestamp into it's constituent parts like date, hour, minute, second and tenths of seconds will help tremendously - but don't keep the original timestamp. This will enable much much higher compression under the hood for Qlik. You might save a lot of memory.
Thanks Petter. I've been editing the script following your advice and I've seen some improvement. I'm keeping the timestamp and I think the cardinality is not that high since in my DB table all values are in 15min samples (9:00, 9:15, 9:30, 9:45, 10:00, 10:15...). But I've got rid of all the other "link" fields and of speed. Speed_mph I've rounded it as you suggested.
Still, it takes a long time to load, but I'm now thinking of the resources of the machine where I have Qlik Sense installed. I'm using two cores and the CPU usage is of 100% during the load. I'm going to increase to 4 and see what happens.
Same with the memory, but this time not when loading but while editing or using the app. The 8 GB I have are used most of the time so I'm going to increase the memory too.
Thanks for the help, and I'll let you all know how it goes.
Regards,
Juan
This is working very well now. Thanks petter-s for the recommendations to improve the load script. And all participants for the good ideas provided.
Thanks for this solution, I have never heard anyone speak of evaluating the cardinality of fields (or maybe I was just asleep in class that day). I implemented this approach on a QVD with over 100 million rows that was taking just over an hour to load, even though it stated it was an optimized QVD load. Now loading in under 5 minutes.