--- In firebird-support@yahoogroups.com, "bortoswatkins" <rob@b...>
wrote:

> --- In firebird-support@yahoogroups.com, "bortoswatkins" <rob@b...>
> wrote:
> > --- In firebird-support@yahoogroups.com, "Alan McDonald"
> <alan@m...>
> > 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.
> > > >
> > > > 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,
> > >
> > > Oh - and show us the PLAN which results from the query when you
> > fire it in
> > > IBExpert.
> > > Alan
> > Here is the plan:
> >
> > PLAN (B INDEX (LEDGER_IDX1))
> > PLAN SORT ((A INDEX (LEDGER_IDX1)))
> >
> >
> > the index (LEDGER_IDX1 ) is on COMPANY_ID only...
>
>
>
> I have tried everything that was suggested in previous posts to no
> avail.
>
> And to top it off, I imported the table into MS Access, and the

query

> runs in half the time.
>
> I really must be missing something.

more info:

Database "C:\cashledger\CASHLEDGER.GDB"

Database header page information:
Flags 0
Checksum 12345
Generation 105
Page size 4096
ODS version 11.1
Oldest transaction 653
Oldest active 654
Oldest snapshot 654
Next transaction 655
Bumped transaction 1
Sequence number 0
Next attachment ID 0
Implementation ID 16
Shadow count 0
Page buffers 20799
Next header page 0
Database dialect 3
Creation date Jan 18, 2004 19:37:37
Attributes

Variable header data:
Sweep interval: 0
*END*


Database file sequence:
File C:\cashledger\CASHLEDGER.GDB is the only file

Database log page information:
Creation date
Log flags: 2
No write ahead log

Next log page: 0

Variable log data:
Control Point 1:
File name:
Partition offset: 0 Seqno: 0 Offset: 0
Control Point 2:
File name:
Partition offset: 0 Seqno: 0 Offset: 0
Current File:
File name:
Partition offset: 0 Seqno: 0 Offset: 0
*END*

Analyzing database pages ...

CATEGORY (130)
Primary pointer page: 169, Index root page: 170
Data pages: 1, data page slots: 1, average fill: 52%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 0

Index RDB$PRIMARY1 (0)
Depth: 1, leaf buckets: 1, nodes: 50
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

COMPANY (131)
Primary pointer page: 172, Index root page: 173
Data pages: 7, data page slots: 7, average fill: 78%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 6

Index ICOMPANY_BANK (1)
Depth: 1, leaf buckets: 1, nodes: 137
Average data length: 5.00, total dup: 75, max dup: 39
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

Index ICOMPANY_NAME (2)
Depth: 1, leaf buckets: 1, nodes: 137
Average data length: 20.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 1

Index RDB$PRIMARY4 (0)
Depth: 1, leaf buckets: 1, nodes: 137
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

IBE$REPORTS (133)
Primary pointer page: 176, Index root page: 177
Data pages: 0, data page slots: 0, average fill: 0%
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

Index RDB$PRIMARY10 (0)
Depth: 1, leaf buckets: 1, nodes: 0
Average data length: 0.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

LEDGER (129)
Primary pointer page: 167, Index root page: 168
Data pages: 1584, data page slots: 1584, average fill: 77%
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 1576
80 - 99% = 7

Index LEDGER_IDX1 (0)
Depth: 2, leaf buckets: 89, nodes: 59531
Average data length: 0.00, total dup: 59139, max dup: 1880
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 1
80 - 99% = 88

Index RDB$PRIMARY5 (1)
Depth: 2, leaf buckets: 104, nodes: 59531
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 103

LEDGER_TYPE (132)
Primary pointer page: 174, Index root page: 175
Data pages: 1, data page slots: 1, average fill: 5%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

Index RDB$PRIMARY6 (0)
Depth: 1, leaf buckets: 1, nodes: 6
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

USERS (128)
Primary pointer page: 164, Index root page: 165
Data pages: 1, data page slots: 1, average fill: 12%
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

Index RDB$PRIMARY9 (1)
Depth: 1, leaf buckets: 1, nodes: 14
Average data length: 1.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0

Index USERNAME (0)
Depth: 1, leaf buckets: 1, nodes: 14
Average data length: 5.00, total dup: 0, max dup: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0