login
chunking big lists in ACC2K, revisited posted: Tue 2011-08-30 16:49:55 tags: database
As previously noted, Access 2000's TOP clause is the cognate of MySQL's LIMIT clause. Now let's say, having selected and contacted the first 1000 contacts from a big table, you want to isolate the next block of 1000 contacts.

The way not to accomplish this, is construct a query like

SELECT email FROM contacts
WHERE email NOT IN (
   SELECT TOP 1000 email FROM contacts
)

The NOT IN clause is just horribly inefficient and slow. If you're picking the TOP 1000 records, and your list is a million records, the database engine has to exhaustively string-compare all 1M records at least 1000 times, i.e. a minimum of a billion string comparisons. The efficient way to structure the query is to specify a JOIN:

SELECT TOP 1000 contacts.email, first_chunk.email
FROM contacts
LEFT JOIN first_chunk ON contacts.email = first_chunk.email
WHERE ((first_chunk.email) Is Null)