Query performance question.

[Mark]

Hi group.

I am in the process of running a query that is trying to cross reference a
table containing file information against a list of useable file extensions.
I have imported this list of file extensions permissable into Access and
have writen something in SQL as follows:

SELECT *
FROM Hnb_groups
WHERE [FILE_Type] NOT IN (SELECT [File_Extension] FROM [FILE_EXTENSIONS]);

Hnb_groups contains the original info' I am trying to reference. The
FILE_EXTENSIONS file contains info related to the title.

It seems to be crashing Access all the time. There are typicall 500,000+
records in the Hnb_groups tables.

Is there an issue with the SQL code that would make it slower?

Any help is greatly appreciated as this is my first assignment in my first
graduate post since finishing college.

M

 

[John Spencer]

You might try using a frustrated outer join query. It should be faster.

SELECT *
FROM Hnb_groups LEFT JOIN File_Extensions
ON Hnb_groups.File_type = File_Extentions.FileExtension
WHERE File_Extentions.FileExtension is Null

NOT IN (SELECT ...) is S L O W AND if any nulls are in the values returned
by the subquery, then you get zero records returned.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jerry Whittle]

Four thoughts to add to John's sage advice.

1. Make sure that Hnb_groups.File_type is indexed.

2. When In/Not In runs badly, I try a Exists/Not Exists statement and visa
versa. Sometimes the improvement can be dramatic.

SELECT *
FROM [Hnb_groups]
WHERE Not Exists (SELECT "X"
FROM [FILE_EXTENSIONS]
WHERE [FILE_EXTENSIONS]![FILE_EXTENSION] = [Hnb_groups]![FILE_Type] );

3. Such a query needs internal space to run within the .mdb file. With a
half million records in the table, it's possible that the database is getting
near the 2 GB limit. Make a backup first then try a compact and repair before
running the query.

4. Also what do you mean by "crashing"? Error messages? Freezing up?