max(date) from a table with a billion rows

[anushree1]

Hi,

@tresesco , @Vegar , @sunny_talwar , 

I would need to load data from a history table by filtering for the recent date, this table has more than a billion rows.

In actual terms what i need to do is get the recent date of load on this table, there is col by name name load_date providing the date of load 

so i will need to get     max(load_date) as maxdate from table1.qvd;

let vmaxdate=peek('maxdate');

and then do the filtering like

load *

from table1.qvd

where load_date=vmaxdate;

But the issue here is , the script which is supposed to get max(load_date) is running for more than 1.5 hrs and timing out.

Please let me know if there is a better way to handle this situation so that my load time is minimized and i get the desired results

[Vegar]

I suggest you to take a look at “FASTEST” METHOD TO READ MAX(FIELD) FROM A QVD  by Row Wunderlich. It is 10 years old, but still good. 

[Or]

This is one of those cases where the correct answer is often "None of the above".  The best way to get a date in this scenario is to have whatever is writing the QVD output put the max date into a variable or a separate file, and then read that.