[AccessD] Optimizing subqueries

Would using an Unmatched query be a faster solution that a query with a
sub-query?

SELECT Table1.ForeignKeyID
FROM Table1 LEFT JOIN Table2 ON Table1.ForeignKeyID = Table2.PrimaryKeyID
WHERE (((Table2.PrimaryKeyID) Is Null));

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Tuesday, June 03, 2008 1:23 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Optimizing subqueries

Gustav,

I need to try to optimize this function into a query (or set of queries).
Can you help?
When I try to do it, the "NOT IN" clause is so slllloooowww that I resorted
to creating this temp table.

Max

Public Function pfpf()
  Dim datStart As Date, datEnd As Date
  Dim sql As String, rst As DAO.Recordset, rst2 As DAO.Recordset, lngRecCt
As Long
  datStart = Time()
  Debug.Print datStart,
  
  sql = "Drop Table tblTempOrgs"
  On Error Resume Next
  CurrentDb.Execute (sql)
  sql = "Create Table tblTempOrgs (FKOrgID Long)"
  CurrentDb.Execute (sql)
  sql = "Create Unique Index FKOrgID on tblTempOrgs (FKOrgID)"
  CurrentDb.Execute (sql)
  sql = "Select OrgID, FKOrgTypeID from mcmOrganisations Where
FKDefaultROID=11200 and (FKOrgTypeID = 1 or FKOrgTypeID = 4 or (FKOrgTypeID
> 9 and FKOrgTypeID < 13))"
  Set rst = CurrentDb.OpenRecordset(sql)
  rst.MoveFirst
  Do While Not rst.EOF
    If DCount("*", "LinkTrusts2Contacts", "FKOrgID=" & rst!OrgID) > 0 Then '
there is a contact
      If DCount("*", "mcmIncomeTransactions", "FKOrgID=" & rst!OrgID) = 0
Then ' no previous transaction
        If DCount("*", "tblTrustBids", "FKOrgID=" & rst!OrgID) = 0 Then 'no
previous bids
          sql = "Insert into tblTempOrgs (FKOrgID) values (" & rst!OrgID &
")"
          CurrentDb.Execute (sql)
        End If
      End If
    End If
    rst.MoveNext
  Loop
  lngRecCt = DCount("*", "tblTempOrgs")
  datEnd = Time()
  Debug.Print datEnd, DateDiff("s", datStart, datEnd), lngRecCt
  Debug.Print "==============================================="
End Function

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com