Query performance issue

  • Thread starter Thread starter joe smith
  • Start date Start date
J

joe smith

I have the following query that performs very slowly if the inner select
statement returns a large result. Is there another way to write this query
that would perform better. Thanks.

SELECT [Table1].[MyID] as [ID]
FROM [Table1]
WHERE [Table1].[ID] NOT IN (SELECT [Table2].[MyID] FROM [Table2] WHERE
[Table2].[ID] = 15)

Joe
 
Joe,

Is this the same as...
SELECT MyID FROM Table1 WHERE MyID<>15

- Steve Schapel, Microsoft Access MVP
 
Is this the same as...
SELECT MyID FROM Table1 WHERE MyID<>15
Click to expand...
No, it is not.

I'm trying to find all the items not in the list MyID = 15 (I used poor
naming convention for this example).

Table1 contains all the possible items.
Table2 contains some (or all) the items in a list...Table2 describes lists
that contain items from Table1. ...does this make sense?

Joe

Steve Schapel said:
Joe,

Is this the same as...
SELECT MyID FROM Table1 WHERE MyID<>15

- Steve Schapel, Microsoft Access MVP


I have the following query that performs very slowly if the inner select
statement returns a large result. Is there another way to write this query
that would perform better. Thanks.

SELECT [Table1].[MyID] as [ID]
FROM [Table1]
WHERE [Table1].[ID] NOT IN (SELECT [Table2].[MyID] FROM [Table2] WHERE
[Table2].[ID] = 15)

Joe
Click to expand...
Click to expand...
 
Is this the same as...
SELECT MyID FROM Table1 WHERE MyID<>15
Click to expand...
No, it is not.

I'm trying to find all the items not in the list MyID = 15 (I used poor
naming convention for this example).

Table1 contains all the possible items.
Table2 contains some (or all) the items in a list...Table2 describes lists
that contain items from Table1. ...does this make sense?

Joe

Steve Schapel said:
Joe,

Is this the same as...
SELECT MyID FROM Table1 WHERE MyID<>15

- Steve Schapel, Microsoft Access MVP


I have the following query that performs very slowly if the inner select
statement returns a large result. Is there another way to write this query
that would perform better. Thanks.

SELECT [Table1].[MyID] as [ID]
FROM [Table1]
WHERE [Table1].[ID] NOT IN (SELECT [Table2].[MyID] FROM [Table2] WHERE
[Table2].[ID] = 15)

Joe
Click to expand...
Click to expand...
 
Joe-

I see from a later post that you posted a generic example, so I'm going to
give you a generic solution. Your problem is the Access JET engine doesn't
optimize NOT IN at all, but you can restate the problem as an outer JOIN
with a Null test.

"Find all widgets that don't have any parts that are category type 15."

SELECT Widgets.*
FROM Widgets LEFT JOIN
(SELECT WidgetParts.WidgetID
FROM WidgetParts
WHERE WidgetParts.CategoryType = 15) As WC15
ON Widgets.WidgetID = WC15.WidgetID
WHERE WC15.WidgetID IS NULL

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Thanks John,

That's the query I was looking for.

Joe


John Viescas said:
Joe-

I see from a later post that you posted a generic example, so I'm going to
give you a generic solution. Your problem is the Access JET engine doesn't
optimize NOT IN at all, but you can restate the problem as an outer JOIN
with a Null test.

"Find all widgets that don't have any parts that are category type 15."

SELECT Widgets.*
FROM Widgets LEFT JOIN
(SELECT WidgetParts.WidgetID
FROM WidgetParts
WHERE WidgetParts.CategoryType = 15) As WC15
ON Widgets.WidgetID = WC15.WidgetID
WHERE WC15.WidgetID IS NULL

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
joe smith said:
I have the following query that performs very slowly if the inner select
statement returns a large result. Is there another way to write this query
that would perform better. Thanks.

SELECT [Table1].[MyID] as [ID]
FROM [Table1]
WHERE [Table1].[ID] NOT IN (SELECT [Table2].[MyID] FROM [Table2] WHERE
[Table2].[ID] = 15)

Joe
Click to expand...
Click to expand...
 
Back
Top