Which Access Query is more efficient?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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);
 
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?
 
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);
Click to expand...

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!
 
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 said:
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.
--
Click to expand...
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/microsoft.public.access.formscoding/msg/9454f09bc5fa4402?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 said:
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/microsoft.public.access.formscoding/msg/9454f09bc5fa4402?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...
Click to expand...
 
Back
Top