Load time for 5-6 million records?

[kmswetha]

Hi,

I am having 5-6 million records, and we would be hitting Oracle database to fetch these 5 million.

How long would it take to load to qlikview for this much of data?

Its a pretty simple statement without any where clause

EX:

Load

a,

b,

c;

SQL select

a,b,c from table;

The reason for posting this is, its taking 20 mins to load it into qlikview, but ideally I believe it should load in less time than this.

Is there any why to increase a performance.Am using OLE DB connections.

[its_anandrjs]

Hi,

Better way if you load this table as Incremental load.

1. First load is full load from the Source file and create the QVDs of this file.

2. After first step do incremental load means load only one day back records and concatenate the records to the QVD.

Regards

Anand

[maxgro · Accepted solution]

It seems very slow, I have seen 100-120 million of record extracted x hour from some Oracledb, 10-12 gb of a single qvd.

But it depends on a lot of things (database, driver, network, ....).

Usually I get good result increasing the parameter FetchSize in oledb conn string until the extraction time of the same query decrease.

OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxxxxx;Data Source=xxxxxxxxxxxx;Extended Properties="FetchSize=100000"] (XPassword is xxxxxxxxxxxxxx);

[kmswetha]

Hi Grossi,

Tried using this option, but its not returing anything.

It shows database is connected but doesn't go further.

Is there anything am missing?

Also I cannot use QVD since this is just one day load.

[Anonymous]

Hi Swetha,

Are you using any transformations while loading data?

[maxgro]

start with a lower fetchsize, this is from oracle doc

• FetchSize - specifies the number of rows the provider will fetch at a time (fetch array). It must be set appropriately depending on the data size and the response time of the network. If the value is set too high, this could result in more wait time during the execution of the query. If the value is set too low, this could result in many more round trips to the database. Valid values are 1 to 429,496,296. The default is 100.

[CITIPK57092]

@maxgro 

is it same for ODBC connection as well?
we are using ODBC connection and we want to increase our fetch size /batch size.  is there any setting for the same ? or can we create connection string for the same (ODBC connection string)

Can you please help

Thanks in advance