From recent experience, retrieval of 7 million rows in 30 minutes is
about the max that you can expect from any database on a single query
across a 100 MBit network connection.

With that statement, you must still bear in mind that you will need to
do careful measurements to positively identify the bottleneck.

This may be a situation where a table space scan is actually faster than
an indexed scan. You will need to compute I/O's for index plus table
versus table only scans for your application. Since Firebird carries
transactional integrity to the extreme, there is no index-only scan like
other DBMS's allow. Each I/O against an index that identifies a
candidate column must also hit the table.

For optimum performance, follow Alan's advice about lots of RAM and fast
hardware. I would be tempted to go even further and try to move the
table into a database in a memory mapped file, then run your process
against that.

Also, run comparisons between a straight table space scan and the index
scan on that table. Your I/O count may actually be reduced since you
are hitting essentially every row in the database. If your records are
small enough, your page size large enough, and your clustering random
enough relative to the ordering of the index, this could be significant.

On Wed, 2005-02-02 at 19:25, Adam wrote:
>
>
>
> Hi Ed,
>
> Are you executing the query from the database server or over a
> network connection? From what I can gather, your recordset to be
> returned will have somewhere around 7 million records (assuming a 50-
> 50 split). The selectivity of that index will be very large. What
> sort of performance are you expecting from this query, and what sort
> of hardware are you running your database over? Obviously high speed
> SCSI hard drives and a lot of RAM on a server that does nothing else
> will help out.
>
> My guess is your Prepare and Execution times will be quite reasonable
> (ms) and you are waiting on Fetch time which is really hardware and
> configuration dependent. I just ran a query to select 15000 records
> (1 integer field) which took 1 second, which if linearly projected to
> 7 million records will take around 8 minutes (and that is locally).
>
> Adam
>
>
> --- In firebird-support@yahoogroups.com, "Edwin A. Epstein, III"
> <eepstein@c...> wrote:
> > I have a table with 14 million records in it. As part of certain
> operations
> > I need to do against the records I flag them individually as part
> of a que.
> > I am using a field called DNC_QUED which is a VarChar(1) and ASCII
> character
> > set. I have non-unique index on just the field alone. There are
> no null
> > values and the only values are '1' or '0'.
> >
> > When I pull a SELECT FIELD1 FROM TABLE1 WHERE DNC_QUED = '1' and
> attempt to
> > fetch all records it takes a very long time (30 minutes plus). I
> checked
> > the plan and it is using the index.
> >
> > Is there anything I can do to increase the performance of that
> select
> > statement?
> >
> > Thanks, Ed
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>