Performance issue with large Access database

Giganews Newsgroups
Subject: Performance issue with large Access database
Posted by:  Bernard Taylor
Date: Wed, 29 Apr 2009

The setup: ISAPI DLL written in D2007 connected to Access database via ADO (Jet provider) on W2003 server.

The requirement: open the first unregistered record (field = "N") from a table of 1 million, mark the record as registered (field = "Y") and return details to the user.

The issue: The database takes approximately 30 seconds to return a set of records using TADODataSet if the request has an ORDER BY clause on the primary key. We need the records to be taken in the correct sequence so this is important. If two simultaneous requests are received, the response time grows to >2 minutes.

As we only require one record per request, is there a way to get ADO to return this record without creating a full set of eligible records first?

I haven't written database software for a long time and I think I used to know this kind of thing, but that was with the BDE... :(