Hi,

> If I have a table in firebird, and a table in microsoft SQL server
> 2000. Identical table setup( I actually used DTS to import the table
> into SQL server).
>
> In MS SQL server I can run a query and return all rows in 6 seconds.
>
> Using firebird(I have tried 1.03 and 1.5, as well as interbase 7.1)
> it takes 23 seconds.

SELECT
A.*,
(SELECT
SUM(DEPOSIT_AMT - CHK_AMT)
FROM
LEDGER B
WHERE
((b.effective_date < a.effective_date) or
((b.effective_date = a.effective_date) and (b.entered_date <
a.entered_date)) or
((b.effective_date = a.effective_date) and (b.entered_date =
a.entered_date) and
(b.id <= a.id))) and
B.COMPANY_ID = 54 and B.LEDGER_TYPE = 2) Balance
FROM
LEDGER A
WHERE
A.COMPANY_ID = 54 and
A.LEDGER_TYPE = 2
ORDER BY
EFFECTIVE_DATE,
ENTERED_DATE,
ID

Current PLAN :
PLAN (B INDEX (LEDGER_IDX1))
PLAN SORT ((A INDEX (LEDGER_IDX1)))


What is the result if you create a compound index on
(COMPANY_ID, LEDGER_TYPE)

And do you really need all fields from "LEDGER A" in the result? I suggest
you to add only those you
need.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81