Create an ascending index on BIRTHDATE and a descending index on
DEATHDATE (or is it descending index on BIRTHDATE and ascending index on
DEATHDATE?).

SELECT
COUNT(*)
FROM
PERSON
WHERE
(BIRTHDATE <= :EndDate AND DEATHDATE > :EndDate) OR
(BIRTHDATE <= :EndDate AND DEATHDATE IS NULL)

You may want to add intelligence to not use an index if EndDate is close
to the beginning or end of the data set. If EndDate is close to the end
of time, change BIRTHDATE to BIRTHDATE+0 and if it's near the beginning
change 'DEATHDATE>:EndDate' to 'DEATHDATE+0>:EndDate'. In those cases
the index in question will not be useful as most rows will match the
filter. You will have to profile your system to determine a definition
for "near."

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of kokok_kokok
Sent: Saturday, June 21, 2008 7:28 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Optimal algorithm for inventory


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
columns:

-BirthDate
-DeathDate
-(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

Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.