topic Re: SQL data in QlikView

SQL data

MK_QSL — Sat, 26 Oct 2013 08:23:17 GMT

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.

Re: SQL data

swuehl — Sat, 26 Oct 2013 11:01:00 GMT

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)?

Re: SQL data

MK_QSL — Sat, 26 Oct 2013 11:16:57 GMT

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...

Re: SQL data

Gysbert_Wassenaar — Sat, 26 Oct 2013 11:32:11 GMT

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.

Re: SQL data

swuehl — Sat, 26 Oct 2013 11:32:45 GMT

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.

Re: SQL data

MK_QSL — Sat, 26 Oct 2013 11:42:37 GMT

Thanks to both of you.

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

Re: SQL data

Gysbert_Wassenaar — Sat, 26 Oct 2013 11:52:24 GMT

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.

Re: SQL data

MK_QSL — Sat, 26 Oct 2013 11:55:24 GMT

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?

Re: SQL data

swuehl — Sat, 26 Oct 2013 12:05:42 GMT

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.

Re: SQL data

MK_QSL — Sat, 26 Oct 2013 12:19:44 GMT

What is meant by Professional Business IT equipment?

Re: SQL data

swuehl — Sat, 26 Oct 2013 12:24:41 GMT

An equipment that is adequate for the needs of your business task. Your computer seems to be not adequate, as far as I see.

You don't really want to fight with hardware limitations that you can easily overcome (and a 64bit computer (today's standard) with an appropriate amount of CPU's and RAM is not really expensive nowadays).

Re: SQL data

MK_QSL — Sat, 26 Oct 2013 12:27:47 GMT

Thanks for your reply.

Re: SQL data

swuehl — Sat, 26 Oct 2013 12:39:07 GMT

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.

Re: SQL data

MK_QSL — Sat, 26 Oct 2013 12:45:39 GMT

Thanks to both of you for your useful information.

I will definitely try using last three months data to make my application working.