QlikView Administration

[pedromsouza]

Hi,

I have an application that uses data from a huge SQL database. When I open it takes more than 60 gbs of ram, so I started to look for optimization tips.

I created a mem file and noticed that most of the RAM is being used by database (class), field (type) and symbols (subtype). The field that uses more memory is hash128(field1,field2,field3). Field 1 to 3  is a complex SQL key... I can't get rid of it. It connects the 3 biggest tables in the script.

All three are integer fields.

Should I use another function other than hash? Should I "treat" these fields somehow in SQL before loading them into Qlikview?

What should I do to free RAM?

Regards,

Pedro.

[ajsjoshua]

Hi,

convert them to QVD and your application loads faster.

[pedromsouza]

Joshua,

I'll try that. But, what about the keys? As I said, most of the memory is being taken by this compost key.

Regards.

[Peter_Cammaert · Accepted solution]

Use autonumber instead of autohash. And if possible, you could eliminate the keys altogether by joining/concatenating two or more tables.

Best,

Peter

[pedromsouza]

Peter,

thanks! Your suggestion (autonumber) reduced ram usage by 60%... The QVW now is "loadable" and "reloadable".

Question: what happens if I concatenate tables that have a relation of "one to many"? Won't it multiply "one"s rows?

[oknotsen]

Please start a new topic for a new question.

Once a topic is answered (and flagged like it), it will not get the attention you want for new questions.

[Peter_Cammaert]

Onno is right of course. However, I hope he will disregard this very quick answer:

Concatenation is suitable for tables that look alike and that may have a one-to-many relationship but don't depend on it for analysis (like Proposals/Offers and Orders).

Joins can be used for tables that either have a strict 1-to-1 relationship (like SalesOrderLine-to-DeliveryLine-to-BillingLine, one of each) or that do not create a risk of data duplication (a header record with multiple line records where the header doesn't contain "summable" data like amounts or quantities). Concat/Join aren't always a good idea.

YMMV.

Peter