Why is Select * Faster than Select Top 1 in a Microsoft Access DB

Question

I've tried to make this sample as easy as possible to reproduce. I have a table in MS Access with about 425,000 rows. When I execute the following query again the table it takes about 10 seconds to execute:

SELECT TOP 1 BlockID FROM AvailabilityBlocks ORDER BY BlockID;

The "BlockID" Field is the primary key and is indexed.

But When I run the following query it returns in less than 2 seconds:

SELECT BlockID FROM AvailabilityBlocks ORDER BY BlockID;

I've tried rebuilding the index, even did a compact and repair and neither made a difference.

Any help or insight would be GREATLY appreciated!

Answer 1

When expanding your experiments you will see such behaviour of MS-Access in more ways.

The observation is reproduced here in an MS-Access database of 4 mln rows, used to transport a CSV import to an SQL server.

When selecting all of an unindexed field with a sort order MS-Access takes 3 minutes to execute. When selecting all of an indexed field it takes split second. When selecting TOP 1 of the indexed field it takes 3 minutes again, which proves that the function is not using the available index. The cheat (SELECT TOP 1 BLOCKID FROM (SELECT BlockID FROM AvailabilityBlocks ORDER BY BlockID) is not using the index either and also takes 3 minutes.

My explanation is that MS-Access has native 'jet engine' functionality which is acceptable for some use (I still use MS-Access on some light weight websites). The 'jet engine' supports all features of the visual query design screen. As from Office 2007 another layer of functionality was added to make the SQL language compatible with T-SQL.

The 'TOP 1' statement is one of those 'new' functions. As you can see it is not supported in the visual design. It is clear that this function is not created to make use of the existing index.

You can limit your code to jet engine functionality in 'options for Access'-> 'Design for objects' -> 'Query design' -> 'Compatibility with SQL server (ANSI 92).'

Another option to protect your investments in MS-Access is to migrate the data to an ODBC database (MYSQL, SQL-server, Oracle ...) and use MS-Access only as a front end. The view with 'top 1' can then be rendered by a more optimized engine.