CODEWITHSUNDEEP
ms-access
user1801904
user1801904

Reputation: 99

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

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!

Upvotes: 6

Views: 1824

Answers (2)

Jem Rabbit
Jem Rabbit

Reputation: 1

This would give the exact same result (a single row with [BlockID] field containing the 'lowest' value)... only this way Access/Jet should use the index to do it quickly: 

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

Or if it's the whole top row you're after, this should also get it quickly:

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

Using ORDER BY tells it to assemble 425K rows in order first. With Min(BlockID) it simply grabs the first/lowest value from the index, and the WHERE clause will use the index to grab the row you're after.

The difference comes down to touching a single row, instead of hundreds of thousands...

Upvotes: 0

Martijn van der Jagt
Martijn van der Jagt

Reputation: 524

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.

Upvotes: 1

Related Questions