Re: Query performance question.

Tech-Archive recommends: Speed Up your PC by fixing your registry

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
..

"Mark" <Mark@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F06217E3-0FEB-4AC2-847D-9A2078BDB5DE@xxxxxxxxxxxxxxxx
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



.