At 06:13 PM 23/01/2004 +0000, you wrote:

>--- In firebird-support@yahoogroups.com, richard poulin
><rpoulin@s...> wrote:
> > I was following this debate .....
> > Is Firebird/Interbase that slow ? How does it really compare to
>MySql
> > or Ms Sql Server ?
>
>
>I have 5 other applications that use Interbase, and they all run
>great with respect to speed. It's this particular query I can't get
>any speed out of.
>
>
>I definitely don't want to detract from Interbase, I just want this
>query to run!!

If it were my problem, I would
(1) want to get rid of all doubles used as keys in your system. (I'm
really curious to know how these values are set in the system, and how you
have dealt in the past with the unknown inaccuracies in data from false
positives and negatives in =, <>, > and < searches).
(2) perform this particular operation with a stored procedure. Using that
subquery to calculate a running total is causing far too many expensive hits.

If you are getting it fast via MSSQL as you say you are, then the clustered
indexes are likely to be helping. The right indexing in Fb might help too,
but solutions like indexing every column won't get you to that place. More
indexes <> better.

create procedure ledger_running_total (
company_id double precision,
ledger_type smallint)
returns (
id double precision,
col1 sometype,
col2 sometype,
...,
balance numeric(9,2)

as
declare variable amount numeric(9,2);
declare variable v_effdate timestamp;
declare variable v_entdate timestamp;

begin
balance = 0;
for select
a.id,
a.col1,
a.col2,
a. ..., /* assume 3 columns for example */
a.effective_date,
a.entered_date,
SUM(a.DEPOSIT_AMT - a.CHK_AMT)
from ledger a
where
a.company_id = :company_id
and a.ledger_type = :ledger_type
order by
a.effective_date,
a.entered_date,
a.id
into
:id, :col1, :col2, <3 other variables>, :v_effdate, :v_entdate,
:amount)
do
begin
if (exists(
select b.id from ledger b
where b.id = :id
and b.effective_date >= :v_effdate
and b.entered_date > :v_entdate
/* dunno what these tests are meant to test - roll your own */
))
then
begin
balance = balance + amount;
suspend;
end
end
end

/heLen