Solved: Reloading a table with a lot of datas - Qlik Community

Report Inappropriate Content · ‎2015-10-23

Hello every one I am trying to load a table with more than 20 Millions of rows via an ODBC connector.

The ODBC can't support this volumetric so I set filters for one day,like my script bellow:

F_CALL_INVOICE:

LOAD CALL_DATE,

date(CALL_DATE) as CALL_DATE_Day,

DESTINATION,

DURATION,

ACCOUNT_ID,

INVOICE_ID,

PRODUCT_ID,

TIMETOANSWER,

STATUS;

SQL SELECT

CALL_DATE,

DESTINATION,

DURATION,

STATUS,

ACCOUNT_ID,

PRODUCT_ID,

INVOICE_ID,

TIMETOANSWER

FROM REPORT."F_CALL_INVOICE"

where (TO_CHAR(CALL_DATE,'DD/MM/YYYY')) ='01/09/2015';

The load arrived to 380 000 rows and nothing is happening since 30'.

Could someone give me some advices to load these kind of table?

thank you

cwolf · ‎2015-10-23

You should never use an expression like:

... where convert2external(field) = 'something'

in this case the database can't use any index.

Always use

... where field = convert2internal('something')

In your case:

... where CALL_DATE = TO_DATE('01/09/2015' ,'DD/MM/YYYY')

View solution in original post

Gabriel · ‎2015-10-23

Hi,

Are you loading from Desktop or on server?

If you're loading from Desktop what's the RAM size of your computer.

Because 380,000 rows is nothing to QlikView

Report Inappropriate Content · ‎2015-10-23

Hello thank for your answer.

I am loading on a server,

Peter_Cammaert · ‎2015-10-23

What is your source database?

The ODBC specification contains no explicit/implicit limitation on the number of rows it will pass.

This may point to a driver problem. Did you try with an OLE DB-driver?

Peter

Report Inappropriate Content · ‎2015-10-23

Its an Oracle database

Gabriel · ‎2015-10-23

Hi,

To test that ALL is ok with the connection, may be load just FIRST 100 rows and see the result

jonathandienst · ‎2015-10-23

Does the query run correctly in another application (such as Toad)?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Colin-Albert · ‎2015-10-23

Could this be an index issue?

Your filter is using an expression not a direct comparison on the actual field.

Is the CALL_DATE field indexed and are the date parts indexed. In Oracle I believe you need to index date parts individually.

Report Inappropriate Content · ‎2015-10-23

Yes the query is running correctly when I set a filter on one INVOICE_ID.

It's clearly a volumetic issue

rubenmarin · ‎2015-10-23

Hi Benjamin, there is a join after load the table? ¿or another instruction? if not maybe the script has ended but the document has complex synthetic keys to process.