none
Sorting records in an Access dabase RRS feed

  • Question

  • 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
    Thursday, April 2, 2009 12:04 AM

Answers

  • With that many rows you should be implementing some sort of paging mechanism. If you use the TOP keyword you can select a certain number of rows at a time.

    SELECT TOP 1000 COL1, COL2, COL3 FROM TableName ORDER BY COL1

    There is nothing you can really do about how Access stores the data internally but if your table doesn't have a primary key or index by which you can sort then I would add one.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, April 2, 2009 4:16 PM