Maybe try

select count(*) from person where BirthDate <= :EndDate
and coalesce (DeathDate, :EndDate +1 ) > :EndDate

with an index on BirthDate

----- Original Message -----
From: "unordained" <unordained_00@...>
To: <firebird-support@yahoogroups.com>
Sent: Saturday, June 21, 2008 9:34 PM
Subject: Re: [firebird-support] Optimal algorithm for inventory


>> 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.
>
> Have you tried creating a compound index, and testing how much faster than
> runs? Are you always
> querying by both start and end as in your example above?
>
> create index ix_lifetime on person (deathdate, birthdate);
>
> Also, that 'or' could be causing some problems for optimizing the index
> usage, you might try
> breaking it up into two searches (if you weren't doing a straight 'count',
> you could use this same
> technique with a union instead):
>
> select
> /* this one could benefit from a compound index on (BirthDate, DeathDate)
> or (DeathDate,
> BirthDate) */
> (select count(*) from person where BirthDate <= :EndDate and DeathDate >
> :EndDate)
> +
> /* this one could benefit from a compound index on (DeathDate, BirthDate)
> */
> (select count(*) from person where BirthDate <= :EndDate and DeathDate is
> null)
> from rdb$database;
>
> (Do you want to support the case where BirthDate is unknown, and treated
> as "since forever"?)
>
> I may be a bit fuzzy on that compound indexing ... wait for confirmation
> from someone else on the
> list or just try it out, I guess.
>
> -Philip
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>