At 09:42 PM 22/01/2004 +0000, you wrote:

>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.
>
>I have typed the query and
>
>This query computes a running total for all records.
>
>If I run the index tuning wizard in MS SQL server, it puts a clusted
>index on Company_id. Then the query returns all rows in 4 seconds.
>
>I have an index on the same field in Firebird.

This will be useful if Company_id has a large number of values by
comparison with the number of rows in the table AND the values have a
fairly even distribution. If either of these isn't the case, then that
index is what is killing your performance. Try dropping the index and, if
it's still too slow, then re-add the Company_id index but make a composite
of (Company_id, id) to raise its selectivity (which is effectively what
index clustering does in MSSQL).

You'll have the same problem with Ledger_Type, if it is indexed, but see
note below.

>I have ran the query from within my Delphi app, and in ibconsole,
>ibexpert, and even from Easysoft ODBC. Same results.
>
>What am I missing?????
>
>Thanks,
>
>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

Do you realise that Fb/IB don't "invert" indexes? You may benefit from
having both an ASC and a DESC index on those two date columns.

Also make CERTAIN that you do not have any user-defined indexes that
duplicate any primary, unique or foreign key constraint indexes.

Another point: you should qualify ALL column references when you have a
correlated subquery or a re-entrant query. In this query you have both.

select A.*,
( SELECT SUM(b.DEPOSIT_AMT - b.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 /* see note */
WHERE
A.COMPANY_ID = 54
AND A.LEDGER_TYPE = 2
ORDER BY a.EFFECTIVE_DATE, a.ENTERED_DATE, a.ID

Note: if you have a FK on Ledger_type, or any index on it, then you should
disable the usage of the index. Drop it if you can, otherwise modify the
WHERE clause to prevent the optimizer from choosing it:

WHERE
A.COMPANY_ID = 54
AND A.LEDGER_TYPE = 2
and a.Ledger_type + 1 = a.Ledger_type + 1

/heLen