Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

MK_QSL
Not applicable

SQL data

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

  1. The Date Field is named as DECDDT and is in 03/01/2011 00:00:00 format.
  2. 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.

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: SQL data

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.

13 Replies
swuehl
Not applicable

Re: SQL data

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
Not applicable

Re: SQL data

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
Not applicable

Re: SQL data

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
Not applicable

Re: SQL data

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
Not applicable

Re: SQL data

Thanks to both of you.

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

Gysbert_Wassenaar
Not applicable

Re: SQL data

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
Not applicable

Re: SQL data

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
Not applicable

Re: SQL data

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
Not applicable

Re: SQL data

What is meant by Professional Business IT equipment?