How to optimize load time on searching one value in a 3-million-row table?

[MEBG93]

Hello experts,

I need to reduce load time in a table in which I only take the max date of a 3-million-row table. I know that using the index may help but I've never used it before.

Also, the table is consulted using SQL.

Any suggestions?

Many thanks!!

[dplr-rn]

please elaborate.

share snippet of the qlikview code you are using

[MEBG93]

Hello @dplr-rn  I'm using:

MaxDate:
LOAD max(`date`) as maxdate;
SQL SELECT `date` 
FROM IMPALA.sales;

It works, but its taking too long to retrieve just one value.

Thanks

[dplr-rn]

why not just do max(date) in sql? That should make it  faster. because what you are currently doing is transferring 3 millions rows from db then across network and into qlik and then doing the max.

LOAD maxdate;
SQL SELECT max(`date`) as maxdate 
FROM IMPALA.sales;

[MEBG93]

It is faster, but is still very slow. 

Is there a way to insert an index or any sort?

[marcus_sommer]

Probably it depends on if you only need the max. date from this table or if it's just a part of the data which are pulled from this table respectively from this database. If you only need this single value the query within the db should be the fastest way because otherwise all 3 M records needs to be transferred to Qlik to make the evaluation there.

If in general all records are transferred respectively considered in any way maybe by using any kind of incremental approaches you could loop through the Qlik system-tables to get this value. This may look like:

sql select `date` from source;

load date(max(fieldvalue('date', recno())) as maxdate autogenerate fieldvaluecount(date);

- Marcus