topic Out of Physical and/or logical memory in QlikView

Out of Physical and/or logical memory

sunil2288 — Wed, 14 Apr 2010 10:21:39 GMT

HI all

I am using qlikview v9,I am incurring an error while handling 2.3millions of data..The error is showing as Out of Physical and/or logical memory.How can i resolve it. The table having four fields.

Comments awaited..

Thanks and Regards,

Sunil

Out of Physical and/or logical memory

pover — Wed, 14 Apr 2010 14:46:31 GMT

Hi Sunil,

Is your machine 64 bits or 32 bits? How much memory does your machine have? What data types are you trying to import?

What's your data model look like when you run the script through the debug? Does it have any synthetic tables or are you doing any join between 2 tables that don't share a common field? Any complex group by or interval match statements?

I remember one case in which I successfully loaded about 1.5 million total lines from 5 tables with several columns on a 32 bit machine with 2 Gb of memory RAM. The data model was simple.

Regards.

Out of Physical and/or logical memory

sunil2288 — Thu, 15 Apr 2010 07:41:53 GMT

HI karl

My machine is 32 bit and my table is a simple model having 5 fields,One is a date field,and 4 are numerical values.i am having 4gb ram and 2.52 ghz of processor..

Regards

SV:Re: Out of Physical and/or logical memory

Thu, 15 Apr 2010 07:47:10 GMT

Hello

How much do you allocate for virtual memory. I've changed both my initial and maximum to about 1.5 times my physical memory and I barely ever get any memory problems. I'm on a 32-bit machine with 4 gb (only 3gb active with XP) in memory.

Regards, Max

SV:Re: Out of Physical and/or logical memory

sunil2288 — Thu, 15 Apr 2010 08:26:13 GMT

Hi max

My virtual memory is having min value 2046 and max value 10000 for c:\ drive. So how to resolve it.

Thanks

SV:Re: Out of Physical and/or logical memory

pover — Thu, 15 Apr 2010 13:36:33 GMT

The moment QlikView starts using virutal memory, you're better off stopping the reload or graph generation because it is going to take too long. Max is right that XP allows for 3 GB to be used, but any one application can use a maximum of 2 GB. Open the task manager and see how much memory QV is using at the time it sends you the error. It should be around 2 GB.

Does the error show up when you do a reload or when you are generating a graph?

Out of Physical and/or logical memory

Thu, 15 Apr 2010 14:54:44 GMT

If you reach a maximum of your memory - you are doing something wrong. Review your data structure and expressions.

If you don't reach a maximum of your memory, but still receive 'Out of memory' error, maybe you have a pretty heavy calculation in formulas. In this case you can play with 'Calculated Formulas Max Memory':

1. go to Settings->User Preferences->Object

2. uncheck "Ignore Calculation Limits"

3. set appropriate amount of memory for "Calculated Formulas Max Memory", by default it's 1 MB.

Out of Physical and/or logical memory

wadesims — Thu, 15 Apr 2010 15:40:28 GMT

You might find it useful to to look at your table and determine the number of unique values in each of the fields. Perhaps the easiest way to identify the number of distinct records in a table is to go to [Settings] [Document Properties] [Tables] and look at (or export) the structure of the tables. The important columns to look at are the # Values and # Distinct. Because the amount of memory required to store data is proportional to the number of distinct values, and the field with the largest number of distinct values is where you want to look first.

You indicated that you have a date field. If you are storing the values as a datetime stamp and only want the date, you might consider truncating the time portion from that field. If you need both the date and the time, you could split them into separate columns. For example, if you have 2.5 million rows where each has a distinct datetime stamp it can eat up a lot of memory. In our case, we only wanted the date and the hour associated with a transaction so we converted the datetime as follows (SQL Server syntax):

CAST(CAST(TranDTime AS BIGINT) AS SMALLDATETIME) AS TranDate,
DATEPART(hour, TranDTime) AS TranHour,

By splitting the datetime into two fields we were able to reduce the memory required to store that field.

I hope that this helps!

- Wade

SV:Re: Out of Physical and/or logical memory

sunil2288 — Fri, 16 Apr 2010 05:16:39 GMT

yes at the time reload also its generating the error..

SV:Re: Out of Physical and/or logical memory

pover — Fri, 16 Apr 2010 05:42:22 GMT

Hi Sunil,

Load just one column and see if you get the out of memory error. If not then load two columns, etc. until you get the error. That might tell us if one column in particular is giving you problems.

Anyway you can post your script?

Regards.

SV:Re: Out of Physical and/or logical memory

sunil2288 — Fri, 16 Apr 2010 10:19:11 GMT

my script is like this

LOAD

SQL

SELECT * FROM FullTable; date, tester, sl, model;

SV:Re: Out of Physical and/or logical memory

sunil2288 — Fri, 16 Apr 2010 10:30:18 GMT

LOAD

SQL

SELECT *FROM

FullTable;

date,tester,sl,model;

SV:Re: Out of Physical and/or logical memory

sunil2288 — Fri, 16 Apr 2010 11:17:34 GMT

HI all

I loaded allth data nut at the time of viewing any data from the table through any chat object it'showing the error as

OUT OF VIRTUAL AND/OR LOGICAL MEMORY, ALLOCATING 128MB

SV:Re: Out of Physical and/or logical memory

pover — Sat, 17 Apr 2010 02:26:04 GMT

Hi Sunil,

Can you try uploading your script again? Nothing appears. There is a link when you post a message that tells you how to post script.

Regards.

SV:Re: Out of Physical and/or logical memory

sunil2288 — Sat, 17 Apr 2010 05:15:04 GMT

Hi Karl,

I am again trying to upload the script

SV:Re: Out of Physical and/or logical memory

sunil2288 — Sat, 17 Apr 2010 05:22:42 GMT

LOAD date, tester, sl, model; SQL SELECT * FROM FullTable;

SV:Re: Out of Physical and/or logical memory

sunil2288 — Sat, 17 Apr 2010 06:20:37 GMT

Hi Karl

Though the table has 4 fields but it has more than 2 millions of data..

And the script is lky dis

LOAD date, tester, sl, model; SQL SELECT * FROM FullTable;

Out of Physical and/or logical memory

pover — Sat, 17 Apr 2010 18:02:46 GMT

Wow. That is as simple as it gets. I wouldn't usually think 2 million rows and 4 columns would take up more than 2 GB to load. I also have a 32-bit laptop with 4GB with Windows 7 and I just tested loading 2.6 million rows and 39 columns from a qvd I have of General Ledger entries. My processor peaks, but the memory stays pretty stable. I saw the QV.exe process peak at about 600 MB. I done no special configuration to my QlikView o laptop.

With this in mind, I would try the same load on a different computer. I once used QlikView with Vista and had no problems, but there were many people who complained about Vista's memory usage. Wade made a valid comment that if all the data is unique then it will use up more memory, but I assume the values in the date and tester column are not all unique.

You should try running your script first like this:

LOAD date, tester, sl, model; SQL SELECT date, tester, sl, model FROM FullTable;

then like this:

LOAD date; SQL SELECT dateFROM FullTable;

then like this:

LOAD date, tester; SQL SELECT date, tester FROM FullTable;

and so on until the error appears. Report back on the results, please.

You aren't running any other high memory usage programs in parallel to QlikView, right? You could try installing the 64 bit version Windows over your 32 bit computer and I've seen that that allows you to take advantage of the 1GB RAM that the 32 bit version Windows doesn't use, but I would leave that as a last option.

Let's see if anybody else has an idea...

Good Luck

Out of Physical and/or logical memory

Oleg_Troyansky — Sat, 17 Apr 2010 20:31:00 GMT

I'd recommend loading a partial amount of data (for example, 10,000 rows), generate Memory Statistics and run them through QlikView Optimizer. You might find some interesting surprises. For example:

1. Date. Some databases are passing full timestamps instead of dates. Timestamps take up much more space, and they are all unique (distinct), because each time-stamp is storing time up to milliseconds.

2. I'm assuming "tester" is the name of a person - most likely nothing interesting there...

3. Don't know what "sl" and "model" mean - any chance those might be blob fields with huge binary arrays of information?

QlikView Optimizer will show you how much space each field takes up.

Out of Physical and/or logical memory

rwunderlich — Sun, 18 Apr 2010 02:22:26 GMT

Are there more than 4 columns in the SQL FullTable? As Karl suggested, I would try specifying the column names, instead of *, in the SELECT.

LOAD date, tester, sl, model;
SQL SELECT date, tester, sl, model FROM FullTable;

You may be bringing back many columns from the DB and the extra columns would occupy memory even though Qlikview will eventually drop them.

-Rob