Sorting records in an Access table for real

[borophyll]

Hi,

I am wanting to iterate over the set of records in an Access database
table programmatically using an OleDbDataReader. This works fine
using a plain SELECT statement. However, I want them to be read in
sorted on a particular field. So, I added an ORDER BY clause. This
made the program hang and caused the system to perform poorly (it
appears to have used all the memory). I should mention that the table
contains about 5 million records, so it is likely it is struggling to
sort these records well. As it is unacceptable for the program to
perform like this, is there some way to 'presort' the records in the
database, so that the Access table is internally already in the
correct order? I tried dierctly sorting the table using Access, but
apparently this doesn't really sort the table, it will just apply a
sort query everytime the table is opened. Any help on what I could do
would be appreciated.

Regards,
Michael

 

[Tom van Stiphout]

On Wed, 1 Apr 2009 21:19:40 -0700 (PDT), (e-mail address removed) wrote:

Put an index on the column you are sorting by. 5m records is not too
much on decent hardware.

-Tom.
Microsoft Access MVP

 

[david]

Access/Jet tables are physically sorted to data entry order
at data entry, then resorted to Primary Key order when or
if the database is compacted.

Because it's not documented, you can't be absolutely sure
that an append query to a new database would append
data in a correct order, but it sounds like you could use
a sort query on a table that was probably correct.

Compacting (and re-sorting) a large database is long and
slow, particularly if you don't have enough memory for the
entire database, but at it compacts to a new database, so
at least you don't need to have room in memory for two
copies, which you do if you are trying to sort with a select
query.

(david)

 

[BruceM]

To elaborate just a little on one of the responses, index the field, then
try the query again.