Run Query - return 10 rows

[Andy]

Is there anyway in which I can run a select query, but only return say the
first ten rows to save time? Running a query against a million records takes
some time and whilst making ammendments I quickly want to review some of the
returned formats (say the first ten).
Ta

 

[Jerry Whittle]

Change the SQL statement from:

SELECT ......
to
SELELCT TOP 10 .....

For the TOP statement to work you need to have a sort field or ORDER BY
clause.

HOWEVER, it still needs to search through all the records. An index on the
sorted field(s) can help. Better yet would by criteria or a WHERE clause to
limit the records. For example if there is a date/time field in the table,
you might want something like the following in the criteria/WHERE clause to
pull the records from the last few days:

Date() - 10

Make sure that the field with criteria is also indexed to speed things up.

Now if the million records are in something like SQL Server or Oracle
instead of Access, you can get much better performance with a pass-through
query.

 

[J_Goddard via AccessMonster.com]

Hi -

For the TOP statement to work you need to have a sort field or ORDER BY
clause.

Not true - The "Select top 10..." should work fine. I ran it on a table of
350,000+ records, with no PK (but with indexes) using no sort, order by, or
criteria. For the whole table it was several seconds over the network, but
virtually instantaneous using the top 25.

This was in the query design view, but presumably the same would be true if
selecting into a recordset.

John

 

[KARL DEWEY]

I thought an index was a sort.

 

[J_Goddard via AccessMonster.com]

Not really - an index contains pointers to records in a table, and these
pointers are sorted in order of the values of the field(s) used to create the
index. Think about the index at the back of a book - it refers to, or points
to, pages, but it does not physically sort them.


A table can have multiple indexes, which can be used to speed up queries. A
query which is sorted on fields which have indexes defined will run much
faster than if the sort is on a non-indexed field, and this is particularly
noticeable for queries on very large tables.

An analogy would be a library (in the days before electronic searches).
There are card catalogues which index the books by author, title, and subject,
but the books themselves are sorted on the shelves by (for example) their
Library of Congress numbers. Imagine what you would have to go through to
find all the books from a specific publisher.

John

I thought an index was a sort.

Hi -

[quoted text clipped - 37 lines]