topic Re: max(date) from a table with a billion rows in App Development

max(date) from a table with a billion rows

anushree1 — Wed, 06 Sep 2023 12:22:41 GMT

Hi,

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

Re: max(date) from a table with a billion rows

Vegar — Wed, 06 Sep 2023 12:28:22 GMT

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.

Re: max(date) from a table with a billion rows

Or — Wed, 06 Sep 2023 12:50:09 GMT

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.