Which Access Query is more efficient?

Both queries perform the same function (at least it appears that way). Which
query is better from a performance stand point.

#1
SELECT BatchLocationTable.BatchLocation
FROM BatchLocationTable LEFT JOIN ActiveBatchLocation ON
BatchLocationTable.BatchLocation = ActiveBatchLocation.BatchLocation
WHERE BatchLocationTable.BatchLocation NOT IN
(ActiveBatchLocation.BatchLocation);

or

#2
SELECT BatchLocationTable.BatchLocation
FROM BatchLocationTable
WHERE BatchLocationTable.BatchLocation NOT IN (SELECT
ActiveBatchLocation.BatchLocation FROM ActiveBatchLocation);
--
Husker

I would also appreciate it if you could tell me why. Thanks in advance.
--
Husker


"Husker" wrote:

Both queries perform the same function (at least it appears that way). Which
query is better from a performance stand point.

#1
SELECT BatchLocationTable.BatchLocation
FROM BatchLocationTable LEFT JOIN ActiveBatchLocation ON
BatchLocationTable.BatchLocation = ActiveBatchLocation.BatchLocation
WHERE BatchLocationTable.BatchLocation NOT IN
(ActiveBatchLocation.BatchLocation);

or

#2
SELECT BatchLocationTable.BatchLocation
FROM BatchLocationTable
WHERE BatchLocationTable.BatchLocation NOT IN (SELECT
ActiveBatchLocation.BatchLocation FROM ActiveBatchLocation);
--
Husker

Since we do not have access to your computer and data, have you considered
the possibility of copying the relevant parts of your database and trying
them both to find out?

"Husker" (do not spam) wrote in message
news
Both queries perform the same function (at least it appears that way).
Which
query is better from a performance stand point.

#1
SELECT BatchLocationTable.BatchLocation
FROM BatchLocationTable LEFT JOIN ActiveBatchLocation ON
BatchLocationTable.BatchLocation = ActiveBatchLocation.BatchLocation
WHERE BatchLocationTable.BatchLocation NOT IN
(ActiveBatchLocation.BatchLocation);

or

#2
SELECT BatchLocationTable.BatchLocation
FROM BatchLocationTable
WHERE BatchLocationTable.BatchLocation NOT IN (SELECT
ActiveBatchLocation.BatchLocation FROM ActiveBatchLocation);
--
Husker

On Tue, 3 Oct 2006 14:59:02 -0700, Husker (do
not spam) wrote:

Both queries perform the same function (at least it appears that way). Which
query is better from a performance stand point.

#1
SELECT BatchLocationTable.BatchLocation
FROM BatchLocationTable LEFT JOIN ActiveBatchLocation ON
BatchLocationTable.BatchLocation = ActiveBatchLocation.BatchLocation
WHERE BatchLocationTable.BatchLocation NOT IN
(ActiveBatchLocation.BatchLocation);

or

#2
SELECT BatchLocationTable.BatchLocation
FROM BatchLocationTable
WHERE BatchLocationTable.BatchLocation NOT IN (SELECT
ActiveBatchLocation.BatchLocation FROM ActiveBatchLocation);

If you're trying to find records in BatchLocationTable which do not
exist in ActiveBatchLocation, neither one of these is ideal. The first
will not work because ActiveBatchLocation.BatchLocation will be NULL
if there is no match, and NULL doesn't match anything; the second
should work but Access handles NOT IN queries inefficiently.

Try

SELECT BatchLocationTable.BatchLocation
FROM BatchLocationTable LEFT JOIN ActiveBatchLocation ON
BatchLocationTable.BatchLocation = ActiveBatchLocation.BatchLocation
WHERE ActiveBatchLocationTable.BatchLocation IS NULL;

This "frustrated outer join" query will return only those records
where there is NOT a match between the tables.

John W. Vinson[MVP]

John - believe it or not both queries actually worked, they weren't
thoroughly tested but they were returning the same results. I tried your
theory and it worked as well so I went with that as I had also heard that
Access isn't efficient with Not In. Thanks!
--
Husker

did you time the queries?

"Husker" (do not spam) wrote in message
...
John - believe it or not both queries actually worked, they weren't
thoroughly tested but they were returning the same results. I tried your
theory and it worked as well so I went with that as I had also heard that
Access isn't efficient with Not In. Thanks!
--
Husker

Currently the test database is small so all queries execute without
hesitation. Is there a way to time queries that I'm not aware of? Otherwise
we won't be increasing the size of database for another week or two.
--
Husker

"Husker" wrote:
Currently the test database is small so all queries execute without
hesitation. Is there a way to time queries that I'm not aware of?
Otherwise
we won't be increasing the size of database for another week or two.
--
here be simple previous suggestion from Johm Viescas:

'***quote**

In a "fresh" open of Access, do:

Dim sglTime As Single, db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
sglTime = Timer
Set rst = db.OpenRecordset(" test query name here ")
rst.MoveLast
Debug.Print Timer - sglTime

'***unquote***

for the ultimate code by Michel
that could be altered for your purposes, go to

http://groups.google.com

search for

QueryPerformanceCounter group:microsoft.public.access.*

one example:

http://groups.google.com/group/micro...5fa4402?hl=en&

gist is you start a code module with

'*** quote ***
Option Explicit

Private Type LARGE_INTEGER
lowpart As Long
highpart As Long
End Type

Private Declare Function QueryPerformanceCounter Lib "kernel32"
(lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32"
(lpFrequency As LARGE_INTEGER) As Long

Public Function LargeToDec(Arg As LARGE_INTEGER) As Variant
LargeToDec = Arg.lowpart + 2 * Cdec(Arg.highpart) * 2 ^ 31
End Function

'*** unquote***

then, in a public sub

'*** mostly quote ***
Dim freq As LARGE_INTEGER
Dim starting As LARGE_INTEGER
Dim ending As LARGE_INTEGER
Dim dfreq As Variant

QueryPerformanceFrequency freq
dfreq = LargeToDec(freq)


QueryPerformanceCounter starting
CurrentProject.Connection.Execute "SELECT something from somewhere"
QueryPerformanceCounter ending
Debug.Print "name of your query", (LargeToDec(ending) -
LargeToDec(starting)) / dfreq

'*** end mostly quote***

one could adapt this public sub to accept query name(s)
or the query's SQL(s), or just repeat the "starting/ending"
for each query...

Thanks to Husker & responders

"Gary Walter" wrote in message
...

"Husker" wrote:
Currently the test database is small so all queries execute without
hesitation. Is there a way to time queries that I'm not aware of?
Otherwise
we won't be increasing the size of database for another week or two.
--
here be simple previous suggestion from Johm Viescas:

'***quote**

In a "fresh" open of Access, do:

Dim sglTime As Single, db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
sglTime = Timer
Set rst = db.OpenRecordset(" test query name here ")
rst.MoveLast
Debug.Print Timer - sglTime

'***unquote***

for the ultimate code by Michel
that could be altered for your purposes, go to

http://groups.google.com

search for

QueryPerformanceCounter group:microsoft.public.access.*

one example:

http://groups.google.com/group/micro...5fa4402?hl=en&

gist is you start a code module with

'*** quote ***
Option Explicit

Private Type LARGE_INTEGER
lowpart As Long
highpart As Long
End Type

Private Declare Function QueryPerformanceCounter Lib "kernel32"
(lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32"
(lpFrequency As LARGE_INTEGER) As Long

Public Function LargeToDec(Arg As LARGE_INTEGER) As Variant
LargeToDec = Arg.lowpart + 2 * Cdec(Arg.highpart) * 2 ^ 31
End Function

'*** unquote***

then, in a public sub

'*** mostly quote ***
Dim freq As LARGE_INTEGER
Dim starting As LARGE_INTEGER
Dim ending As LARGE_INTEGER
Dim dfreq As Variant

QueryPerformanceFrequency freq
dfreq = LargeToDec(freq)


QueryPerformanceCounter starting
CurrentProject.Connection.Execute "SELECT something from somewhere"
QueryPerformanceCounter ending
Debug.Print "name of your query", (LargeToDec(ending) -
LargeToDec(starting)) / dfreq

'*** end mostly quote***

one could adapt this public sub to accept query name(s)
or the query's SQL(s), or just repeat the "starting/ending"
for each query...