Solved: SQL data - Qlik Community

MK_QSL · ‎2013-10-26

I have connected SQL Server in QlikView script to retrieve Sales Data from 2010 to 2013 (till date)

The Date Field is named as DECDDT and is in 03/01/2011 00:00:00 format.
Total number of Sales Rows are around >100 million.

Whenever I am trying to load the data, it gives me Out Of Memory Error.

Let me know how to get these data to make QVD for the same.

swuehl · ‎2013-10-26

No problem.

At some point, you or your organization needs a computer that is capable of holding the complete data needed (and even more, if you want to create charts on top to analyze, support multiple applications / users).

Having said this, and if you absolutely need to use this old computer for any reason, you could start developing on this computer, on a subset of the data (removing fields or records, e.g. loading only the last 2 Months or only a certain sales region by using a WHERE clause in your SQL). As Gysbert said, this should also give you an idea of the total RAM needed.

View solution in original post

swuehl · ‎2013-10-26

What version of QV and OS are you using? What is your hardware setting (amount of RAM)?

When do you get this error (after how many rows loaded / RAM allocated in memory)?

MK_QSL · ‎2013-10-26

QV = v11

OS = Windows XP PRO SP3

i3 @3.3GHz

4GB RAM

I usually get this error Between 10Mn to 15Mn Data...

ALLOCATED MEMORY exceeded or OUT OF MEMORY...

Gysbert_Wassenaar · ‎2013-10-26

Ah, XP SP3 that means 32-bit, so the qlikview process can only use 2GB. That sounds rather low for 100 million rows. Try using a machine with a 64-bit OS and a lot more ram. At least 8 GB, but probably even more is needed.

talk is cheap, supply exceeds demand

swuehl · ‎2013-10-26

If possible, try using a 64bit OS and QV version with a larger amount of RAM.

Are you only trying to load the DECDDT field? You can reduce the amount of distinct value by separating the date and time part in two different fields, this should lower the total amount of RAM needed, if the timestamps are pretty distinct.

MK_QSL · ‎2013-10-26

Thanks to both of you.

How much RAM should be needed in 64bit OS to handle 100Mn data?

Gysbert_Wassenaar · ‎2013-10-26

Impossible to say. A 100 million record set with one column that has boolean values can fit in very little ram. A 100 million record set with 250 columns with clobs can use up terabytes.

Load 5 million records and store that in a qvd. Check the size and multiply that by 20. That would be the absolute minimum just to load the data. You'll need more since you'll need ram for the os, qv itself, data processing and intermediate results too.

talk is cheap, supply exceeds demand

MK_QSL · ‎2013-10-26

OK... Thanks for your reply.

We have 100Mn records with 25 column. So you mean to say, I can't load data in single QV application.

What is the solution to overcome this?

swuehl · ‎2013-10-26

No, that's not what Gysbert said.

100 million+ sales data seems like a professional business task. Get professional business IT equipment to handle that and skip your old WinXP 32bit equipment.

MK_QSL · ‎2013-10-26

What is meant by Professional Business IT equipment?

SQL data

Related Topics