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

Question

I tried to make this sample as easy to play as possible. I have a table in MS Access with approximately 425,000 rows. When I run the following query again, the table will take about 10 seconds:

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 tried rebuilding the index, even made it compact and fixed, and made no difference.

Any help or insight would be greatly appreciated!

Answer 1

When you expand your experiments, you will see this behavior of MS-Access to a greater extent.

Observation is reproduced here in the 4-million-row MS-Access database used to transfer CSV imports to an SQL server.

When you select the entire non-indexed field with the sort order, MS-Access takes 3 minutes. When selecting the entire indexed field, a second section is required. Selecting TOP 1 of the indexed field takes another 3 minutes, which proves that the function does not use the available index. A cheat (SELECT TOP 1 BLOCKID FROM (SELECT BlockID FROM AvailabilityBlocks ORDER BY BlockID) also does not use an index, and also takes 3 minutes.

My explanation is that MS-Access has built-in “jet engine” functionality that is acceptable for some purposes (I still use MS-Access on some lightweight websites). The Jet Engine supports all the features of the visual query design screen. Starting with Office 2007, another layer of functionality has been added to make T-SQL compatible with SQL.

The "TOP 1" statement is one of these "new" features. As you can see, it is not supported in visual design. It is understood that this function is not designed to use 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 for protecting your investment in MS-Access is to transfer data to an ODBC database (MYSQL, SQL server, Oracle ...) and use MS-Access only as an interface. The view from the "top 1" can be displayed by a more optimized engine.

Answer 2

This would give exactly the same result (one row with the [BlockID] field containing the "lowest" value) ... the only way Access / Jet should use the index to do this quickly:

 SELECT Min(T.BlockID) AS BlockID FROM AvailabilityBlocks T; 

Or, if you need the entire top row, this should also be obtained quickly:

 SELECT * FROM AvailabilityBlocks WHERE BlockID=(SELECT Min(T.BlockID) FROM AvailabilityBlocks T); 

Using ORDER BY says you need to collect 425K rows first. With Min (BlockID), it simply gets the first / lowest value from the index, and the WHERE clause will use the index to grab the row you are looking for.

The difference comes down to touching one row, not hundreds of thousands ...