Well I do flag the records using nested loops and it is much much faster
that way. However, when I am done I have on average anywhere from 100k to
2million records flagged for processing. This processing is done outside of
the database by a 3rd part data services company. So I have to export all
the flagged records at once.


-----Original Message-----
From: Alan McDonald [mailto:alan@...]
Sent: Wednesday, February 02, 2005 7:16 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Slow performance with Index

> 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

I have no idea how these 14Mill records are organised but here's a tip to
increase performance by 100s on what you are doing.
Let's say I have 14Million rooms across 5 million buildings across 1 million
complexes across 200,000 cities across 1000 states and the data is
structured that way.
If I select roomid from rooms that's 14 mill records to be selected. that's
no good exspecially in the client server environment (maybe in a desktop
database environment it would work).
But if I
FOR SELECT ID FROM STATES INTO :STATEID DO BEGIN
FOR SELECT ID FROM CITIES WHERE STATE=:STATEID INTO :CITYID DO BEGIN
FOR SELECT ID FROM COMPLEXES WHERE CITY=:CITYID INTO :COMPLEXID DO BEGIN
ETC ETC
do processing
END
END
END
Make this nested loop a stored procedure and pass it an argument which
relates to the task at hand.
Call the SP with the argument.
You will be amazed at how fast processing of 14 mill records will be. You
will actually wonder the first time it processes whether or hot it actually
worked.

Alan





Yahoo! Groups Links