Subject Optimal algorithm for inventory
Author kokok_kokok
I am looking for the fastest way to get an inventory at any date.
For example: people living in London at 23 May 1930

My table is simple, it is a person table with millions of records. The

-(Other fields like country, city, ...)

The usual question is to calculate the inventory in any date. I use a
basic sql statement:

select count(*) from person where BirthDate<= :EndDate and (DeathDate>
:EndDate or DeathDate is null)

This SQL statement works well but it is very slow because it must read
all records, or at least, from the BirthDate to the current date if
the table is indexed by BirthDate.

It can take a lot of time, from 30 seconds to some minutes depending
on computer.

Another idea is to create an auxiliary table and to maintain the
inventory for each day, but it is not ok because we need to filter the
result for example by city (i.e All people living in London at 23 May
1930). It would imply to have many breakdowns in this temporary table.

My question is if somebody has had a similar scenario and how it has
been solved.

Thank you