title: Why do SQL queries with VBA function run so slow?
body: <p>I've inherited an app that does the following kind query in a lot of places:</p>
  
  <pre><code>select foo.f1, foo.f2, foo.f3
  from foo
  where foo.f4 = getFooF4()
  </code></pre>
  
  <p>getFooF4 looks like this</p>
  
  <pre><code>Public Function getFooF4()
  Dim dbCurrent As Database
  Dim rstBar As Recordset
  
      Set dbCurrent = CurrentDb
      Set rstBar = dbCurrent.OpenRecordset("Bar", _
                                              dbOpenDynaset, _
                                              dbSeeChanges)
      getFooF4 = rstBar![myF4]
      ''yes this appears broken... Bar only contains one row :-/
  
      rstBar.close
      Set rstBar = Nothing
      dbCurrent.close
      Set dbCurrent = Nothing    
  End Function
  '' Note: in my experimentation getFooF4 only runs once during the 
  ''       execution of the query.
  </code></pre>
  
  <p>This ends up running fairly slow. If I remove getFooF4() from the query with a constant:</p>
  
  <pre><code>select foo.f1, foo.f2, foo.f3
  from foo
  where foo.f4 = 123456
  </code></pre>
  
  <p>or a parameter:</p>
  
  <pre><code>select foo.f1, foo.f2, foo.f3
  from foo
  where foo.f4 = [myFooF4]
  </code></pre>
  
  <p>or with a join:</p>
  
  <pre><code>select foo.f1, foo.f2, foo.f3
  from foo
  INNER JOIN bar ON bar.myF4 = foo.f4
  </code></pre>
  
  <p>It runs much faster.</p>
  
  <p>Why?</p>
  
  <p>Specs: App written and running in MS Access 2003, back-end database is SQL Server 2008.</p>