[AccessD] Optimizing subqueries

Max Wanadoo max.wanadoo at gmail.com
Tue Jun 3 12:44:46 CDT 2008


No not really because of the fact that the OrgID must NOT be in two of the
tables.  Ie, it is a "Not in (Select * etc)" clause.

Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, June 03, 2008 6:34 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Optimizing subqueries

Hi Max

On my way home .. but couldn't you just pull in the tables you lookup and
create inner joins on the three

  tablename.FKOrgID=mcmOrganisations.OrgID

?

/gustav

>>> max.wanadoo at gmail.com 03-06-2008 19:22 >>>
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




More information about the AccessD mailing list