[AccessD] Optimizing subqueries

Lambert,  That is just excellent!  I have never handled "Not Ins" before in
this way.  It is just superb!  Ran in milliseconds.

This is a MakeTable SQL and I  had to do a GroupBy to ensure no dupes.  Code
(your probably not interested but here it is):

SELECT mcmOrganisations.OrgID INTO tblTempOrgs2
FROM ((mcmOrganisations INNER JOIN LinkTrusts2Contacts ON
mcmOrganisations.OrgID = LinkTrusts2Contacts.FKOrgID) LEFT JOIN
mcmIncomeTransactions ON mcmOrganisations.OrgID =
mcmIncomeTransactions.FKOrgID) LEFT JOIN tblTrustBids ON
mcmOrganisations.OrgID = tblTrustBids.FKOrgID
GROUP BY mcmOrganisations.OrgID, mcmOrganisations.FKOrgTypeID,
mcmOrganisations.FKDefaultROID, mcmIncomeTransactions.FKOrgID,
tblTrustBids.FKOrgID
HAVING (((mcmOrganisations.FKOrgTypeID)=1 Or
(mcmOrganisations.FKOrgTypeID)=4) AND
((mcmOrganisations.FKDefaultROID)=11200) AND
((mcmIncomeTransactions.FKOrgID) Is Null) AND ((tblTrustBids.FKOrgID) Is
Null)) OR (((mcmOrganisations.FKOrgTypeID)>9 And
(mcmOrganisations.FKOrgTypeID)<13) AND
((mcmOrganisations.FKDefaultROID)=11200) AND
((mcmIncomeTransactions.FKOrgID) Is Null) AND ((tblTrustBids.FKOrgID) Is
Null));

And all done via the QBE.

Thanks a million.

Thanks also to Gustav who posted something similar just after I had cracked
this.  Thanks Guys

Max
  

-----Original Message-----
From: Heenan, Lambert [mailto:Lambert.Heenan at AIG.com] 
Sent: Tuesday, June 03, 2008 6:51 PM
To: 'Access Developers discussion and problem solving'
Cc: 'Max Wanadoo'
Subject: RE: [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