Performance issue using keyword TOP 1

[JonMa]

Hi.
I have an MS access database with many records and needs to know the field names of a table and use the sql:

SELECT TOP 1 FROM TABLE.
Will access avoid table space scan before picking the first record?

I do not have EXPLAIN installed on my PC so I am a little worried about this query.
Hope someone can help.

 

[r937]

select * from yourtable where 1=0

rudy

http://r937.com/

SQL Consulting

 

[JonMa]

Hello Rudy.
Many thanks for your answer.
Is it that simple ?
Does this means that you sql will avoid table space scan
(select * from table where 1=0) and mine not
(select top 1 * from table) ?
If your sql really works then I think that your's is extremely smart because it could probably be used in Oracle and DB2 databases as well.

 

[Golom]

Rudy's code certainly works in the sense that it selects all fields but no rows.

The real issue is that a table scan is one of those things we can't know about or control directly. If the optimizer is smart enough it will recognize that the WHERE clause contains only constants that evaluate to FALSE and will therefore bypass scanning the table because nothing can satisfy the condition. If it isn't that smart, it may still do a scan of the table and evaluate the condition on each row.

 

[JonMa]

Yes Golom.
If I've only could get an EXPLAIN report, everything would then be solved.
Regards Jon.

 

[JonMa]

.