Wow. All i can really say is wow. Adding the PK to the index dramatically
increased its selectivity. Instead of hours to export 2 million records, I
think it will take 5 to 6 minutes at most ever. I exported 100K in less
then 15 seconds.

It will take a lot longer to restore and a tad more to insert records into
the table, but well worth the increase in performance.

THANK YOU Aage, and thanks to the rest of you for all your comments and
suggestions.

-----Original Message-----
From: Aage Johansen [mailto:aagjohan@...]
Sent: Thursday, February 03, 2005 1:27 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Slow performance with Index



Edwin A. Epstein, III 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?

My guess:
You update a lot of records setting/removing a flag in DNC_QUED. When you
access the records this later on (with the SELECT) there will be some
garbage collecting of old record versions, and cleaning up in the index.
The "cleaning up" will take some time, and with very-low-selectivity
indexes it will be worse.
You might try to append something (e.g. the PK) to the index definition to
make it unique (or 'almost unique'). Hopefully, it should speed up the
cleaning part.


--
Aage J.




Yahoo! Groups Links